Calculate weighted percentile

[BWV10]

Hi,

i have data for the production and production cost for several industrial plants. In column A is the names of the plant, column B how much the plant produces and column C the cost of production per ton in $US.

Now i want to find the production weighted cost position of one particular company compared to the others as a percentile. I know how to do it in complicated (Calculate the weighted percentile for every plant and then look at the company weighted average cost and look where it is closest). But is there maybe an easier way without having to first calculate the weighted percentile for all plants? The end result should be "Company B lays at the 53th percentile of the cost curve".

[Glenn Kennedy]

You can not post links (yet), but you can post a sample Excel sheet from Day1. Re-read the yellow banner (top) and carefully follow the instructions.

[BWV10]

Thanks, i attached the data now

[JeteMc]

Hello BWV10 and Welcome to Excel Forum.
Not sure how company B would be at the 53rd percentile, however to get the values per company in H4:H7 try:

Please Login or Register  to view this content.

Note that your regional settings, for Norway, may dictate that semicolons are substituted for commas in the above formula: =SUMPRODUCT((A$3:A$12=G4)*(C$3:C$12)*(D$3:D$12))/SUMIFS(C$3:C$12;A$3:A$12;G4)
Let us know if you have any questions.