Adding the 3 greatest values from selected cells without duplicates?!

Mitchell Arthur

New Member
Joined
May 4, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Good Afternoon all,

Firstly, im not sure this is possible but its worth a shot!

I have a spreadsheet which has multiple rows of people and their exam score data. Each students final mark is their 3 highest scores however there is a certain combination of the scores required.

The combination has to be Highest Team Sports score + Highest Individual Sports score + Next highest team OR individual sports score. I have attached a simple picture below for reference.

Persons A score should total 25 and Persons B score should total 30.

Due to the depth of my real spreadsheet which holds data for over 150 students if this score was generated automatically every time scores got updated my life would be much much easier!

If anyone can help, i will forever be in your debt!

Many Thanks

Mitchell

1588620343749.png
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Welcome to the Board!

Try:

Book1
ABCDEFGHIJK
1TeamIndividualTeamIndividualTeamIndividualTeamIndividualCorrect Answer
2Person A510104324125
3Person B104386125530
Sheet22
Cell Formulas
RangeFormula
K2:K3K2=MAXIFS(B2:I2,B$1:I$1,"Team")+MAXIFS(B2:I2,B$1:I$1,"Individual")+MAX(AGGREGATE(14,6,B2:I2/(B$1:I$1="Team"),2),AGGREGATE(14,6,B2:I2/(B$1:I$1="Individual"),2))


Edit: Sorry, I just noticed you have Excel 2016 which doesn't have MAXIFS, try instead:

=AGGREGATE(14,6,B2:I2/(B$1:I$1="Team"),1)+AGGREGATE(14,6,B2:I2/(B$1:I$1="Individual"),1)+MAX(AGGREGATE(14,6,B2:I2/(B$1:I$1="Team"),2),AGGREGATE(14,6,B2:I2/(B$1:I$1="Individual"),2))
 
Upvote 0
Hi Eric, thanks for your response.

Copying this formula resulting in #NAME? to come up in the cell. I tried to enter manually but there is no MAXIFS formula.

I may just be being an idiot as im not that Excel savvy, any ideas?

Thanks

Mitch
 
Upvote 0
Hi Eric, thanks for your response.

Copying this formula resulting in #NAME? to come up in the cell. I tried to enter manually but there is no MAXIFS formula.

I may just be being an idiot as im not that Excel savvy, any ideas?

Thanks

Mitch
Check the last line of my post. MAXIFS came out in the 2019 version. I realized that after I posted, and edited the post to use AGGREGATE instead which came out in 2013.

=AGGREGATE(14,6,B2:I2/(B$1:I$1="Team"),1)+AGGREGATE(14,6,B2:I2/(B$1:I$1="Individual"),1)+MAX(AGGREGATE(14,6,B2:I2/(B$1:I$1="Team"),2),AGGREGATE(14,6,B2:I2/(B$1:I$1="Individual"),2))
 
Upvote 0
Here another array formula for you to consider:

varios 04may2020.xlsm
ABCDEFGHIJK
1TeamIndividualTeamIndividualTeamIndividualTeamIndividualAnswer
2Person A510104324125
3Person B104386125530
4Person C5161718217
Hoja8
Cell Formulas
RangeFormula
K2:K4K2=MAX(IF($B$1:$I$1="Team",B2:I2))+MAX(IF($B$1:$I$1="Individual",B2:I2))+MAX(LARGE(IF($B$1:$I$1="Team",B2:I2),2),LARGE(IF($B$1:$I$1="Individual",B2:I2),2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Eric,

This is fantastic and it worked! I hate to ask but my spreadsheet is much more in depth than my example so translating the formula over is way past my grade, and may be everyone considering its length.

Is there any potential way i can get the first row or spreadsheet to you to try and figure out for me?

Really appreciate all you help, and totally understand if not.

Thanks again

Mitch
 
Upvote 0
There are a few ways to get a spreadsheet here. First and best is to use the XL2BB add-in. Check the XL2BB button in the response box. It's quite easy, you download a file, put it in the Excel ADDINS folder, and restart Excel. Then you just select the range you want to show, click the XL2BB button, then go to a response box here and click Control-V (paste). Second way is to paste a picture, which you did. Final way is to upload your workbook to a file sharing service like Google or Dropbox and post the link here. But be aware that some people can't or won't download files from the net due to security concerns.

What is the difference from the screenshot you showed? Headers in a different place, extra columns not shown? Do you think Dante's formula is easier to get? It's the same basic logic as mine, but uses more familiar functions. It does require knowledge of how to enter/use array formulas though, which is a big topic.
 
Upvote 0
1588624009724.png

Hi Eric,

I have tried to add a picture of my spreadsheet. Not sure if you can see it but worth a shot.

I need to formulate the best 'team total (25)' + best 'individual total (25)' + next best 'team OR individual total (25)'.

If you somehow pull this off, i will forever be in your debt sir!
 
Upvote 0
I managed to extract enough (I think) information from your picture to adapt the formula. This is a good example of why showing a layout as close to your original as possible is important. There are enough significant differences that the overall solution could have been a lot different. It's also important to note that merged cells can also be a real issue. In this case though, I think the original formula can work with just a few modifications. Consider:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Practical scoresrugby Union (TEAM)Football (TEAM)Netball (TEAM)Badminton (Individual)Badmindon (TEAM)Table Tennis (Individual)
2
3Davison, HarryMTDU62551010152507916791631221
Sheet22
Cell Formulas
RangeFormula
E3E3=AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("team",F$1:BN$1)),1)+AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("individual",F$1:BN$1)),1)+MAX(AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("team",F$1:BN$1)),2),AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("individual",F$1:BN$1)),2))


I only typed in as much as I think I needed to. Another reason XL2BB is good, I could have copied and pasted the entire range. Assuming F1:H1, J1:L1, N1:P1, etc. are all merged cells or Center across Selection, and the scores you want are in columns H, L, P, etc. then the formula in E3 should work. Notice that the score ranges (H3:BP3) are offset by 2 columns from the header ranges (F$1:BN$1).

AGGREGATE is used 4 times in this formula. Here's a breakdown of one of them.

=AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("team",F$1:BN$1)),1)

In red are the scores, plus a lot of other numbers from other columns we don't care about: 10, "", 10,15,25,"","","",0, etc.

In blue are the headers. Since the cells are merged, to a formula it appears the F1, J1, N1, etc. have values and the rest are empty, so: ruby Union (TEAM),"","","","Football (TEAM)", etc. Then SEARCH is used to find "team" within each entry on the list. If found, it returns the location within the phrase, if not found it returns an error. The ISNUMBER looks at the location or error and returns TRUE/FALSE (or 1/0). Then these numbers are divided into the first list (red). Dividing by 1 gives the original number, dividing by 0 gives an error. So the combined list looks like:

10, #DIV/0!, #DIV/0!, #DIV/0!, #DIV/0!, 25, #DIV/0!, #DIV/0!, #DIV/0!, 0, etc.

Finally, AGGREGATE is a composite function. The 14 means find the nth LARGEst value, the 6 means ignore all the errors, and the 1 at the end is n, so find the 1st largest score that has "team" 2 columns ahead of it.

The next AGGREGATE works the same way for "Individual". The last 2 get the 2nd largest values for "team" and "individual" (notice the 2), then MAX takes the highest of them.

Hope this helps!


Dave, thanks for the clarification. I checked Microsoft's web page, but stopped reading once I was sure it existed in the OP's version. Mea culpa.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top