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 the
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 cum. 1333.3 0 0 2333.3 6666.7-

Cell E1 should display cell E2 minus cell D1 but only if D1 is no
zero. If it is zero then it should subtract C1 but again only if it i
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 problem
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)

 

[Guest]

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

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 wit
some data, it may be easier to help. From your explanation, you want
12 month running total of commissions paid and a month to mont
commission calculator that ignores zeros but I am not sure why if ther
is no payback for not meeting sales goals. I am not sure what E2 i
calculating by subtracting prior month commissions. Maybe I am missin
something though.

Stev