Can someone help me with this formula please????

[Missy]

A B
12 Lesser %: =IF($B$9<$B$10,$B$9,$B$10)
13 Greater %: =IF($B$9>$B$10,$B$10,$B$10)
14 Bilateral Hearing Loss: =(((B12*5)+B13)/6)/100

I get a #VALUE! error in B14. I'm not sure what other information you need.
The formula in B14 uses the lesser and greater numbers from two other
cells/formulas. Any help would be appreciated! Thanks!

 

[Joe User]

I get a #VALUE! error in B14.

A #VALUE! error for such formulas usually means that one of the values (B12
or B13) is text, not a number. Based on your formulas, that actually means
that a value in B9 or B10 is text.

If you are using Excel 2003, you might see this by using the Evaluate
Formula feature. Select B14 and click on Tools > Formula Auditing >
Evaluate Formula. Alternatively, you can evaluate =TYPE(B9) and =TYPE(B10)
in some temporary cells. See the Type help page.

FYI, you can simply your formulas.

B12: =MIN($B$9,$B$10)
B13: =MAX($B$9,$B$10)

Also note that if B9 and B10 were entered as percentages, e.g. writing 15%
or 0.15, you would not need "/100" in B14.

I suspect that is a clue as to the root cause of the problem. That is, I
suspect that you have strings like "15%" in B9 and B10 instead of numbers.

So if you implement the MIN and MAX suggestions above, those formulas will
probably result in #VALUE! errors.


----- original message -----

 

[Squeaky]

Hi Missy,

The error is in the B10 chain. There must be a text reference somewhere. If
B10 is another formula try multiplying the whole thing by 1 in B10. If it
gets the #VALUE error it definitely has a text entry somewhere. Also, your
formula in B13 will always result in what is in B10 so having the formula
makes no sense, unless you typed it wrong in the post.

 

[Missy]

B9 =VLOOKUP($F$4,'%'!A2:B65,2,FALSE)
B10 =VLOOKUP($F$5,'%'!A3:B66,2,FALSE)

 

[Missy]

And I have verified the sheet % does have all numbers.

 

[David Biddulph]

And have you confirmed that they are all numbers by using =ISTEXT() and
=ISNUMBER(), or do the contents just *look* like numbers?

 

[Missy]

Yes, just confirmed.

And have you confirmed that they are all numbers by using =ISTEXT() and
=ISNUMBER(), or do the contents just *look* like numbers?
--
David Biddulph




.

 

[Joe User]

And have you confirmed that they are all numbers by using =ISTEXT()
and =ISNUMBER(), or do the contents just *look* like numbers?

Yes, just confirmed.

It is almost impossible to resolve such mysteries at arm's length, unless we
get lucky.

If you want, send me the workbook or a workbook that duplicates the problem.
The workbook must be saved as an Excel 2003 or XLSX file. Send the file to
joeu2004 "at" hotmail.com.


----- original message -----

 

[Joe User]

Yes, just confirmed.

Missy misspoke.

I have seen the workbook, and the root cause of the problem is what we have
been trying to tell her: the lookup table contains number strings (text),
not real number. Consequently, of course, VLOOKUP returns text, not real
numbers.

Moreover, the "lesser%" and "greater%" IF formulas are convoluted. They
work by accident only because of the example "numbers" (text) she is using,
namely "9.6" and "17.6".

I have sent her a corrected workbook. Hopefully that will put these matters
to rest.


----- original message -----

 

[David Biddulph]

Thanks, Joe. It gets very frustrating when we tell the OP how to check
whether the data values are numbers or text but we still get told the wrong
information. Glad that you were able to confirm the diagnosis of which we
had all been pretty sure.