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

#### Leicester City Fox

##### Board Regular
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

##### MrExcel MVP, Moderator

+Fluff.xlsm
ABCD
1ProviderNumber Of JourneysOriginal PointsWeighted Actual Points given
2A120153.75
3B80105
4C40129
5D1233
6B30107.5
7C81515
Main
Cell Formulas
RangeFormula
D2:D7D2=C2*LOOKUP(B2,{0,13,61,120},{1,0.75,0.5,0.25})

#### Leicester City Fox

##### Board Regular
Hi Fluff

Afternoon

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

Thanks

Leicester Fox

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
2
Views
204
Replies
2
Views
103
Replies
5
Views
85
Replies
0
Views
129
Replies
2
Views
112

1,129,568
Messages
5,637,093
Members
416,957
Latest member
Brovashift

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back