I have a predictive model for MLB games. I use the following formula to take averages of the last four numbers in a single column. But the numbers I am using are duplicates. I would like to keep this formula, but have it disregard the duplicate values. Is that possible? Thanks in advance.
Average Without Duplicates.xlsx
Please Login or Register to view this content.
Last edited by IdabaMalouki; 04-07-2025 at 03:55 PM.
This should work in 2021...
In B164, enter
=AVERAGE(TAKE(DROP(TAKE(UNIQUE(FILTER($A$2:$F$163,$E$2:$E$163<>"")),-4),,COLUMN(B164)-1),,1))
and copy to C, E, and F....
Bernie Deitrick
Excel MVP 2000-2010
I receive a #NAME? error. I need it to take the average of the B column only, then C only, then E only, and then F only. Each column needs to be its own unique average. Does that make sense? Apologies, but I posted wrong cell numbers in the above code from an older sheet.
I'm hoping there is some way to simply nest something into the AVERAGE code that just takes the average of the two unique numbers within 4 cells, and eliminates the duplicates.
Last edited by IdabaMalouki; 04-07-2025 at 04:10 PM.
2021 doesn't have TAKE or DROP.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Unfortunately, that's what I'm working with. What Excel V. has TAKE or DROP?Originally Posted by TMS
Sorry - I skipped Excel 2021, straight from 2016 to 365. Excel 365 has TAKE and DROP... try this, which is clunkier but may work with 2021
B164:
=LET(v,INDEX(UNIQUE(FILTER($A$2:$F$163,$E$2:$E$163<>"")),,COLUMN(B164)),AVERAGE(INDEX(v,COUNTA(v)),INDEX(v,COUNTA(v)-1),INDEX(v,COUNTA(v)-2),INDEX(v,COUNTA(v)-3)))
copy to C, E, and F
Maybe this for column B, and dragged across for column C:
Formula:
Please Login or Register to view this content.
Adjust ranges for columns D:F.
Or, maybe just use Remove Duplicates before you do anything?
Excel 2024 has TAKE and DROP if you don't want a subscription version.
Still no luck. The idea is to take the last 4 values in column B:
5.00
4.25
4.25
4.25
And get the average of 5.00 and only 4.25.
If the formula takes the AVERAGE of:
5.00
4.25
4.25
4.25
The result is 4.44.
But what I would like the formula to do is take the average of just 5.00 and 4.25, which is 4.63. So I need the formula to just take the 5.00 and 4.25 of the last four cells. The existing formula works fine, but averages the three 4.25 values with the 5.00 which throws the true average off. The difference between 4.44 and 4.63 is significant in baseball.
Edit: deleted. Please see next attempt.
Last edited by TMS; 04-07-2025 at 05:48 PM.
Edit: deleted. Please see next attempt.
Last edited by TMS; 04-07-2025 at 05:48 PM.
Edit: apologies. It requires a slight tweak.
Or this?
Formula:Please Login or Register to view this content.
Last edited by TMS; 04-07-2025 at 05:47 PM.
No luck. B163 is blank
Did you drag the formula across?
Updated file.
It depends on what you mean by "Unique" values - my formulas treated unique based on a complete repeat of the entire row, not just the numbers.
Last edited by Bernie Deitrick; 04-07-2025 at 06:23 PM.
My latest formula determines the last four rows and then takes unique values based on columns A:F. Again, not based on just numeric values.
In the attached remove row 164. Then in H2 array enterThen copy/paste into I2, K2 and L2.Formula:Please Login or Register to view this content.
This returned 4.625, 5.875, 3.375, and 5.625
Format according to needs.
I don't know if you have to array enter in Excel 2021 or not, but if you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Dave
This one is simpler than my previous and does not require deleting row 164 or array entry.
In B164 and copy/pasted into C164, E164 and F164Format as Number with 2 decimal places and these will round automatically.Formula:Please Login or Register to view this content.
They return 4.63, 5.88, 3.38 and 5.63
That works great! However, in the sheet I use that formula, I was hoping to use it further down the sheet. For full clarity, I am attaching a new Excel sheet that shows all the calculations I will need using your formula. I will need your formula for cell B1153, C1153, E1153, and F1153. I tried using your formula in B1153. I tried switch B$2 with B$991, but it didn't work. Thanks in advance.
Average Without Duplicates.xlsx
To which post are you referring?
Edit: And by-the-way this looks like a completely different question with different logic and expectations.
Please clarify. It appears you no longer want an average of unique last 4 values.
Last edited by FlameRetired; 04-09-2025 at 02:32 PM.
Sorry for confusion. I'm referring to your last post with simplified formula. Post #20.
In the above new spreadsheet I posted, I need a formula that takes the average of the last 4 unique numbers in column B991 thru column B1152. You formula worked fine in the original spreadsheet I posted. But when I used your formula to take average in column B991 thru column B1152, it doesn't work.
Ok. I think I am understanding now. Will the data layout/groupings be the same throughout? If so this will require calculating dynamic ranges and will take me a while.
OK. It was a problem with the absolute cell references not applying to the new ranges.
Try this in B164Then copy/paste to C164, E164 and F164 .... B1153, C1153, E1154 and F1153.Formula:Please Login or Register to view this content.
Continue with the other summary rows.
Thank you, sir. Works perfectly. I really appreciate your work on this.
Thanks for the rep.
You are welcome. Thank you for the feedback, added rep and marking your thread SOLVED.
Sorry, but quick question. Can the A Column in your formula be locked with $A like so below?
Formula:Please Login or Register to view this content.
Last edited by IdabaMalouki; 04-14-2025 at 09:24 AM.
Sorry, but quick question. Can the A Column in your formula be locked with $A like so below?
Formula:Please Login or Register to view this content.
Have you tried it?
Yes. And it works
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks