Finding next 3 highest values after certain criteria

[jamblo]

Hi everybody

I am having some difficulty in coming up with a formula and could really do with some help.

I have 16 columns, split into 8 'category A' and 8 'category B'. These will be filled with numerical values when populated.

The formula I'm trying to work out is to get the sum of the next 3 highest values (of either category A or B), after you have taken out the top 2 category A and top 2 category B values. It's this different category business that I can't get my head round.

As an example the table looks something like this, values simplified and colours added for clarity example table.jpg

Any help or suggestions are much appreciated.
Many thanks.

[jamblo]

Sorry this is the actual excel file rather than just a picture example.xlsx

[Ace_XL]

Try

Please Login or Register  to view this content.

[jamblo]

Thanks Ace_XL, I had thought of that and tried it, but it only works only if there are definitely 2 category A and 2 category B values.

For example, please see attached spreadsheet,
example2.xlsx

If there are no category B values, the formula gives an outcome of 500, when it should in fact be 300.

Thanks for your help

[jamblo]

I couldn't find anything in the rules about not being able to bump this back up so hope that's ok with the administrators(?). Apologies if it's not.

The only thing I can think of is somehow using the IF function combined with the above fomula, but it's beyond me at the moment to work out how.

[FDibbins]

we normally prefer a little longer duration between posts, but a bump is fine

try this....
=SUM(LARGE(C15:G16,{3,4,5})) (assuming your range is C15:G16 - adjust as needed)

[jamblo]

Thankyou FDibbins. Good to know.

That formula works for that specific sum (row 7 on the attached sheet), but I have added another example in row 9 where it doesn't.

example3.xlsx

Perhaps I am not explaining what result is required that well? In which case I'll have another go:

I need to find the sum of the next 3 highest values(in the attached spreadsheet this is in column U, called 'Best 3 of the rest), from the given total range, after the 2 highest values of a range within that range (call it category A range) and another 2 highest values from a different range within the total range (call that category B range) are taken out. It's hard to explain in words but should make sense when looking at the data I hope.

Many thanks

[dredwolf]

you could try this:

Please Login or Register  to view this content.

This is based on range "A" as being the one to have values, you would have to adjust for it being range"B"

Hope this helps

[Ace_XL]

Try this array formula..

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and not just Enter

[dredwolf]

or...hopefully this non-array formula, I haven't tested EVERY possible cobination yet, but it does so far seem to do the trick :

Please Login or Register  to view this content.

Hope this helps

[jamblo]

Thankyou @Ace_XL and @dredwolf

I tried both your array and normal formulae and from multiple different scenario testing (read 'all that I could think of') I think they both work perfectly.

In the end I went with the non-array formula because I understood it more and could manipulate it to what I needed.

If you were interested...the actual problem is for a running club championship series with different races, with some being on road, and some off-road - these are the different 'categories'... I think it would have been easier if I'd explained that straight away.

The actual data isn't in nice neat ranges so I had to sum the COUNTIF's together, to give the messy formula in column X:

Example4.xlsx

but it works a treat, and I'm extremely grateful for all of the time and work you put in.

Thanks again

I'll mark this as solved.

[dredwolf]

You are welcome

[daddylonglegs]

Sorry, I'm late to the party but this non-array version should work for your original example

=SUM(LARGE(IF({1;0},LARGE(B3:I3,{3,4,5}),LARGE(J3:Q3,{3,4,5})),{1,2,3}))

You can expand the ranges out into individual cells for your "real" formula, i.e. for X2 try

=SUM(LARGE(IF({1;0},LARGE((C2,D2,G2,J2,L2,M2,P2,Q2),{3,4,5}),LARGE((E2,F2,H2,I2,K2,N2,O2,R2),{3,4,5})),{1,2,3}))

when I put some random values in C2:R2 I get different results for my formula and yours - I think mine is working correctly.........

[jamblo]

when I put some random values in C2:R2 I get different results for my formula and yours - I think mine is working correctly.........

so do I!...funny, I'm sure when I was checking yesterday it was working though. I must have made an error somewhere when trying to transcribe the example formula onto the running spreadsheet.

Your formula is a lot tidier than my efforts anyway... and it works 100% of the time which helps

Thanks daddylonglegs