Excel: Are Nested Statements the answer? 2

[shytott]

HI All
What Im trying to achieve is,
If col C has the letters M, A, N or R, then Col E has the values 7,7,10 or 4 respectively UNLESS, col D has an ‘H’ in which case, the value in Col C jumps to Col F, and Col C becomes blank ie:

Col C Col D Col E Col F
M H 7
A 7
N H 10
R 4
Ive tried various combinations of the following expression but Excel is always unhappy with it!
=IF((C8="M"),7,IF((C8="A"),7,IF((C8="N"),10,IF((C8="R"),4,IF((AND(C8="M",D8="H")),"")))))
(I realise this wouild only take care of the M-H combination and not the other 3, but I was just trying to crack this one first!)
I have done something similar using lots of cols with loads of ‘1’s which satisfy different criteria then adding them all up and using the totals to process the answers, but this is a bit unwieldy and I was hoping for a more manageable, nested if solution.

Hope this makes sense!

Cheers
Shytott

 

[SkipVought]

Hi,

1. set up a lookup table
[tt]
M 7
A 7
N 10
R 4
[/tt]
2. the formula...
[tt]
E7: =IF($D7="H","",VLOOKUP($C7,$J$1:$K$4,2,FALSE))
F7: =IF($D7<>"H","",VLOOKUP($C7,$J$1:$K$4,2,FALSE))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Shytott,

You could use:
E7-
=IF(D7="H","",IF(OR(C7="A",C7="M"),7,IF(C7="N",10,IF(C7="R",4,"N/A"))))
F7-
=IF(D7<>"H","",IF(OR(C7="A",C7="M"),7,IF(C7="N",10,IF(C7="R",4,"N/A"))))
and copy down as far as needed.

Cheers

[MS MVP - Word]

 

[shytott]

Thanks Guys, that works a treat - much Obliged