| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple IF statements
Hi gang,
my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
=if(j9={7,14,28,42},0,j9-d21*(j97))
Doesn't deal with the case that J9 = 56 If =56, use =IF(J9={7,14,28,42},0,J9-D21*(J97))*(J9<56) "Tom" wrote: Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
try this idea. Notice I did the 0's first and worked from the top down.
=J9-IF(OR(J955,J9=42,J9=28,J9=14,J9=7),0,IF(OR(J942, J928,J914,J97),D21)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Tom" wrote in message ... Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
Left out a portion
=IF(OR(J9={7,14,28,42}),0,J9-D21*(J97)) which ignores what happens if J9 56 If it should return 0 when 56 then =IF(OR(J9={7,14,28,42}),0,J9-D21*(J97))*(J9<56) "Duke Carey" wrote: =if(j9={7,14,28,42},0,j9-d21*(j97)) Doesn't deal with the case that J9 = 56 If =56, use =IF(J9={7,14,28,42},0,J9-D21*(J97))*(J9<56) "Tom" wrote: Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
On Jul 27, 7:46 am, Tom wrote:
what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 "Best" way? That's debatable. One way is: =if(J9<7, 0, (mod(J9,7)<0)*(J9-D21)) Or perhaps the following would be more clear to you: =if(or(J9<7, mod(J9,7)=0), 0, J9-D21) Note that this ass-u-me-s that you want 0 for all multiples of 7, including 56 and beyond, and that you want J9-D21 for non-multiples of 7, including 57 and beyond. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
Errata....
On Jul 27, 9:01 am, I wrote: On Jul 27, 7:46 am, Tom wrote: what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 =if(J9<7, 0, (mod(J9,7)<0)*(J9-D21)) Oops, misread the conditions in an attempt to simplify. That should be: =if(J9<7, J9, (mod(J9,7)<0)*(J9-D21)) Or perhaps the following would be more clear to you: =if(or(J9<7, mod(J9,7)=0), 0, J9-D21) That should be: =if(J9<7, J9, if(mod(J9,7)=0, 0, J9-D1)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
This should work...
=IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) Rick "Tom" wrote in message ... Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
This should work...
=IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) Or a little more compactly, this... =IF(MOD(J9,7)=0,0,J9-D21*(J97)) Rick |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
On Jul 27, 10:44 am, "Rick Rothstein \(MVP - VB\)"
wrote: This should work... =IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) I believe that returns 0 when J9=0, but the OP wants J9 when J9<7. Therefore, I believe we must test J9<7 first. Note that this also returns J9 when J9<0, which may or may not be want the OP intends, if the OP even cares. But it does follow the OP's specifications. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
Rick & Joe -
The OP's specs omit 21 and 35 as conditions where he wants a zero result, so your solutions incorporating MOD() will be erroneous "Rick Rothstein (MVP - VB)" wrote: This should work... =IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) Rick "Tom" wrote in message ... Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
On Jul 27, 11:06 am, Duke Carey
wrote: Rick & Joe - The OP's specs omit 21 and 35 as conditions where he wants a zero result, so your solutions incorporating MOD() will be erroneous Good eye! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
The OP's specs omit 21 and 35 as conditions where he
wants a zero result, so your solutions incorporating MOD() will be erroneous Good eye! Yes indeed... I completely missed that! Rick |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
This should work...
=IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) I believe that returns 0 when J9=0, but the OP wants J9 when J9<7. The formula is wrong for the reasons Duke pointed out, but as to your statement... perhaps I am missing something in your statement, but isn't my formula returning 0 when J9 equals zero the same thing as returning J9 when J9 equals 0? Rick |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple IF statements
Nice catch!!! I think this formula will account for all of the conditions...
D20: =IF(MOD(J9,14-7*(J9=7))<0,J9-IF(J9<=7,0,D21),0) or, in slightly shorter form, this one... D20: =(MOD(J9,14-7*(J9=7))<0)*(J9-D21*(J9=7)) While the formula produces values for J9<0 and J9=56, we are not in a position to know what the OP wanted to happen there as he left his intentions unstated. Rick "Duke Carey" wrote in message ... Rick & Joe - The OP's specs omit 21 and 35 as conditions where he wants a zero result, so your solutions incorporating MOD() will be erroneous "Rick Rothstein (MVP - VB)" wrote: This should work... =IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21)) Rick "Tom" wrote in message ... Hi gang, my head is spinning around...what would be the best way to write this one? In cell D20: IF J9<7,D20=J9 IF J9=7,D20=0 IF J97<14,D20=J9-D21 IF J9=14, D20=0 IF J914<28,D20=J9-D21 IF J9=28, D20=0 IF J928<42,D20=J9-D21 IF J9=42, D20=0 IF J942<56, D20=J9-D21 thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple if statements with multiple conditions | Excel Discussion (Misc queries) | |||
| Using multiple IF statements | Excel Discussion (Misc queries) | |||
| sum of multiple IF statements | Excel Worksheet Functions | |||
| multiple if statements | Excel Discussion (Misc queries) | |||
| multiple IF statements | Excel Worksheet Functions | |||
Linear Mode
