Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem with IF statement
I have the following set-up with 3 cells: A1 - a target percentage value (30.00%) A2 - the actual value A3 - the deviation Each cell is formatted for percentages. I have inserted the following formula into A3: =IF(ISBLANK(A2),"",IF(A2<30,"",A2-A1) The formula is supposed to do either of three things: 1. If there is no value in A2, leave A3 blank. 2. If the actual percentage is 30% or lower, leave A3 blank. 3. If the actual percentage is over 30%, calculate the deviation from the standard. However, The fromula is not behaving! No matter what I do, the A3 cell remains blank! What have I done wrong??? Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
#2
|
|||
|
|||
Use A2="" instead of isblank, isblank only works if the cell is empty
maybe you have a formula in A2? This would be a shorter way =IF(OR(A2="",A2<30%),"",A2-A1) -- Regards, Peo Sjoblom (No private emails please) "Jonibenj" wrote in message ... I have the following set-up with 3 cells: A1 - a target percentage value (30.00%) A2 - the actual value A3 - the deviation Each cell is formatted for percentages. I have inserted the following formula into A3: =IF(ISBLANK(A2),"",IF(A2<30,"",A2-A1) The formula is supposed to do either of three things: 1. If there is no value in A2, leave A3 blank. 2. If the actual percentage is 30% or lower, leave A3 blank. 3. If the actual percentage is over 30%, calculate the deviation from the standard. However, The fromula is not behaving! No matter what I do, the A3 cell remains blank! What have I done wrong??? Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
#3
|
|||
|
|||
Jonibenj Wrote: I have the following set-up with 3 cells: A1 - a target percentage value (30.00%) A2 - the actual value A3 - the deviation Each cell is formatted for percentages. I have inserted the following formula into A3: =IF(ISBLANK(A2),"",IF(A2<30,"",A2-A1) The formula is supposed to do either of three things: 1. If there is no value in A2, leave A3 blank. 2. If the actual percentage is 30% or lower, leave A3 blank. 3. If the actual percentage is over 30%, calculate the deviation from the standard. However, The fromula is not behaving! No matter what I do, the A3 cell remains blank! What have I done wrong??? Jonathan. Try this ... =IF(OR(ISBLANK(A2),A2<0.3),\"\",A2-A1) Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
#4
|
|||
|
|||
try
=IF(ISBLANK(A2),"",IF(A2<0.3,"",A2-A1)) or in a neater format =IF(OR(ISBLANK(A2),A2<0.3),"",A2-A1) Hope this helps Rowan "Jonibenj" wrote: I have the following set-up with 3 cells: A1 - a target percentage value (30.00%) A2 - the actual value A3 - the deviation Each cell is formatted for percentages. I have inserted the following formula into A3: =IF(ISBLANK(A2),"",IF(A2<30,"",A2-A1) The formula is supposed to do either of three things: 1. If there is no value in A2, leave A3 blank. 2. If the actual percentage is 30% or lower, leave A3 blank. 3. If the actual percentage is over 30%, calculate the deviation from the standard. However, The fromula is not behaving! No matter what I do, the A3 cell remains blank! What have I done wrong??? Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
#5
|
|||
|
|||
Hi!
Could be..... Each cell is formatted for percentages So, in your formula: IF(A2<30 If A2 is formatted as PERCENTAGE then the cell will DISPLAY 30% but the true underlying value is actually 0.3..... So.... IF(A2<30 = TRUE = "" Change the formula to: =IF(ISBLANK(A2),"",IF(A2<0.3,"",A2-A1) OR =IF(ISBLANK(A2),"",IF(A2<30%,"",A2-A1) Biff "Jonibenj" wrote in message ... I have the following set-up with 3 cells: A1 - a target percentage value (30.00%) A2 - the actual value A3 - the deviation Each cell is formatted for percentages. I have inserted the following formula into A3: =IF(ISBLANK(A2),"",IF(A2<30,"",A2-A1) The formula is supposed to do either of three things: 1. If there is no value in A2, leave A3 blank. 2. If the actual percentage is 30% or lower, leave A3 blank. 3. If the actual percentage is over 30%, calculate the deviation from the standard. However, The fromula is not behaving! No matter what I do, the A3 cell remains blank! What have I done wrong??? Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
#6
|
|||
|
|||
Thanks a lot guys, I got it to work!! :) Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Problem | Excel Worksheet Functions | |||
Fundamental problem with IF statement | Excel Worksheet Functions | |||
Fundamental problem with IF statement | Excel Worksheet Functions | |||
IF statement problem | Excel Worksheet Functions | |||
IF statement problem | Excel Worksheet Functions |