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

Leicester City Fox

Board Regular
Joined
Oct 7, 2016
Messages
82
Office Version
  1. 2019
Platform
  1. Windows
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​
ProviderNumber Of JourneysOriginal PointsWeighted 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
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
How about

+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
Joined
Oct 7, 2016
Messages
82
Office Version
  1. 2019
Platform
  1. Windows
Hi Fluff

Afternoon

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

Thanks

Leicester Fox
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top