Need excel formula that will work out the following weighted points from the original point issued

[Leicester City Fox]

Hi All

I need a excel formula that will work out the following weighted points from the original point issued to provider owing to number of Journeys that provider does a week.

The weighting of the point is as follows:

1. Provider has more 120 single journey per week – Points x 0.25
2. Provider has 61 – 120 single Journeys per week = Points x 0.5
3. Provider has 13 – 60 single journey per week – Points x 0.75
4. Provider has 12 or less single journey the original point given stands (No Weighting)

I have given an example of each weighting below with the result I am looking for applied to the weighting it should get.

I have workout the Weighted Actual Points (column D) manually but want a formula that automatically workout the weighted points on the original points issued (column C) owing to number of Journeys (Column B) that provider (Column A) should get.

A​	B​	C​	D​
Provider	Number Of Journeys	Original Points	Weighted Actual Points given
A​	120​	15​	3.75​
B​	80​	10​	5​
C​	40​	12​	9​
D​	12​	3​	3​
B​	30​	10​	7.5​
C​	8​	15​	15​

Any Ideas of the simplest formula that will work out the weighting for each provider (column D).

Many Thanks

The Leicester Fox

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D
1	Provider	Number Of Journeys	Original Points	Weighted Actual Points given
2	A	120	15	3.75
3	B	80	10	5
4	C	40	12	9
5	D	12	3	3
6	B	30	10	7.5
7	C	8	15	15
Main

Cell Formulas
Range	Formula
D2:D7	D2	=C2*LOOKUP(B2,{0,13,61,120},{1,0.75,0.5,0.25})

 

[Leicester City Fox]

Hi Fluff

Afternoon

That worked a treat, thank you very much your a Star

Thanks

Leicester Fox

 

[Fluff]

You're welcome & thanks for the feedback