• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating percentage on Excel

602589

New Member
Hi,

This may be a long winded question.....

In the attached picture, I am trying to work out the average frequency of a route I have driven this year in percentage. I have blacked out the routes themselves, so lets go by the row numbers in Excel. So rows 8 and 9 have an overall percentage driven of 8.64% and 5.76%. However lets just say that row 9 was last driven a couple of months ago, whereas row 8 was driven yesterday. How can I get a percentage in the right hand side to show how frequent or not so frequent each year the route is that has been driven? So something like the percentage will lower each day, and then when it gets driven again, the percentage will raise once again, continuing in that cycle.

Thank you and sorry if it is confusing or doesn't make much sense.
 

Attachments

vletm

Excel Ninja
602589
Upload an Excel-file which You already have
or
would You like to have ... some modifications ... in Your uploaded picture?
 

vletm

Excel Ninja
602589
Okay - I used Gimp.
Buses Driven Excel Edit.png
... and one more time that hint ... an Excel-file would be more useful.
... with expected results.
 

vletm

Excel Ninja
602589
... with a sample data
= instead 'real route' change those eg with one letter from a to z.
Dates ... hmm? .. as You ask something based those
... okay ... You could eg add fixed number to each Your date, then nobody cannot know Your personal dates neither.
 

Peter Bartholomew

Well-Known Member
Some sample data to work on would have been helpful. Too realistic and perhaps you expose personal information; too abstract and you have more of a problem applying the advice to your specific case.

An approach might be count routes as 'partial trips' as they get older with some characteristic time T0 controlling the time over which the trip counts only half, then a quarter etc.

instead of a simple count
= SUM( IF(trip=route, 1, 0) )
you would have
= SUM( IF(trip=route, EXP(-(today-date)/T0), 0) )
 

vletm

Excel Ninja
602589
Here one very rough sample.
Data has ... my random data. I've use letters for routes.
Result has ... one sample of results
> With cells A2 & A3 modify layout. Test Yourself.
 

Attachments

Peter Bartholomew

Well-Known Member
The image is based upon @vletm's data and shows a normal distribution used to weight the importance of each trip by their proximity to a specified date. Route T is ranked first despite not being the most frequently travelled route (orange bars) because it was travelled a disproportionate number of times early on (the average of the associated dates shown by the black marker is below average).
71579

The table is built using Excel 365 functionality so would need to be reimplemented in its entirety.
Code:
= LET(
  routes,   UNIQUE(Table1[route]),
  counts,   COUNTIFS(Table1[route], routes),
  meanDate, AVERAGEIFS(Table1[date], Table1[route], routes),
  wtedSums, SUMIFS(Table1[weight], Table1[route], routes),
  totalWt,  SUM(Table1[weight]),
  combined, CHOOSE( {1,2,3,4}, routes, counts, meanDate, wtedSums/totalWt),
  SORT(combined, 4, -1) )
 

Attachments

Top