Highest two values out of three added together

[[email protected]]

Hi all,

I have these cells (within columns) with scores and rankings. I need a formula in the Combined Highest Two Scores cell (column) to add together the highest two scores from Score 1, Score 2 & Score 3. Please note that not all of the cells in the columns may have results and may contain #N/A from formulas already within those cells.

Score 1 Rank 1 Score 2 Rank 2 Score 3 Rank 3 Combined Highest Two Scores
42.233 10 42.816 13 43.000 8 XX.XXX

Thanks in advance.

[FDibbins]

1. If you have formulas returning NA, I suggest you resolve those 1st
2. Take a look at using SUMIFS, with the criteria being =Score 1 (etc) and LARGE()

However, to give better suggestion, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Saqib Qureshi]

Use this Formulae if the scores are placed in A:C

Please Login or Register  to view this content.

It is better to handle #N/A error at Individual Scores only.

[[email protected]]

Thanks for your replies and suggestions guys.

I have had another go at the formula and used =SUM(LARGE(AN2:AR2,1)+LARGE(AN2:AR2,2)) which seems to work as a work around as the scores in the range are going to be higher than the rankings (hope that makes sense). I would like to resolve this with a proper formula so I don;t run into any issues later.

I've attached a sample "clean" workbook for you to see what I'm trying to achieve.

You can see in column At that the formula =SUM(LARGE(AN2:AR2,1)+LARGE(AN2:AR2,2)) works OK if there are absolute values in the ranges however there are two issues; 1, the formula does not work with and ranges with #N/A in the cells and 2, I have "cheated" a little in the workbook as AGC Trial #3 has not completed yet (I simply duplicated the date from AGC Trial #2 to use as an example to test my formula). If I delete the sample data from AGC Trial #3 then the formula in AT does not work. Agian, I'd like to resolve this so the 2 highest scores are added together from Trial 1, 2 or 3 regardless if there are scores in Trial 1, 2 or 3.

Hope that makes sense guys.

[Bo_Ry]

Please try AT2

=SUM(AGGREGATE(14,6,AN2:AR2,{1,2}))

For more accurate, this will not sum rank#

=SUM(AGGREGATE(14,6,AN2:AR2/(LEN($AN$1:$AR$1)<24),{1,2}))
or
=SUM(AGGREGATE(14,6,AN2:AR2/{1,0,1,0,1},{1,2}))

[Pepe Le Mokko]

Or simply = SUM(A1:A3)-MIN(A1:A3)
Adapt references to fit

[[email protected]]

Thanks Bo_Ry, This seems to work really well. I'll apply it to the live workbook and hopefully it resolves my issue.

Thanks heaps.

[[email protected]]

Thanks everyone for your input, I've learnt some new things along the way which is always a good thing.

Really appreciate everyone input.

It looks like the suggestions from FDibbins & Saqib Qureshi to resolve the #N/A issues first worked really well and Bo_Ry's solution might just do the trick.

Awesome collaboration guys, greatly appreciated

[[email protected]]

Hi guys,

Sorry to be pain.

Just need to tidy up the workbook a little with some extra conditional formatting.

I basically need to format the Columns AU Green if AU= "Yes" and Column AV Red if AV ="No"

I also need to format Column E Green if both AU & AV = "Yes" and/or format column E Res if either Au & Av = "No"

Hope that makes sense

[[email protected]]

Hi guys,

I resolved the issue with conditional formatting the Columns AU Green if AU= "Yes" and Column AV Red if AV ="No"

However I'm still a little stuck on how to conditionally format Column E Green if both AU & AV = "Yes", conditionally format column E Orange if either AU & AV = "No" and conditionally format Column E Red if both AU & AV = "No"

Hope that makes sense

[AliGW]

You need three CF rules for E1:

=AND(AU1="Yes",AV1="Yes")

=AND(AU1="Yes",AV1="No")

=AND(AU1="No",AV1="No")

Once you have set up the rules for E1, make sure that you use the Applies To box to apply the rules to the entire column (e.g. $E:$E or a finite range such as $E1:$E500).

[[email protected]]

Awesome, Thanks AliGW. Worked Perfectly. I just forgot to include the condition =AND(AU1="No",AV1="Yes")

[AliGW]

No problem!

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

[[email protected]]

Damn, one last thing

I'd like to conditionally format cell AT1 to check if any of the Cells AN1 or AP1 or AR1 are greater that the value in worksheet A1. If any of the cells are greater then I'd like to conditionally format cell AT1 Green, if not then I'e like to leave the cell AT1 unformatted or formatted "No Fill Colour"

Hope that makes sense

[AliGW]

Try this:

=OR(AN1>$A$1,AP1>$A$1,AR1>$A$1)

[[email protected]]

Opps, I forgot to add a condition ..... if cell N="Level 8"

[AliGW]

This?

=AND(N1="Level 8",OR(AN1>$A$1,AP1>$A$1,AR1>$A$1))

I think you need to play around a bit with the AND and OR functions so that you learn how they work.

[AliGW]

So did my suggestion work?

If so, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[[email protected]]

Hi AliGW, looks like it's working a treat. I'm adding the two CF together to CF the cell with the combined scores. It seems to be working OK if I use the Stop If True check box in the CF but not if I order the CF's in order of processing?

[AliGW]

Sorry - we are getting to the stage where you are going to need to provide a sample workbook AND explicitly list what you are expecting to happen. Does it matter that you need stop if true? You don't say why that is an issue. It's all too sketchy at the moment - I can't see the whole picture.

[[email protected]]

Hi AliGW, I'm pretty sure it's now working as expected. I'm getting one of the other volunteers at our club to check it out and test it. I've spent sooo long on this workbook that I cannot see the wood fro the trees so hopefully a fresh pair of eyes will find any glaring errors or omissions.

Thanks heaps for all of your assistance, it is greatly appreciated.

I'll certainly let you know how it goes.

Stewie

[AliGW]

Yes, please do let us know. Glad to have helped.

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

[[email protected]]

Hi AliGW,

Damn, just found another major issue that I need to resolve.

There are some names in column E that are the same (i.e. Smith) and the work book is returning the same results for E Smith and I Smith.

How can I get the formula that is say in cell AN8 ( =IF(ISNA(VLOOKUP($E8,AGC_Trial_1,20,FALSE)),0,VLOOKUP($E8,AGC_Trial_1,20,FALSE)) ) to match First Name (Column C) and Last Name (Column E) and return the correct value form the data sets on AGC Trial #1, AGC Trial #2 & AGC Trial #3?

I have uploaded a sample workbook for you.

[AliGW]

Maybe try this instead?

=IFERROR(LOOKUP(2,1/(AGC Trial #1'!$E$2:$E$21=$E8)*(AGC Trial #1'!$C$2:$C$21=$C8,'AGC Trial #1'!$X$2:$X$21),0)

[[email protected]]

Hi AliGW,

Sorry that doesn't appear to work? The formula isn't even accepted?

Just to note I used this formula ( =IF(ISNA(VLOOKUP($E8,AGC_Trial_1,20,FALSE)),0,VLOOKUP($E8,AGC_Trial_1,20,FALSE)) ) because a member might not compete in all of the Trials but I still want to collect the data from when they do trial.

I basically need a formula like =VLOOKUP($E12,AGC_Trial_1,18,FALSE) to match the First Name & Last Name and then return the match from AGC Trial #1, AGC Trial #2 & AGC Trial #3?

I tried =VLOOKUP($C12 & $E12,AGC_Trial_1,18,FALSE) but that didn;t work either.

[AliGW]

Sorry - try this:

=IFERROR(LOOKUP(2,1/(AGC Trial #1'!$E$2:$E$21=$E8)*(AGC Trial #1'!$C$2:$C$21=$C8),'AGC Trial #1'!$X$2:$X$21),0)

There was a missing bracket.

Obviously you need one of these for each worksheet tabs you are looking at (as you have done with your VLOOKUPS). It's up to you to fit it to your requirement - this is just ONE VLOOKUP replacement.