Need to supress display of FALSE in a cell

A

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
 
S

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
B

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

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
Click to expand...

=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
 
M

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
 
A

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


Sandy Mann said:
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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Top