Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello experts!
I've run into an interesting average problem and any help from you guys will be highly appreciated.
Basically I have 5 columns. Day, Date, Future Date, ID and service. For each ID we provide different services and record the date we provided service. We usually provide service consecutively and sometimes there are different dates. We flag this as Day 1, Day 2.
Also in Future, for each ID a future date is assigned. I want to calculate the average for each ID per day basis.
Lets have a look at the table first.
Now these are the future dates:
Days Future Date ID
1 | 07/01/2017 | A |
2 | 08/01/2017 | A |
1 | 13/02/2017 | B |
2 | 15/02/2017 | B |
So these dates and ID should be in straight table and when no future date is selected then this should be the average calculated:
But when the user selects 07/01/2017, as this date is assigned to Days 1 then only Days one past dates are used to calculate average.
Average = (4+10)/2
= 7
Similarly, if the user selects 15/02/2017 and as this date is assigned to Days 2 then only days 2 dates for this ID is used for average calculations.
Average: = (5+10) / 2
= 7.5
I know it's a bit tricky but this is where I need your help! I've attached both the app and data file if anyone is willing to help. Thanks
Its me again (you are going to start hating me), try this:
=Sum(Aggr(If([Future Date] > 0, Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} Service)/Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} DISTINCT Date)), ID, [Future Date]))
No Selection
07/01/2017 selected
15/02/2017 selected
Its me again (you are going to start hating me), try this:
=Sum(Aggr(If([Future Date] > 0, Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} Service)/Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} DISTINCT Date)), ID, [Future Date]))
No Selection
07/01/2017 selected
15/02/2017 selected
Hey Sunny!
The average problem continues...
I've just posted a discussion Tricky Average! which is a bit of an extension of my other discussion. Would you be able to have a look at it please?
Thanks