Nested If and Or? Function

[virago_midget]

Hi ya

I'm trying to set a cell to a value depending on several values in another cell.

The cell will contain D,M,P,U or F.

If it contains D then the answer needs to be 80, or if it contains M then the answer needs to be 70 if it contains P then answer is 50, if it's U then it's 10, if F it's 0

I did get it to work with a lookup, but wanted to try doing an If statement.

Any ideas?

Many thanks... first time posting here

 

[T. Valko]

Hi ya

I'm trying to set a cell to a value depending on several values in another cell.

The cell will contain D,M,P,U or F.

If it contains D then the answer needs to be 80, or if it contains M then the answer needs to be 70 if it contains P then answer is 50, if it's U then it's 10, if F it's 0

I did get it to work with a lookup, but wanted to try doing an If statement.

Any ideas?

Many thanks... first time posting here

Like this...

=IF(A1="F",0,IF(A1="U",10,IF(A1="P",50,IF(A1="M",70,IF(A1="D",80,"")))))

 

[Aladin Akyurek]

Hi ya

I'm trying to set a cell to a value depending on several values in another cell.

The cell will contain D,M,P,U or F.

If it contains D then the answer needs to be 80, or if it contains M then the answer needs to be 70 if it contains P then answer is 50, if it's U then it's 10, if F it's 0

I did get it to work with a lookup, but wanted to try doing an If statement.

Any ideas?

Many thanks... first time posting here

The best practice is a look up formula...

=VLOOKUP(A2,{"D",80;"M",70;"P",50;"U",10;"F",0},2,0)

Otherwise, you need a chain of IFs, hopefully correctly itemized...

=IF(A2="D",
    80,
    IF(A2="M",
      70,
      IF(A2="P",
        50,
        IF(A2="U",
          10,
          IF(A2="F",
            0,
            "")))))

 

[virago_midget]

Fantastic! I now realised why I couldn't get the IF to work on it's own, and thanks so much for the Lookup as well.

My grades calculator works brilliant.

Good job I only teach Excel to A level standard, otherwise I'd be stuck!

 

[Aladin Akyurek]

Fantastic! I now realised why I couldn't get the IF to work on it's own, and thanks so much for the Lookup as well.

My grades calculator works brilliant.

Good job I only teach Excel to A level standard, otherwise I'd be stuck!

You are welcome. Thanks for providing feedback.

 

[T. Valko]

Fantastic! I now realised why I couldn't get the IF to work on it's own, and thanks so much for the Lookup as well.

My grades calculator works brilliant.

Good job I only teach Excel to A level standard, otherwise I'd be stuck!

You're welcome. Thanks for the feedback!