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

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

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

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

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

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

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

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.

Challenge
 

More enigmatic and shorter but not necessarily better:

=IF(BG229<0,0,IF(MEDIAN(3,7,BE229)<BE229,0,AK24* INDEX(Input!$G$8:$G$15,MEDIAN(3,7,BE229)+(BE2294) )))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ron Coderre" wrote in message
...
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...

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.

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.