How to COUNT/SUM/AVERAGE first X active cells in Y columns

[zakazano]

Hi,
Newbie here, have tried reading as many threads as I could before posting but couldn't find anything that quite matched what I want to do.

I am teaching myself how to model with sports data. This example relates to golf.

Each sheet of my model focuses on a statistic (here it is Stroke Differential Field Average) which I want to compare across events and players. If every player competed in every tournament, this would be simple (like comparing teams in a league), but golfers all have different schedules. So for the Last 4 Weeks summary, events played by the list of players ranges between 0 to 4 events, which naturally affects the strength of the data.

Rather than rigidly counting the last four weeks of tournaments, I would like to count the last four (or eight or...) tournaments for each golfer. That could be spread across 4, 6 or 20 columns. The data might use SUM, AVERAGE or COUNT depending on the nature of that stat.

As you'll see in the example, I'd prefer not to have to use single columns per tournament (eg O4:AZ4 won't work as the relevant stat is O4,R4,U4,X4,AA4,AD4,AG4,AJ4,AM4,AP4....), but if that's the only way it can be solved...



Have seen many mighty complicated problems solved in here, this one must be solvable...mustn't it?

[zakazano]

(deleted - changed heading)

[oeldere]

I used the code below to re-arange the data.

After that a pivot table.

See the attached file.

Please Login or Register  to view this content.

[zakazano]

Thanks but I can't understand what you've done in there. It doesn't seem to have done anything other than change the presentation. It's not counting the first four events played for anyone apart from those who played four tournaments in a row - which is what I already had.

[bebo021999]

In B4:

Please Login or Register  to view this content.

C4:

Please Login or Register  to view this content.

Copy to 8wks, change 4 to 8

J4:

Please Login or Register  to view this content.

K4:

Please Login or Register  to view this content.

Important note:

ALL:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[zakazano]

Thanks but I can't see what that has actually done apart from change the formula - the result is still the same.

Perhaps I haven't been clear, let me remove all the unnecessary noise and try to explain this another way.

[oeldere]

@zakazano

you replied in #4,

counting the first four events played for anyone apart from those who played four tournaments

I made an average of the first 4; of course it can also be made (in the pivot table) with a sum of the data.

Please reply what is incorrect at the result.

[zakazano]

Ignoring the Field Average and Eindtotaal columns which don't have any meaning, the Stroke Differential column in the Pivot Table is just a repeat of the data in the SDFA tab - but that's the data I wish to fix. So for golfers who only played 2 of the last 4 tournaments, you have only counted 2 of the last 4 tournaments.

What I need is to count the last four events that golfer has played (whether it is the last four tournaments conducted, or spread over several months).

I've simplified it in the post above (Excel expert query file) - that will filter out all the noise. See if that makes it clearer.

[oeldere]

not correct statement, will come back

[zakazano]

thanks - also please explain what I need to do with any VBA stuff you give me, it's not something I've ever used before.

[oeldere]

Don't worry about the VBA stuff (I will explain that later).



Last four => sort the data on column C => sort Z - A.

After that the new formula.

After that a pivot table.

I added count (all players, played at least 4 games).

See the attached file.

[zakazano]

I still can't see what that is actually doing. It's just saying 4 events, but not paying any attention to whether each individual player competed or not.

Take a look at Jon Rahm.

Under 4wks on SDFA tab, he has played only two events. SDFA avg = 2.91. --- you have this on the pivot table as his figure after 4 events.

Under 8wks on SDFA tab, he has played exactly four events. SDFA avg = 1.89 --- this is what should be showing for him under 'Last Four Events'.

[oeldere]

There went something wrong with the posted file (sorry for the unconviency).

Here is the correct one.

Jon Rahm 71,443 4 2,318 4


The average of the 4 last played games of Jon Rahn is 2.318

See the attached file.

The green cells in the sheet output.

[zakazano]

No, that doesn't match with the other sheet (1.89). Not sure what your sums are calculating, but it's not the correct data.

[oeldere]

No, that doesn't match with the other sheet (1.89). Not sure what your sums are calculating, but it's not the correct data.

In that case you think you have the correct formula in

(sheet) Score Diff Field Avg (cell) B10 =SUM(O10,R10,U10,X10)
=> they refering to the data => July 23 - 26 / July 16 - 19 / July 9-12 / July 2-5


In my opion that are the first 4 played games.


In my opinion the last 4 played games are
=> Oct 10 - 13 / Oct 3 - 6 / Sep 26 - 29 / Sep 19 - 22


Having said that, I think your conclusion that the average needs to be 1.89 is incorrect.

Please reply.

[zakazano]

I want the last four events per player so it's a true reflection of their performance, not just results by arbitrary time period.

See attachment for a clearer demonstration of the requirement.

[zakazano]

bump - still no closer to a solution.

[bebo021999]

Work with your initial sample in #1. Is it real-life file?

See atachment

[zakazano]

yes, it's a real sample.

I don't see how it is changing anything, apart from changing the formulae in the background.

EVERY player in the left grouping (4wks) should have data for exactly 4 tournaments, and the same for the 8wks group (with exception of Jason Day who had only played 7 events).


I can figure how to count (or average or sum) all the columns with values in them, what I can't decipher is how to limit the number of columns it gathers information from

eg
COUNT all events with data UNTIL Total Events Played =4 (this must be variable by player, not strictly by date)



For a simpler example with less irrelevant noise, try this one.

[bebo021999]

My solution in #18 did exactly what you expectation (I copy it from row 22), doesn't it?
With the latest file, it quite different from the initial file, you may find problem with bringing it into the real case.

[zakazano]

My solution in #18 did exactly what you expectation (I copy it from row 22), doesn't it?
With the latest file, it quite different from the initial file, you may find problem with bringing it into the real case.

It hasn't changed anything.

For Henrik Norlander, the 4wk data is exactly the same as he played the last four events.
His 8wk data (remembering I want to change this to last 8 events played) has not changed, it's still on 5 events.

This (F5)
=SUM(IFERROR((COLUMN($O$3:$CQ$3)/($O$3:$CQ$3="Stroke Differential")<=AGGREGATE(15,6,COLUMN($O$3:$CQ$3)/($O$3:$CQ$3="Stroke Differential"),8))*$O4:$CQ4,""))

does exactly the same as this (F5)
=SUM(O4,R4,U4,X4,AA4,AD4,AG4,AJ4)





Not one of the cell outputs has actually changed.



What I want to see is

F5 =SUM(O4,R4,U4,X4,AA4,AD4,AG4,AJ4) but when it finds AG4 and AJ4 are empty, then it keeps looking in every "Stroke Differential" column unless it finds two more values (which it would find in AV4 and BE4) to make up the required 8 events.

[JeteMc]

The following produces the same sums as those that were manually placed in cells B5:B6 :

Please Login or Register  to view this content.

The following produces the same averages as those that were manually placed in cells C5:C6 =B5/C$1
Note that the number of events to be summed/averaged is placed in cell C1.
Let us know if you have any questions.

[zakazano]

brilliant! Works on the example sheet, now to transfer it to the big file.

[zakazano]

this works great where the columns examined are only of the one type.

If they were like the initial example (attached), can the formula be adjusted to look at every 2nd/3rd/Xth column as required?

[JeteMc]

Try the following array entered formulas**:
For 4wks:

Please Login or Register  to view this content.

For 8wks:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself). Do this before the formulas are copied down.
Let us know if you have any questions.

[zakazano]

getting closer - that is counting the correct columns, but isn't completing 4 events or 8 events where possible.

Henrik Norlander for his last 8 tournaments should be F4 = 11.97


I can see how it is trying to derive the last no. of events from LEFT(F$2,1) and read only the Stroke Differential columns, which helps my understanding enormously, but I can't decipher why it isn't doing what you are asking.

[JeteMc]

Henrik Norlander for his last 8 tournaments should be F4 = 11.97

Please explain in detail because I get 10.69 when manually selecting what I believe are the appropriate cells: O4, R4, U4, X4, AA4, AV4, BE4 and BH4.
The following array entered formula (see post #25) will produce 10.69 in cell F4:

Please Login or Register  to view this content.

Let us know if you have any questions.

[zakazano]

Please explain in detail because I get 10.69 when manually selecting what I believe are the appropriate cells: O4, R4, U4, X4, AA4, AV4, BE4 and BH4.
The following array entered formula (see post #25) will produce 10.69 in cell F4:

Please Login or Register  to view this content.

Let us know if you have any questions.

You are correct (apologies for confusion), and that is simply brilliant.

Now for the fun task of working through that in detail so I can understand it enough to roll it out across all the stats sheets in this tracker.

Thanks again!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.