how to apply weighted factors in two dimensional data in excel


New member
Jul 15, 2013
Reaction score
hi All,

I have a large set of staff traveling expense data for the whole year, it records for each staff travel, the number of tickets, and the price per tickets. Now the challenge is that company wants to reduce the total traveling costs by 20%, however, this 20% can't just apply across the board, but must be adjusted by two factors such as frequency of the travel, and cost per ticket so that for example more frequent traveler should apply higher percentage to it to reduce the frequency, and higher ticket price user should also apply higher percentage so that to reduce the ticket price. (People can do this by booking early or taking bulk discount). The overall affect of this is that the total aggregated traveling costs should be cut by 20%.

So what I did was running a pitvot table shows for each person, two lines of values:Number of traveling over the 12month periods (This gives me frequency), and Average tickets price brought over the last 12month(This gives me average ticket price). Now i just need to apply weight to these two factor for each person, but I just don't now how. How wonder if anyone can give me some idea how I can apply weighted factor to each individual so that total costs is 20% less than original costs.


  • Example.xlsx
    10.6 KB · Views: 16