Letters with numeric values for formulas

[dannyok90]

Im trying to create a Purchase order form, amongst other problems I'm facing, the one I'm trying to tackle at the moment is as follows,

I'm trying to use letters as 'code' for Tax percentages, take a look at the following

so after i have entered the quantity and the unit price, the 'amount' column will populate with a simple '=[unit price]*[quantity]'

when i then enter 'S' into the 'TAX' Column i want it to add 20% onto the 'amount' column

but just to make it that bit trickier i have about three different letters representing different tax percentages

I'm pretty much a noob to excel, I'm pretty literate and capable but simple explanations would help haha

many thanks

 

[TonyUK72]

Nested IF statement:

=IF(D1="",(B1*C1),IF(D1="A",((B1*C1)/100)*5+(B1*C1),IF(D1="B",((B1*C1)/100)*10+(B1*C1),IF(D1="C",((B1*C1)/100)*20+(B1*C1)))))

 

[TonyUK72]

But what you want to aim for is:

=IF(D1="",(B1*C1),((B1*C1)/100)*VLOOKUP(D1,I1:J3,2)+(B1*C1))

This formula takes into account the in cells I1, I2, I3 you have A, B, C "being the letters you wish to use" & in cells J1, J2, J3 you have 5, 10, 20 "being the percentage rates you wish to assign to each letter"

 

[dannyok90]

But what you want to aim for is:

=IF(D1="",(B1*C1),((B1*C1)/100)*VLOOKUP(D1,I1:J3,2)+(B1*C1))

This formula takes into account the in cells I1, I2, I3 you have A, B, C "being the letters you wish to use" & in cells J1, J2, J3 you have 5, 10, 20 "being the percentage rates you wish to assign to each letter"

Thanks! I think i slightly understand, i would create a table in a new sheet with and then paste the =IF formula into the total cell.. then manipulate the cells in the formula to suit..

this is a bigger picture of my spreadsheet

 

[dannyok90]

This is what i tried, but its coming up with an error, on sheet two all i have for the moment is is
A B C
1 S 20
2

IF+(d19="",(c19*b19),((c19*b19)/100)*VLOOKUP(Sheet2!A1:B1)+(c19*b19))

 

[TonyUK72]

If you create the table in another sheet, highlight all the cells (6) of the table. you can then right click & give the newly created table a name e.g TaxCODES

your formula would then look like this

=IF(D1="",(B1*C1),((B1*C1)/100)*VLOOKUP(D1,TaxCODES,2)+(B1*C1))

Like you say, you can manipulate the formula to suit your needs

 

[dannyok90]

IT WORKED! Thats GREAT! thanks a hell of a lot!