# 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

• 83.4 KB Views: 11

#### vletm

##### Excel Ninja
602589
or
would You like to have ... some modifications ... in Your uploaded picture?

#### 602589

##### New Member
Some modifications in the pic will do please

#### vletm

##### Excel Ninja
602589
Okay - I used Gimp.

... and one more time that hint ... an Excel-file would be more useful.
... with expected results.

#### 602589

##### New Member
Hi, Thanks, but the file has my personal data on, which I prefer not to share.

#### 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.

= 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

• 27.3 KB Views: 8

#### 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).

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

• 66.4 KB Views: 3