Ignoring zero cells in IF statements
 

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

Ignoring zero cells in IF statements
 

Andy,

Try this in E1.

=IF(D10,E2-D1,IF(C10,E2-C1,IF(B10,E2-B1,E2)))

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

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=488796

Ignoring zero cells in IF statements
 

=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~" wrote in message
...

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

Ignoring zero cells in IF statements
 

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

Ignoring zero cells in IF statements
 

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


--
~Andy~
------------------------------------------------------------------------
~Andy~'s Profile: http://www.excelforum.com/member.php...o&userid=17751
View this thread: http://www.excelforum.com/showthread...hreadid=488796

Ignoring zero cells in IF statements
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=488796