Excel formula help 1

[mattm31]

Hi

I'm trying to get a formula to work but cant seem to get my head round it this morning.

I have a cell E14 with a list in it, Gold, Silver, Bronze
I want cell E15 to look at it and which ever value is selected use
a folrmula for it.

Something like this

IF(E14="GOLD",THEN(H14<5,0,IF(H14=5,2,IF(H14<11,2.5,IF(H14<16,3,IF(H14<21,3.5,IF(H14<26,4,IF(H14<31,4.5,IF(H14>30,5))))))))
IF(E14="SILVER",THEN(H14<5,0,IF(H14=5,1,IF(H14<11,1.5,IF(H14<16,2,IF(H14<21,2.5,IF(H14<26,3,IF(H14<31,3.5,IF(H14>30,4))))))))
IF(E14="BRONZE",THEN(H14,0)

Thanks

 

[PWD]

Could be wrong, but I think you don't use the "Then" - that's implied. So you'd have something like:-

=IF(E14="GOLD",IF(H14<5,0,IF(H14=5,2)),IF(E14="Silver",IF(H14<5,0),IF(E14="Bronze","Whatever")))

Des.

 

[mattm31]

Thanks Des

I just tried it with the GOLD only and if i run this

=IF(E14="GOLD",IF(H14<5,0,IF(H14=5,2,IF(H14<11,2.5,IF(H14<16,3,IF(H14<21,3.5,IF(H14<26,4,IF(H14<31,4.5))))))))

It works but i needed one more on the end so added this

=IF(E14="GOLD",IF(H14<5,0,IF(H14=5,2,IF(H14<11,2.5,IF(H14<16,3,IF(H14<21,3.5,IF(H14<26,4,IF(H14<31,4.5,IF(H14>30,5)))))))))

and it errors on the last IF any ideas ?

 

[PWD]

I remember something about Excel only allowing 7 nested Ifs. It could be something to do with that - I'm afraid

Des.

 

[mattm31]

I see the problem now i need a VLookup table

so i have created this

Code Points Value
GOLD 1 £0.00
GOLD 2 £0.00
GOLD 3 £0.00
GOLD 4 £0.00
GOLD 5 £15.00
GOLD 6 £18.00
GOLD 7 £21.00
GOLD 8 £24.00
GOLD 9 £27.00
GOLD 10 £30.00
GOLD 11 £38.50
GOLD 12 £42.00
SILVER 1 £0.00
SILVER 2 £0.00
SILVER 3 £0.0
SILVER 4 £0.00
SILVER 5 £10.00
SILVER 6 £15.00
SILVER 7 £17.00
SILVER 8 £20.00
ETC

Can someone help me with the formula,
i need E14 to match "CODE" and H14 to match "POINTS" and return the "VALUE" in the CELL.

 

[SkipVought]

Seems as if your H column has numeric data that could be categorized in a TABLE with breakpoints like...
[t]
Val
0
5
11
16
26
31
[/tt]
Maybe I don't have the correct values, but with the correct values a MATCH lookup with a 1 MatchType.

Then use the MATCH function rather than nested IF statements.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattm31]

Sorted thanks by adding merging code&points so GOLD15 and
running a VLOOKUP

Thanks for your help.

 

[PWD]

And just to throw another spanner in the works, I've just come across the Excel function 'REPT'. It does seem a bit complicated though!

=REPT("North",(H2="red") * (B2>4))& REPT("South",H2="white") & REPT("East",H2="blue") & REPT("Other",NOT(OR(H2={"red","white","blue"})))

This will give you "North" if H2 = "Red" AND B2 > 4, "South" if H2 ="white" or "East" if H2="blue". I'm sure there should be uses for it!

Des.