Ignoring zero cells in IF statements

[~Andy~]

Hi all - I'm not sure if it's an IF statement I need - but if it is then I would like to know how to ignore cells with zero as the number.

Let me explain further:

A B C D E F
1 Threshold 1333.3 0.0 0.0 1000.0 4333.3
2 Threshold ***. 1333.3 0 0 2333.3 6666.7

Cell E1 should display cell E2 minus cell D1 but only if D1 is not zero. If it is zero then it should subtract C1 but again only if it is not zero. Again if C1 is zero it should go to B1 and so on.

I hope this is explained clearly enough. It's giving me some problems this one and I would appreciate some help.

Many Thanks,

Andy

[SteveG]

Andy,

Try this in E1.

=IF(D1>0,E2-D1,IF(C1>0,E2-C1,IF(B1>0,E2-B1,E2)))

This will work backwards from D1:B1 meaning if the first argument is met (D1>0) then it will always return the difference of E2-D1, even if C1 or B1 are greater then 0.

Cheers,

Steve

[Bob Phillips]

=E2-IF(D1<>0,D1,IF(C1<>0,C1,IF(B1<>0,B1,0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"~Andy~" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all - I'm not sure if it's an IF statement I need - but if it is then
> I would like to know how to ignore cells with zero as the number.
>
> Let me explain further:
>
> A B C D
> E F
> 1 -Threshold 1333.3 0.0 0.0 1000.0 4333.3
> 2 Threshold ***. 1333.3 0 0 2333.3 6666.7-
>
> Cell E1 should display cell E2 minus cell D1 but only if D1 is not
> zero. If it is zero then it should subtract C1 but again only if it is
> not zero. Again if C1 is zero it should go to B1 and so on.
>
> I hope this is explained clearly enough. It's giving me some problems
> this one and I would appreciate some help.
>
> Many Thanks,
>
> Andy
>
>
> --
> ~Andy~
> ------------------------------------------------------------------------
> ~Andy~'s Profile:
http://www.excelforum.com/member.php...o&userid=17751
> View this thread: http://www.excelforum.com/showthread...hreadid=488796
>

[Search33]

Hi Andy
Try this
=IF(D1=0,IF(C1=0,IF(B1=0,"B1=0",E2-B1),E2-C1),E2-D1)

Not sure what you mean by the "and so on" but you can put that in where it
says "B1=0" (the case where all 3 are 0)


- Search


"~Andy~" wrote:

>
> Hi all - I'm not sure if it's an IF statement I need - but if it is then
> I would like to know how to ignore cells with zero as the number.
>
> Let me explain further:
>
> A B C D
> E F
> 1 -Threshold 1333.3 0.0 0.0 1000.0 4333.3
> 2 Threshold ***. 1333.3 0 0 2333.3 6666.7-
>
> Cell E1 should display cell E2 minus cell D1 but only if D1 is not
> zero. If it is zero then it should subtract C1 but again only if it is
> not zero. Again if C1 is zero it should go to B1 and so on.
>
> I hope this is explained clearly enough. It's giving me some problems
> this one and I would appreciate some help.
>
> Many Thanks,
>
> Andy
>
>
> --
> ~Andy~
> ------------------------------------------------------------------------
> ~Andy~'s Profile: http://www.excelforum.com/member.php...o&userid=17751
> View this thread: http://www.excelforum.com/showthread...hreadid=488796
>
>

[~Andy~]

Hi guys,

Thanks for the responses - they have been very helpful thus far. Let me explain further what it is I am trying to do - as it is perhaps a little more comlpex than I stated in my original post.

What the spreadsheet is trying to achieve is to work out how much commission someone gets. This means that the cell can never display a minus number (because if someone doesnt achieve their target then they don't pay money back - they just dont recieve commission).

Also - the formula needs to count back up to 12 months. The formulas above can only count back 7 months (because of the limit of 7 IF statements).

I hope this is all clear. Any help would be mightily appreciated.

Many thanks,

Andy

[SteveG]

Andy,

If you could provide an example of how you want the sheet to look with some data, it may be easier to help. From your explanation, you want a 12 month running total of commissions paid and a month to month commission calculator that ignores zeros but I am not sure why if there is no payback for not meeting sales goals. I am not sure what E2 is calculating by subtracting prior month commissions. Maybe I am missing something though.

Steve