Using IF function to run calculation when reference cell contains specific text

[rcgarrett]

Hello. I am trying to create a formula using nested IF statements that will run a calculation based upon specific text that is entered into a reference cell. I am getting a #VALUE! error. The formula is currently written as:

=IF(D4="B",(Q3*(M3/L3+M3)),IF(D4="C",(Q3*(O3/N3+O3)),IF(D4="A",Q4,)))

Where:

* D4 will be populated with the letter A, B or C
* All cells are formatted as "General"
* All other cells (e.g. L3, M3, N3, O3, Q3) are populated with number values

Does anyone know how to resolve the value error so that the formula will run the calculations on the condition that a specific letter is entered into the reference cell? Probably overlooking something really obvious. Thanks.

[FDibbins]

you dont need the 2nd IF in that formula if you are only evaluating 3 criteria. try it like this...

=IF(D4="B",(Q3*M3/L3)+M3,IF(D4="C",(Q3*O3/N3)+O3,Q4))

[rcgarrett]

Hmm, I tried that but am still getting the #VALUE! error. I think that the error is occurring due to the use of text as a criteria for calculating a numeric value because the formula seems to work with A in Q4. The error only occurs when D4 contains B or C.

[FDibbins]

upload a sample please.

the way you have set up your formula does not include any text in the calcs (unless there is text in your data?)

[Kevin UK]

Hi

Your not using Q4 to calculate are you! It is only referenced if D4 = "A"

=IF(D4="B",Q3*(M3/L3+M3),IF(D4="C",Q3*(O3/N3+O3),Q4))

[rcgarrett]

I figured it out. It's:

=IF(D4="A",Q4,IF(D4="B",(Q4*(L4/(L4+M4))),IF(D4="C",(Q4*(N4/(N4+O4))),"")))

Thanks for the quick replies.