Lookup Formula??

[Gary Thomson]

What is the best formula to use to do the following:?

A B C D E
1 School Roll Flat Rate Rate per Pupil
2 1 - 31 £368 0
3 32 - 166 £368 £3.41
4 167 - 700 £828 £4.55


Now, I enter into cells A6 through A40 various different
schools rolls e.g. 101, 45, 32, 170, 167, 31, 166 etc...

I want my formula to look into the above table, and
display the total cost to that school based on the number
of pupils.

For example, for a roll of 101, this falls into the 32-166
category. So the total cost for 101 pupils would be £368
+ (101-32+1)*£3.41 = £606.70.

(Note that the £3.41 is for each and every pupil exceeding
31; similarly the £4.55 is for each and every pupil
exceeding 166)

For a School Roll of 170, this falls into the 167-700
category, ans so the total cost for 170 pupils would be
£828 + (170-167+1)*£4.55 = £846.20

 

[Guest]

Hi Gary
The following formula will give you what you want

=VLOOKUP(A6,$A$2:$E$4,4)+((A6-VLOOKUP(A6,$A$2:$E$4,1)+1)*VLOOKUP(A6,$A$2:$E$4,5)

Since it is a little hard to tell I assumed your table had the following arrangement
Colum
A - Low number of pupil
B - "-
C - High number of pupil
D - Flat rat
E - per pupi

The first VLOOKUP returns the flat rate, the second returns the low number of pupils in the group, and the third retunrs the per pupil rate

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Gary Thomson wrote: ----

What is the best formula to use to do the following:

A B C D
1 School Roll Flat Rate Rate per Pupi
2 1 - 31 £368
3 32 - 166 £368 £3.4
4 167 - 700 £828 £4.5


Now, I enter into cells A6 through A40 various different
schools rolls e.g. 101, 45, 32, 170, 167, 31, 166 etc..

I want my formula to look into the above table, and
display the total cost to that school based on the number
of pupils

For example, for a roll of 101, this falls into the 32-166
category. So the total cost for 101 pupils would be £368
+ (101-32+1)*£3.41 = £606.70

(Note that the £3.41 is for each and every pupil exceeding
31; similarly the £4.55 is for each and every pupil
exceeding 166

For a School Roll of 170, this falls into the 167-700
category, ans so the total cost for 170 pupils would be
£828 + (170-167+1)*£4.55 = £846.2