IF Function with 3 conditions

[ConfusedAtWork]

If a cell says"Y" I want the next cell to have a numerical value, if it says "N" I want it to say "Nil" If it is blank then I want it to stay blank. The first two bits I can do, I just can't do the last.

 

[Gerald Higgins]

Hi, welcome to the board.

What numerical value do you want to use, if the first cell says "Y" ?

 

[SteveO59L]

=IF(A1="y",1,IF(A1="n","nil",""))

 

[ConfusedAtWork]

I'll put this image below so it's easier. If the value in column E says "Y" the 2.5% of the number in column D will appear in column F. If the value is "N", then I want it to say "Nil".

This is about as far as I have got. I want the rows with blanks in them to stay blank however.

Furthermore, in column G I want the values to be D-F. The cells next to "Nil" however, I would like to stay blank.

 

[C with no eyes]

=IF(A1="y",1,IF(A1="n","nil",""))

This works but what if someone types M in column A by accident? It wouldn't notice it's been input incorrectly and would return a blank cell.

I would be inclined to add some kind of error message so =IF(E2="","",IF(E2="y",2.5/100*D2,IF(E2="n","Nil","Invalid value in E")))

 

[ConfusedAtWork]

Thanks for the help so far all. Could anyone help me with a formula for the second bit?

 

[EXCEL_IN_EXCEL]

I would suggest the following formulas -

Paste in column F - =IF(E11="Y",SUM(D11*$E$1),IF(E11="N","Nil",""))
Paste in column G - =IFERROR(SUM(D11-F11),"")

If you want to be sure of "Y" / "N" in column E I would consider a list data validation which will avoid the possibility of typos.

 

[C with no eyes]

a formula for the second bit?

Sorry, missed that line.

use: =IF(F2="nil","",D2-F2)

Need to ask though... row 12... does that relate to the amount shown on row 11? If so, the formula in this post won't work the way you'd want it to. It's easy enough to create a formula though. Could you paste columns A-C of your spreadsheet? (Substitute sensitive information before posting on here)

 

[C with no eyes]

I would suggest the following formulas -

Paste in column F - =IF(E11="Y",SUM(D11*$E$1),IF(E11="N","Nil",""))
Paste in column G - =IFERROR(SUM(D11-F11),"")

EinE, The sum() functions are completely unnecessary. =SUM(A1+B1) returns the same value as =A1+B1 etc.

 

[EXCEL_IN_EXCEL]

EinE, The sum() functions are completely unnecessary. =SUM(A1+B1) returns the same value as =A1+B1 etc.

This being a matter of preference not fact. Even though it wasn't asked for, thank you for sharing your opinion nevertheless. To the poster it is entirely up to you the end user on your preference. Both formulas will work.