Problem with IF statement

[Jonibenj]

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.

[BenjieLop]

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.

[Peo Sjoblom]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Rowan]

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
>
>

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jonibenj]

Thanks a lot guys, I got it to work!!

Jonathan