Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with ignoring blank cells | Excel Discussion (Misc queries) | |||
how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) | |||
geomean ignoring blank cells and chars | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) |