|
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??? :mad: :confused: Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
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??? :mad: :confused: Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
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??? :mad: :confused: 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 |
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??? :mad: :confused: Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
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??? :mad: :confused: Jonathan. -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=396355 |
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 |
| All times are GMT +1. The time now is 02:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com