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(I 28: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
--
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"),S UM(I28:M29)*120,"")))

--
HTH

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


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


"akkrug" wrote in message
...
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(I 28: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
--
akkrug

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


--
Wag more, bark less


"akkrug" wrote:

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(I 28: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
--
akkrug

On Thu, 19 Jun 2008 12:48:02 -0700, akkrug
wrote:

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:M2 9)*120))))))

Thanks for the help!!

Ken K. - 2191

=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I 28: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="OH 2"))*90+((I17="EH1")+(I17="EH2"))*120)

Hope this helps/ Lars-Åke

try

=IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I 28: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" wrote:

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(I 28: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
--
akkrug

Hi,

A different option would be to apply conditional formatting, such that if
the cell content is "FALSE", then change the font colour to match the
background colour of the cell.

Dave

url:http://www.ureader.com/msg/10355905.aspx

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" wrote:

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"),S UM(I28:M29)*120,"")))

--
HTH

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


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


"akkrug" wrote in message
...
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(I 28: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
--
akkrug