| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge
=IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11),
IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
OwenGiryluk wrote:
=IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... Just add -- in front, like this: =--(IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0))))))) Beege |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
Put a double unary minus in front of it, i.e.:
=--IF( etc Hope this helps. Pete On Oct 1, 7:34 pm, "OwenGiryluk" <u37865@uwe wrote: =IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$1*1), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=*7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
=IF(BG229=0,IF(BE229=3,AK229*(0+INput!$G$10),IF(BE 229=4,AK229*(0+INput!$G$11),IF(BE229=5,AK229*(0+IN put!$G$13),IF(BE229=6,AK229*(0+INput!$G$14),IF(BE2 29=7,AK229*(0+INput!$G$15)))))),0) Vaya con Dios, Chuck, CABGx3 "OwenGiryluk" wrote: =IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
Try this:
=IF(SUM(COUNTIF(BE229,{3,4,5,6,7})),AK229*CHOOSE(B E229-2,Input!$G$10,Input!$G$11,Input!$G$13,Input!$G$14, Input!$G$15),0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "OwenGiryluk" <u37865@uwe wrote in message news:790d3d9f06f38@uwe... =IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
Thanks for your help!
Beege wrote: =IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... Just add -- in front, like this: =--(IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229 *(0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0))))))) Beege |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
Darn...I missed a test...
Try this: =IF(AND(BG229=0,SUM(COUNTIF(BE229,{3,4,5,6,7}))),A K229*CHOOSE(BE229-2,Input!$G$10,Input!$G$11,Input!$G$13,Input!$G$14, Input!$G$15),0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Try this: =IF(SUM(COUNTIF(BE229,{3,4,5,6,7})),AK229*CHOOSE(B E229-2,Input!$G$10,Input!$G$11,Input!$G$13,Input!$G$14, Input!$G$15),0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "OwenGiryluk" <u37865@uwe wrote in message news:790d3d9f06f38@uwe... =IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
On Oct 1, 11:34 am, "OwenGiryluk" <u37865@uwe wrote:
=IF(BG229=0,IF(BE229=3,AK229*(0+Input!$G$10),IF(BE 229=4,AK229*(0+Input!$G$1*1), IF(BE229=5,AK229*(0+Input!$G$13),IF(BE229=6,AK229* (0+Input!$G$14),IF(BE229=*7, AK229*(0+Input!$G$15),0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. Insert ",0" before the right-most parenthesis. It returns FALSE because you do handle the condition where BG229<0. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
"OwenGiryluk" <u37865@uwe wrote...
=IF(BG229=0, IF(BE229=3,AK229*(0+Input!$G$10), IF(BE229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13), IF(BE229=6,AK229*(0+Input!$G$14), IF(BE229=7,AK229*(0+Input!$G$15), 0)))))) This formula returns a "FALSE" statement if not true. Anyway to change the formula to return a zero if FALSE. THANKS... If that were all you really want to do, then rearrange it. =IF(BG229<0,0, IF(BE229=3,AK229*(0+Input!$G$10), IF(BE229=4,AK229*(0+Input!$G$11), IF(BE229=5,AK229*(0+Input!$G$13), IF(BE229=6,AK229*(0+Input!$G$14), IF(BE229=7,AK229*(0+Input!$G$15), 0)))))) However, the '0+' bits do nothing. The multiplications would coerce the Input!G# cells to numbers, so you should simplify to =IF(BG229<0,0, IF(BE229=3,AK229*Input!$G$10, IF(BE229=4,AK229*Input!$G$11, IF(BE229=5,AK229*Input!$G$13, IF(BE229=6,AK229*Input!$G$14, IF(BE229=7,AK229*Input!$G$15, 0)))))) But this looks like a lookup. You could simplify this by using a lookup function. =IF(OR(BG229<0,AND(BE229<{3;4;5;6;7})),0, AK229*LOOKUP(BE229,{3;4;4.5;5;6;7},Input!$G$10:$G$ 15)) The 4.5 value in the second array constant corresponds to Input!G12, which your original formula skips. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge
CLR wrote...
=IF(BG229=0,IF(BE229=3,AK229*(0+INput!$G$10), IF(BE229=4,AK229*(0+INput!$G$11),IF(BE229=5,AK229 *(0+INput!$G$13), IF(BE229=6,AK229*(0+INput!$G$14),IF(BE229=7,AK229 *(0+INput!$G$15) ))))),0) .... Don't like testing? This will return 0 when BG229 < 0, but when BG229 = 0 but BE229 < any of 3, 4, 5, 6 or 7, it'll return FALSE. The OP's own ',0' wasn't misplaced. It just wasn't sufficient. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortcut Key --- A Challenge | Excel Discussion (Misc queries) | |||
| A Challenge | Excel Discussion (Misc queries) | |||
| Algorithm Challenge | Excel Worksheet Functions | |||
| A Challenge | Excel Worksheet Functions | |||
| Who is up for a challenge? | Excel Discussion (Misc queries) | |||
Linear Mode
