## Conditional If Statement With Percent |

## Conditional If Statement With Percent - Excel |
View Answers |

Hi everyone-

I'm trying to figure out the formula that will place the word "yes" if cell A is less than 25% of cell C.

For example, "Yes" if cell A1 (Quantity in stock) is less than 25% of C1 (Quantity in reorder), or "No" if this condition is no met.

Values:

A1-325

B1-??

C1-1000

I'm trying to figure out the formula that will place the word "yes" if cell A is less than 25% of cell C.

For example, "Yes" if cell A1 (Quantity in stock) is less than 25% of C1 (Quantity in reorder), or "No" if this condition is no met.

Values:

A1-325

B1-??

C1-1000

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

## Similar Excel Tutorials

List all Conditional Formatting Formulas in Excel

List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...

List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...

Multiple Conditional Formatting Rules for a Cell in Excel

Apply multiple Conditional Formatting rules to a cell or range in Excel. This allows you to change the way a cell ...

Apply multiple Conditional Formatting rules to a cell or range in Excel. This allows you to change the way a cell ...

Apply Conditional Formatting to Multiple Cells with a Single Formula

How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...

How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...

Make Complex Formulas for Conditional Formatting in Excel

How to make complex formulas for conditional formatting rules in Excel. This will serve as a guide to help you buil ...

How to make complex formulas for conditional formatting rules in Excel. This will serve as a guide to help you buil ...

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File

- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Macro to add a New Line to Message Box Pop-up Windows in Excel

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

Save the Current Worksheet as a New Excel Workbook File

- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel

- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF

- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

## Similar Topics

Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I have cells in a column, some colored yellow, some not. I am trying to use

the SUMIF function to sum only the cells in the range that are colored. Can

this be done? Can I enter something in the "criteria" part of the formula

that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it

to work right. I don't know how to get Excel to recognize if a cell is

colored in a formula.

=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;

otherwise returns 0 (zero).

Can anyone help?

the SUMIF function to sum only the cells in the range that are colored. Can

this be done? Can I enter something in the "criteria" part of the formula

that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it

to work right. I don't know how to get Excel to recognize if a cell is

colored in a formula.

=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;

otherwise returns 0 (zero).

Can anyone help?

I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks

Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks

How do I get the colors to change automatically when I use a drop down list.

Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of

that cell to change its color to "green." And if later I change the "word" to

another from the drop down list, it will change it's color to a specified

color.

The drop down list that I use was created from cells that have the colors

already in the "word", but I do not know how to make the list show the colors

so it puts the word & color automatically in the drop down list to the cell

with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,

as I don't know fully the capabilities of formulas or vba.

Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of

that cell to change its color to "green." And if later I change the "word" to

another from the drop down list, it will change it's color to a specified

color.

The drop down list that I use was created from cells that have the colors

already in the "word", but I do not know how to make the list show the colors

so it puts the word & color automatically in the drop down list to the cell

with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,

as I don't know fully the capabilities of formulas or vba.

Hi all -

Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.

Cell Value = "birds"

Example -- I need to filter every row so that I see every row that CONTAINS "birds" in the character-string, not just the row that = "birds".

a) is this possible?

If not, I'd like to know also so I can stop attempting to guess (-;

Thanks!!!

Matt

Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.

Cell Value = "birds"

Example -- I need to filter every row so that I see every row that CONTAINS "birds" in the character-string, not just the row that = "birds".

a) is this possible?

If not, I'd like to know also so I can stop attempting to guess (-;

Thanks!!!

Matt

I am trying to figure out how to write a formula to figure out production cycle time.

We will be building 8 "widgets" a day.

We will work 10 hours a day.

There is a 20 minute break at 9:20 am.

Production shuts down for lunch 30 minutes for lunch at 12:30.

Production starts at 6:00 am.

Here is what I can do.

Production cycle time = (10*60)-30/8 or 71.25 minutes

If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.

This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.

=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))

I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.

I have bought a number of books in an attempt to figure this out, and I am still stumped

We will be building 8 "widgets" a day.

We will work 10 hours a day.

There is a 20 minute break at 9:20 am.

Production shuts down for lunch 30 minutes for lunch at 12:30.

Production starts at 6:00 am.

Here is what I can do.

Production cycle time = (10*60)-30/8 or 71.25 minutes

If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.

This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.

=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))

I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.

I have bought a number of books in an attempt to figure this out, and I am still stumped

I am trying to write a formula that uses both an if statement and a vlookup statement.

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?

Hi, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT

The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula

=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00

Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.

The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success

Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)

On the attached timesheet there are columns IN, OUT, IN,OUT

The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula

=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00

Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.

The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success

Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)

Hey

I have three columns containing last name values from 3 different sources that I've brought into one sheet. I am trying to find a way to compare all three values to establish if they are the same.

Of the three values there is no master, Each value may be different. So essentially it would be like doing =IF(a1=a2=a3,"match", "nomatch")

But of course this isn't possible.

I don't care about obtaining detail about which value matches which. I just need true or false response as to whether all three values are the same or not.

Thanks for any help anyone can provide.

Aaz

I have three columns containing last name values from 3 different sources that I've brought into one sheet. I am trying to find a way to compare all three values to establish if they are the same.

Of the three values there is no master, Each value may be different. So essentially it would be like doing =IF(a1=a2=a3,"match", "nomatch")

But of course this isn't possible.

I don't care about obtaining detail about which value matches which. I just need true or false response as to whether all three values are the same or not.

Thanks for any help anyone can provide.

Aaz

I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.

Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.

In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.

Can anyone help with this, because I can't figure it out....

In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.

Can anyone help with this, because I can't figure it out....

I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Hi,

Try this...

There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2.

Data validation is used in cell K2 to limit it to a set of values.

Can we implement this formula?

IF(K2="Approved")

{

A2=A3; B2=B3; C2=C3; D2=D3; K2=K3;

}

ELSE

{

no change to any values.

}

Thanks in advance...

Try this...

There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2.

Data validation is used in cell K2 to limit it to a set of values.

Can we implement this formula?

IF(K2="Approved")

{

A2=A3; B2=B3; C2=C3; D2=D3; K2=K3;

}

ELSE

{

no change to any values.

}

Thanks in advance...

How do I set the formula if

if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank

if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank

I am trying to establish a formula, but not too sure how.

For example " If Cell A5 has been marked with ' X ', then copy what is in Cell A6 to

cell C25 "

For example " If Cell A5 has been marked with ' X ', then copy what is in Cell A6 to

cell C25 "

I'm looking for a formula that pulls the text from a cell unti it hits a space.

I'm using the formula below but keep getting #VALUE results

B1: =LEFT(A1,FIND(",",A1,1))

I know it's not that hard but can't figure it out.

thanks for the help

max

I'm using the formula below but keep getting #VALUE results

B1: =LEFT(A1,FIND(",",A1,1))

I know it's not that hard but can't figure it out.

thanks for the help

max

Hi Everyone,

I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!

I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!

I have searched and read all the help files. I find the properties of

an object, I see how I can "lock", "size and move with cells" or "not

move with cells". No matter what I select, the object moves off the

screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right

corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,

that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and

give us their valuable insight and time.

Jo

an object, I see how I can "lock", "size and move with cells" or "not

move with cells". No matter what I select, the object moves off the

screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right

corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,

that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and

give us their valuable insight and time.

Jo

I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.

Hi, this is something so simple that I have forgotten how to do it.

I want F3 to turn yellow if someone selects "Yes" from a drop down list in cell E3, then F3 has no fill once data is entered into it. And F3 turns Black if E3= no selected from the list. Or F3 turns red if E3 contains "TBC"

so in summary,

if E3="Yes", then F3 conditional format to Yellow until data is entered in it.

if E3="No", then F3 conditional format to Black

if E3="TBC", then E3 & F3 conditional format to Red until E3 is changed to

"Yes" or "No" at a later time.

Thanks in Advance for the help

Mutley13

I want F3 to turn yellow if someone selects "Yes" from a drop down list in cell E3, then F3 has no fill once data is entered into it. And F3 turns Black if E3= no selected from the list. Or F3 turns red if E3 contains "TBC"

so in summary,

if E3="Yes", then F3 conditional format to Yellow until data is entered in it.

if E3="No", then F3 conditional format to Black

if E3="TBC", then E3 & F3 conditional format to Red until E3 is changed to

"Yes" or "No" at a later time.

Thanks in Advance for the help

Mutley13

I have been using Excel (XP) to make a text chart for several months. Some of

my text entries are rather lengthy. The past two weeks these lengthy entries

are showing up as pound signs (#########) when I click off the cell. I know

the text will fit in the cell, and the problem isn't solved by making the

cell bigger or using a little bit less text. I have the cells formatted as

"text" and "wrap to fit". I have printed the pages and the printed version

also has pound signs. I just want my text to show up!

my text entries are rather lengthy. The past two weeks these lengthy entries

are showing up as pound signs (#########) when I click off the cell. I know

the text will fit in the cell, and the problem isn't solved by making the

cell bigger or using a little bit less text. I have the cells formatted as

"text" and "wrap to fit". I have printed the pages and the printed version

also has pound signs. I just want my text to show up!

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients. Thank you.