| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can multiple criteria be used for IF formulas?
I am using the below formula to calculate percent of change from one year to
another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can multiple criteria be used for IF formulas?
"CHaney" wrote:
=IF(F3=0,1,U3/F3-1) [....] I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. Your current formula seems to do just that. Did you try it? When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage) because F3 is zero. There is no need to make a special case. How is your formula behaving differently than you expect? If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in the formula (ill-advised) or be sure that the cell is formatted as Percentage. If you do change 1 to 100 (ill-advised), you will need to change the last argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure that the cell is __not__ formatted as Percentage. ----- original message ----- "CHaney" wrote: I am using the below formula to calculate percent of change from one year to another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can multiple criteria be used for IF formulas?
Oh, sorry I should have entered that. I get the following results with my
formula. If F3 and U3 are 0 it calculates If F3 is 0, I get 100% --exactly as you said because it's formatted for percentage However, when both F3 and U3 are 0, I also get 100% when I want it to yield 0 as the percent of change. So, I wanted to know if I can ask it to yield the one if only F3 is 0, and zero if U3 is zero, and zero if F3 and U3 are both 0. -- Thanks, Christine "Joe User" wrote: "CHaney" wrote: =IF(F3=0,1,U3/F3-1) [....] I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. Your current formula seems to do just that. Did you try it? When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage) because F3 is zero. There is no need to make a special case. How is your formula behaving differently than you expect? If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in the formula (ill-advised) or be sure that the cell is formatted as Percentage. If you do change 1 to 100 (ill-advised), you will need to change the last argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure that the cell is __not__ formatted as Percentage. ----- original message ----- "CHaney" wrote: I am using the below formula to calculate percent of change from one year to another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can multiple criteria be used for IF formulas?
=U3/F3-1*(IF(F3=0,1)*(IF(F3+U3=0,0)))
I have also tried this formula. But the yield is #DIV/0! -- Thanks, Christine "CHaney" wrote: Oh, sorry I should have entered that. I get the following results with my formula. If F3 and U3 are 0 it calculates If F3 is 0, I get 100% --exactly as you said because it's formatted for percentage However, when both F3 and U3 are 0, I also get 100% when I want it to yield 0 as the percent of change. So, I wanted to know if I can ask it to yield the one if only F3 is 0, and zero if U3 is zero, and zero if F3 and U3 are both 0. -- Thanks, Christine "Joe User" wrote: "CHaney" wrote: =IF(F3=0,1,U3/F3-1) [....] I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. Your current formula seems to do just that. Did you try it? When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage) because F3 is zero. There is no need to make a special case. How is your formula behaving differently than you expect? If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in the formula (ill-advised) or be sure that the cell is formatted as Percentage. If you do change 1 to 100 (ill-advised), you will need to change the last argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure that the cell is __not__ formatted as Percentage. ----- original message ----- "CHaney" wrote: I am using the below formula to calculate percent of change from one year to another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can multiple criteria be used for IF formulas?
Thanks Joe for the help, I figured it out.
Used the following formula within the cell: =IF(F3=0,1,U3/F3-1) Then used this formula in the Conditional Format to blank out the cell when both cells were 0: =F3+U3=0 -- Thanks, Christine "CHaney" wrote: Oh, sorry I should have entered that. I get the following results with my formula. If F3 and U3 are 0 it calculates If F3 is 0, I get 100% --exactly as you said because it's formatted for percentage However, when both F3 and U3 are 0, I also get 100% when I want it to yield 0 as the percent of change. So, I wanted to know if I can ask it to yield the one if only F3 is 0, and zero if U3 is zero, and zero if F3 and U3 are both 0. -- Thanks, Christine "Joe User" wrote: "CHaney" wrote: =IF(F3=0,1,U3/F3-1) [....] I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. Your current formula seems to do just that. Did you try it? When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage) because F3 is zero. There is no need to make a special case. How is your formula behaving differently than you expect? If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in the formula (ill-advised) or be sure that the cell is formatted as Percentage. If you do change 1 to 100 (ill-advised), you will need to change the last argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure that the cell is __not__ formatted as Percentage. ----- original message ----- "CHaney" wrote: I am using the below formula to calculate percent of change from one year to another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can multiple criteria be used for IF formulas? | Excel Worksheet Functions | |||
| Need Help w/ formulas for multiple criteria | Excel Worksheet Functions | |||
| How do I put multiple criteria in LOOKUP formulas | Excel Worksheet Functions | |||
| Array Formulas with multiple criteria in the same row? | Excel Worksheet Functions | |||
| multiple criteria for formulas | Excel Discussion (Misc queries) | |||
Linear Mode
