Need to supress display of FALSE in a cell

[akkrug]

I have entered a formula that seems to work ok, but is displaying false if an
invalid code is entered into one of the input cells. Is there any way to
suppress the display of FALSE and just display spaces?

The formula in question is as follows:
=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I28:M29)*90),IF(I17="OH2",(SUM(I28:M29)*90),IF(I17="EH1",(SUM(I28:M29)*120),IF(I17="EH2",(SUM(I28:M29)*120))))))

Thanks for the help!!

Ken K. - 2191

 

[Sandy Mann]

Give the formula a FALSE argument:

=IF(I17="LH",SUM(I28:M29)*30,IF(I17="OH1",SUM(I28:M29)*90,IF(I17="OH2",SUM(I28:M29)*90,IF(I17="EH1",SUM(I28:M29)*120,IF(I17="EH2",SUM(I28:M29)*120,"")))))

But it looks like you can shorten it to:

=IF(I17="LH",SUM(I28:M29)*30,IF(OR(I17="OH1",I17="OH2"),SUM(I28:M29)*90,IF(OR(I17="EH1",I17="EH2"),SUM(I28:M29)*120,"")))

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Brad]

Don't know if you want to change your equation

=IF(ISNA(VLOOKUP(I17,L17:M19,2,FALSE)),"",(SUM(I28:M29)*VLOOKUP(I17,L17:M19,2,FALSE)))

This does what your original formula did (this does assume you don't have an
OH2 or EH2 - that would need different multipliers

This also mean that in L17 = EH, L18 = LH, L19 = OH and m17 = 120, L18 = 30,
L19 = 90.

There is nothing magical about using L17-M19. but if you change the
location the you will need to change the equation.....

 

[Lars-Åke Aspelin]

I have entered a formula that seems to work ok, but is displaying false if an
invalid code is entered into one of the input cells. Is there any way to
suppress the display of FALSE and just display spaces?

The formula in question is as follows:
=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I28:M29)*90),IF(I17="OH2",(SUM(I28:M29)*90),IF(I17="EH1",(SUM(I28:M29)*120),IF(I17="EH2",(SUM(I28:M29)*120))))))

Thanks for the help!!

Ken K. - 2191

=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I28:M29)*90),IF(I17="OH2",(SUM(I28:M29)*90),IF(I17="EH1",(SUM(I28:M29)*120),IF(I17="EH2",(SUM(I28:M29)*120),"")))))

Note the added "" almost at the end of the formula.

You can shorten your formula a bit, like this (if you don't mind
having a 0 as result when the input is "not correct")

=SUM(I28:M29)*((I17="LH")*30+((I17="OH1")+(I17="OH2"))*90+((I17="EH1")+(I17="EH2"))*120)

Hope this helps/ Lars-Åke

 

[Mike H]

try

=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I28:M29)*90),IF(I17="OH2",(SUM(I28:M29)*90),IF(I17="EH1",(SUM(I28:M29)*120),IF(I17="EH2",(SUM(I28:M29)*120)," ")))))

Mike

 

[akkrug]

Thanks so much. You all are the best. I don't think I ever would have
figured out the "" fix. Too many parentheses for me!!

Ken K.
--
akkrug

Give the formula a FALSE argument:

=IF(I17="LH",SUM(I28:M29)*30,IF(I17="OH1",SUM(I28:M29)*90,IF(I17="OH2",SUM(I28:M29)*90,IF(I17="EH1",SUM(I28:M29)*120,IF(I17="EH2",SUM(I28:M29)*120,"")))))

But it looks like you can shorten it to:

=IF(I17="LH",SUM(I28:M29)*30,IF(OR(I17="OH1",I17="OH2"),SUM(I28:M29)*90,IF(OR(I17="EH1",I17="EH2"),SUM(I28:M29)*120,"")))

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk