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

Find out who has the highest average cost

B

budda919

New Member
Good morning all,

I have an excel formula question that has been stumping me this morning.


My data: A list of all employee travels for a one year period, including traveler name, travel departure date, whether the trip occurred or was cancelled (authorization status), and cost of travel. If an employee travel three times in a year, there is a separate row for each of the 3 trips.


My desires (more like my bosses desires) are: Two Formula 1) Find the traveler who averages the highest cost per trip during a given time frame, such as, during the month of January. 2) How much is his/her average cost per trip during that time frame.


I have created a formula that finds which employee travels most, but I'm not sure how to transition this to who has the highest average. Anyone want to take a stab at it? Any and all help is appreciated.


Formula for who travels the most:

{=(INDEX(data[Traveler Name],MATCH(MAX(FREQUENCY(IF((data[Depart]<=1/31/13)*(data[Depart]>=1/1/13)*(data[Authorization Status]<>"Cancelled"), COUNTIF(data[Traveler Name], "<"&data[Traveler Name]), ""), COUNTIF(data[Traveler Name], "<"&data[Traveler Name]))), FREQUENCY(IF((data[Depart]<=1/31/13)*(data[Depart]>=1/1/13)*(data[Authorization Status]<>"Cancelled"), COUNTIF(data[Traveler Name], "<"&data[Traveler Name]), ""), COUNTIF(data[Traveler Name], "<"&data[Traveler Name])), 0)))}
 
depending on layout of data, a pivottable sounds like a real easy solution as it would combine all records belonging to the same person to calculate the totals/averages.
 
Hi budda919,


I will recommend you post a sample workbook with any sensitive / company specific data removed.


This will get you more specific / accurate responses.


To post sample take a look at this post:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi budda919,


While i agree with Shrivallabha's suggestion, you can see this file as well:


http://dl.dropbox.com/u/60644346/Highest%26Avg..xlsx


Regards,
 
Thanks Faseeh. I like the approach, but the problem is first knowing all the individuals names that will appear in the data. I do not, as the names can change with each new run of the data. ** I mispoke. Your formula in column E does find the name of the traveler, but how far down would I have to pull the formula to ensure I encompass all travelers every month? There are new travelers and data expanding into the thousands.


I've uploaded a sample file here.

http://dl.dropbox.com/u/3217811/sample.xlsx
 
Hi budda919,


If the data is too too large then might ask someone else for a macro, but if it is just reasonably large then you can stick to a formula solution, convert ranges into table and ease yourself, see the new sheet in this file:


http://dl.dropbox.com/u/60644346/Cost%20of%20Travel.xlsx


Regards,
 
Hello budda919,


Try this Array Formula (hit with CTRL+SHIFT+ENTER, not just ENTER)


Note: If you have huge data, this will slow down calculation performance.


Code: 
=INDEX(Data[Traveler Name],MATCH(MAX(AVERAGEIFS(Data[Amount],Data[Traveler Name],Data[Traveler Name],Data[Depart],">="&StartDate,Data[Depart],"<="&EndDate,Data[Authorization Status],"<>Cancelled")),AVERAGEIFS(Data[Amount],Data[Traveler Name],Data[Traveler Name],Data[Depart],">="&StartDate,Data[Depart],"<="&EndDate,Data[Authorization Status],"<>Cancelled"),0))


If you want to find 2nd, 3rd, 5th etc... high average names, try this Array Formula.


In A1: enter a heading ie: High Avg Traveler


A2: with CTRL+SHIFT+ENTER


=INDEX(Data[Traveler Name],MATCH(MAX(IF(ISNA(MATCH(Data[Traveler Name],A$1:A1,0)),AVERAGEIFS(Data[Amount],Data[Traveler Name],Data[Traveler Name],Data[Depart],">="&StartDate,Data[Depart],"<="&EndDate,Data[Authorization Status],"<>Cancelled"))),AVERAGEIFS(Data[Amount],Data[Traveler Name],Data[Traveler Name],Data[Depart],">="&StartDate,Data[Depart],"<="&EndDate,Data[Authorization Status],"<>Cancelled"),0))


Then copy A2 & Paste down as needed.


Hope this helps,

Haseeb
 
Excellent! Thank you so much. The performance seems good so far, but I have not fully loaded a years worth of data just yet.
 
Okay, so it appears that Haseeb's formula brings back the #Div/0! error if there is a Traveler in the list that does not meet the specified criteria (ie. They don't have travel between the Start and End dates, or all of their travel has a cancelled status.)
 
You must log in or register to reply here.
Back
Top