Averaging (only some) Data in a row of cells

[Jim Varner]

On volunteer basis, I "officiate" over a group of golfers and keep score data so I can use average scores to make up fair team competitions.

I want to list names down the first column and dates of play in a row across the top---then have the average returned of the most RECENT 6 scores into a column next to the name column.
In other words, if a golfer has posted six scores I would want that average returned. BUT, then when I enter the 7th score, the average of the first score of the 7 is
no longer used and the average of scores 2 through 7 is returned. Or 3 through 8. Or 4 through 9, etc. (YELLOW in example file)

Also, If a golfer has fewer that 6 scores posted, I'd like the average of whatever number of scores are present to date. (Orange in example)

I'm unsure whether or not this calculation is even possible in Excel.

Thanks...


Using Home and Student 2019 Example file should be attached)

[AliGW]

See if this works for you:

=AVERAGE(AA2:INDEX(C2:AA2,LARGE(COLUMN(C2:AA2)*(C2:AA2<>0),6)))

[Crooza]

You could put this in any blank cell in row 2 and drag down

then enter it as a array formula which means enter + shift + control

Please Login or Register  to view this content.

[wk9128]

Cell AC2 formula , drag down

[AliGW]

Given we are looking for the highest 6 containing data, I really don't think this is necessary:

COLUMN(C2:AA2)-2

[Jim Varner]

Thank you much for tackling this....

Trying to apply your formula in column AD in the file you've named "last 6 averages" into A2 on the top portion of my (actual) data sheet which I've attached here, I do not get the desired result. I get #VALUE!.
I have changed the range on your titled page from C5:AA5 to D2:BO2 in my formula. (You may notice that on my worksheet, all the scores have been manually moved to the right [that's the step I'm trying to make
unnecessary so I can keep actual dates each player has participated across the top] so I could average the last six posted scores)

Thanks again,
Jim Varner



Attached file named "Top 6 Rows:

[AliGW]

So, obviously, provide a workbook that has the same layout as your real data.

[Crooza]

The spreadsheet gave the average of the coloured cells so I’m not clear on what you’re after.

[wk9128]

Try Cell A2 array formula , Drag down

Please Login or Register  to view this content.

[Crooza]

You had a heap of spaces in your data in row 2. I deleted it and my formula at least seems to work.

@wk9128 - is your formula getting hte highest 6 scores or the most recent 6? I thought the OP was asking for the most recent which I think my formula does but I'm wondering if yours is getting the highest 6 scores.

[Jim Varner]

Hey to all who have been giving me input on this issue. Thanks and much appreciated. You all belong in MENSA. Crooza, yes your formula is great. When I insert new columns for new dates and entries, the BO2 in the formula no longer encompasses the the desired cell range. In an effort to include a new date with data I added a new column which now became BP2 and revised your formula from BO2 to BP2 and added a score but did not get a revised average of the most recent 6 scores but got a VALUE! message. I need to be able to endlessly add new date columns with scores and hopefully have a formula that will account for that. What am I doing wrong?

[Crooza]

They are array formulas. Press control + shift + enter rather than just enter and they will work

[Crooza]

Got your message. Try this.

A few comments too:
1. There is no need to put all your data to the far right. Just create date labels in row 1 and put the score for the corresponding player in the corresponding date column.
2. Leave dates where there is no score empty (don't put spaces or other text there)
3. I added about 15 columns to the right and adjusted the formula. If you're running out of columns and need more don't add them to the end. Rather before entering the last column with data select that column and ad as far to the right as you want and insert. As long as the starting column you select is within the present formula range it will automatically expand the formula to pick up your new range

[Jim Varner]

Thanks to everyone who has assisted in solving my issue and getting me a working formula for my worksheet. Your efforts and expertise are surely appreciated.

Jim Varner

[AliGW]

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Jim Varner]

I've got the formula which is outstanding...it's a complex one. I'm waiting for one more little piece of info from Crooza which will get me totally solved and I'll mark is SOLVED when I no longer need to bother you guys. Thank you for your assist with my issue. JV

[AliGW]

You'll need to explain if you want that extra help - in your previous post you had said that everything was solved.

[Jim Varner]

Well, I had sent Crooza a private message seeing if he could give me a specific step by step on how to move the formula from my example worksheet, which is working perfectly, to my big file. As can be seen from my earlier comments on this thread, my EXCEL IQ is limited. All the ways I know of on copying and pasting is not getting it. I guess what I need is a keystroke-by keystroke, cell-by-cell specific primer on precisely how to do it. Does the Excel Moderator Community laugh at us Excel mere-mortals and our ignorance? : > ) I guess it's like driving a car.\: I need and depend on a vehicle. I'm an excellent driver. But have no idea how a catalytic converter works.

All that said, I'm very grateful for the Excel Forum resource where we can go for help.

[AliGW]

Does the Excel Moderator Community laugh at us Excel mere-mortals and our ignorance?

Where have I given you this impression? No, we don't. I am self-taught - almost everything I know has been learnt here. I am very well aware that some members will be at the beginning of their journey, as I was some years ago.

Just for your information, you should ask for extra help here within the thread (because the help you are asking for may help others, too). You should not be sending PMs to members to ask for help (see Rule #8) - I am grateful to Crooza for responding here in the thread and not via PM.

I am still not clear about why you feel unable to mark this thread as solved - what further help do you need?

[Jim Varner]

No, no. You are mis-reading my comment. No one has given me that impression. I just feel like I'm asking stupid stuff and not properly understanding the guidance I've been given and frankly am in awe of you smart folks than can figure this complex stuff out which is way over my pay grade. I am nothing but grateful for the help I am given and think this forum is a great asset for us wannabes. Friends. Peace.

I have been presented a perfect formula on the test worksheet I attached early on--that does exactly what I need it to do. The final piece is for me to correctly copy that formula from my test worksheet over to my "big" all-inclusive worksheet. All the ways I know of on copying and pasting is not getting it. I guess what I need is a keystroke-by keystroke, cell-by-cell specific primer on precisely how to do it.

[AliGW]

Sorry - you need to tell us what you mean by "All the ways I know of on copying and pasting is not getting it."

We can't give you instructions unless you tell us what's going wrong - you need to be specific. You can't say "It hurts!" to a doctor and expect him to be able to diagnose and prescribe from just hose two words.

Things you can check:

1. Have you changed cell references and ranges so that they reflect the real dataset?
2. What happens? Do you get error messages? If so, what are they? If not, in what way are the results not as you expect?
3. Is there anything about the real dataset that is fundamentally different to the sample set?

Your formula is simple enough:

=VLOOKUP(CR2,$CT$3:$CV$6,3, TRUE)

CR2 will be the cell to the left of the formula cell - change if necessary.

$CT$3:$CV$6 is your lookup table - make sure the range in the formula matches the location in your real dataset.

Nothing else needs changing.

[Jim Varner]

Crooza, Thanks for your continuing help. I got your latest worksheet where you added columns and where your formula worked
perfectly as the ranges of columns expanded.

I have tried unsuccessfully to get the formula over to my main file.

Could you possibly do it for me ? I've attached my workbook here (I think). The worksheet is the first tab entitled SCORE SHEET: the
COLUMN that needs the formula is Column C.

[Hat in hand] thank you.

Jim Varner

[Crooza]

Nothing attached

[Jim Varner]

Went to advanced and clicked uploaded so hopefully it's attached here. Thanks.

[Crooza]

Put this in columns C then enter as an array Shift + control + enter then drag down column C

Please Login or Register  to view this content.

If you get an error it's because some of your blank cells have spaces so highlight the blank cells and hit delete to remove any text content from them.

[Jim Varner]

Crooza...thank you very much for bearing with me on this issue and getting me the formula that I needed. It will save me a lot of work and time.