Need help with formula!!!!

[Missy]

A B
9 Right ear % =VLOOKUP($F$4,Sheet2!A2:B65,2)
10 Left ear % =VLOOKUP($F$5,Sheet2!A3:B66,2)

11 Enter lesser %: =IF(B9>B10,B10,B9)
12 Enter greater %: =IF(B10>B9,B10,B9)
13 Bilateral Hearing Loss: =(((B12*5)+B13)/6)/100

This formula calcualtes the % of hearing loss in the right ear and the left
ear then uses a formula to calculate the % of total bilatearl hearing loss.
The formula in B13 multiplies the lesser % of the right vs. left ear by 5
then adds the greater % and divides by 6. I get a #VALUE! in B13. If I
manually enter the percentages in B9 and B10 OR in B12 and B13, then B13
calculates correctly but it apparently won't calculate if I have a formula in
B9/B10 or B12/B13. Help!

 

[Pete_UK]

I think the problem is with your data table in sheet2. You may have
values in column B which look like percentages but which are actually
text values, so when you try to do some arithmetic on them in B13 you
get the #VALUE error. You will need to change the values in your table
to numbers.

Incidentally, your formula in B9 looks up the table A2:B65 in Sheet2,
but in B10 you are looking at A3:B66.

Hope this helps.

Pete

 

[Missy]

I checked sheet2 and they are all numbers. Thanks for catching the vlookup
error.