Need help with formula!!!!
 

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(B9B10,B10,B9)
12 Enter greater %: =IF(B10B9,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!

Need help with formula!!!!
 

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

On Dec 2, 4:13*pm, Missy wrote:
* * * *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(B9B10,B10,B9) * *
12 * Enter greater %: * * * * * * * =IF(B10B9,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!

Need help with formula!!!!
 

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

"Pete_UK" wrote:

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

On Dec 2, 4:13 pm, Missy wrote:
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(B9B10,B10,B9)
12 Enter greater %: =IF(B10B9,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!

.