Weighted Percentile

[Kimber154]

Hi All - I have a similar question. I have data with similar questions. The data set is a sample of set, Origin, Destination, Number of Units, Conveyance mode rail or truck, transit days. I am looking for a weighted average and ranked by 90% percentile? How do I best sort to get the weighted average and 90 percentile for each lane based on transit days? There are some negative values that will eventually be corrected. I have tried to upload a file but file upload states failed? Thank you everyone for the help!

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Kimber154]

Hi Richard! Thank you! I just tried to upload the file - let me try earlier excel version ....

[Kimber154]

Hi Richard -- Each conveyance node, example arrival at vessel to container discharge each takes a certain amount of days. I need to know weighted average by origin, destination, truck or rail, volume and the 90th percentile of transit days.
Thank you!

[Richard Buttrey]

Would you add some manually calculated results and explain how you derived them please.

Remember you are 100% au fait with your terminology, it's second nature to you and you probably spend a lot of your waking days dealing with it.
We come to this with zero knowledge so our first task is to understand your terminology and where these things you describe are located on the sheet.

Then we need to understand and see how you want the data manipulated to provide your weighted average results. We need that so that we can be sure a suggested solution does in fact give you what you expect.

[Kimber154]

Hi Richard..totally understand and I am very appreciative! Give me a few minutes and I will attach. Thank you so much for your help!

[Kimber154]

Hi Richard - Hope that the attached is more helpful - DATA 2 tab. The conveyance headers in red are the days in-between movement that we are trying to measure. Each measure needs to be weighed by container volume by Port of Discharge (POD), column "H" and Final Destination, column "I", then we have to determine how many days by volume make up the 90th percentile. Thank you!

[Kimber154]

Good morning Richard and Excel help Team - Any luck with the attached! Thank you everyone for your contributions!!

[Glenn Kennedy]

That is a truly awful sample. Totally incomprehensible. I don't have the patience to try to figure out what you want from that splurge of cells. What do you expect to see? Where do you expect to see it? Cut it back to the bare essentials (delete all those irrelevant rows/sheets) and explain WHAT your expected result is (calculated manually)...

[AliGW]

The issue of the cells that don't populate is being addressed in the other thread here: https://www.excelforum.com/excel-gen...o-another.html

I have removed your post that refers to that issue - please don't post about it here.

The only issue that should be addressed in THIS thread is the weighting question.

If things start to get muddled, I shall be obliged to close one of the threads, so please don't muddy the waters.

[Kimber154]

Hi AliGW - Thank you.

[Kimber154]

Question - it is taking a lot of time to delete rows..can I delete 20,000 at a time quicker?

[JeteMc]

There are 59 rows and 69 columns of data on the Data 2 sheet.
Instead of deleting it would seem like it would be quicker to hide the rows/columns that are not needed to illustrate the issue and then do as Richard stated: show the manually calculated results of the sample data and explain how those calculations are made.
Let us know if you have any questions.