formulas - hard to explain

[Guest]

Hi
I need to write a formula which would refer to a table with a calculation
depending on what I entered in the first cell. It is a table which will
minus a percentage depending on what the margin class number ( 22 - 4)
e.g.
A1 B1 C1 A1 B1 C1
£24.99 - 19 (75%) = £6.247 or £24.99 - 14 (52%) = £11.995

I created the table to do the equation however I need an 'IF' formula which
states, 'if B1 contains '16' refer to cell E4' which has the formula in or
'if B1 contains '19' refer to cell E7' hence subtracting the percentage to
give me the result in cell C1

Any help or guidance would be greatly appreciated.

 

[Bernard Liengme]

In G1:G10 I have some number 0, 5, 10, 15, ....
In H1:H10 I have the discount percentages
In A1 I have a dollar (pound) amount say (£24)
In B1 I enter a number (say, 19)
In C1 I have the formula
=ROUND(A1*(1-VLOOKUP(B1,$G$1:$H$10,2)),2)
The VLOOKUP part locates the 19 of B1 (actually it locates 15 since this is
be number closest but smaller than 19) and returns the corresponding
percentage.
Experiment with just =VLOOKUP(B1,$G$1:$H$10,2) to see how it works
The we compute the new dollar amount A1*(1-discount)
The ROUND will ensure you see pounds and pence (2 decimal places)

This is so much easier that a horrid nested IF statement.
best wishes

 

[joeu2004]

I need to write a formula which would refer to a table with a calculation
depending on what I entered in the first cell. It is a table which will
minus a percentage depending on what the margin class number ( 22 - 4)
e.g.
A1 B1 C1 A1 B1 C1
£24.99 - 19 (75%) = £6.247 or £24.99 - 14 (52%) = £11.995

I created the table to do the equation however I need an 'IF' formula which
states, 'if B1 contains '16' refer to cell E4' which has the formula in or
'if B1 contains '19' refer to cell E7' hence subtracting the percentage to
give me the result in cell C1.

And if B1 is 13, then E1; if 15, E3; if 18, then E8; etc? In other
words, do you have a table of percentages in column E for each class
number?

If so, the following indexes that table based on the value in B1. I
assume that the table begins with E1 corresponding to class number 13;
and I assume that column E contains an entry for all class numbers.
Then in C1:

=A1*(1-offset(E1,B1-13,0))

You can bulletproof this by doing the following, assuming that 22 is
the largest class number:

=A1*(1-offset(E1,max(0,min(22,B1)-13),0))

Note: This assumes that you record the percentages in column E in the
form of 75% or 0.75, not simply 75.

HTH