EXCEL question

[HoustonHorn]

I'm trying to create a spreadsheet for a master's pool in which we pick 6 players and count the best 4 scores each day. I'm having some difficulty automatically summing up the totals and removing the two highest scores. I know there's a MAX function to locate the highest total, but is there a function to remove the "next" max?

I'd prefer if there was an easy function as opposed to a complicated if/then iteration, but I'm open to any suggestions.

Thanks!

 

[Bobbs40]

This may not be the best solution, but it might help. You can use the RANK function to return the rank of a number in a series relative to the other numbers in the series. That could help you identify the first and second highest scores.

 

[JohnnyM]

why didn't you just join mine? 600+ teams!!

try this:

=SUM(your range) - LARGE(your range,1) - LARGE(your range, 2)

that will work

 

[HoustonHorn]

Thanks for the suggestion. I think I could make that work except for ties. If there's a tie between the fourth and fifth best scores, by ranking them, they both come out with a score of 4, which means that I can't easily separate them to subtract only one of the scores.

 

[JohnnyM]

well, it works, that's all i can tell you. i've used it for a few years.

or maybe you weren't referring to mine?

 

[HoustonHorn]

sorry Johnny, I was responding to the post above yours...I will try that LARGE function...

Thanks

 

[HoustonHorn]

By the way, that LARGE function is EXACTLY what I was looking for. I was coming up with some fairly complicated if/then formulas - this simplifies my life greatly.

Thanks again!

 

[JohnnyM]

i feel your pain. i've screwed with a lot of formulas over the years. with my pool getting to 350+ teams last year and 600+ this year i needed to get the most efficient excel formulas possible.

how did you guys pick teams? just any 4?

 

[chitwood]

Johnny,
I was in your pool last year, but never saw any mention of it happening this year. Did you send anything out?

 

[JohnnyM]

****, i'm sorry. i tried emailing everyone that i could but i know i forgot some ppl because of others like you who sent the same kind of message! i didn't want to pimp it on here either, thought that might be against the rules or something. might do another for the us open though because of the turnout for this one.

 

[HoustonHorn]

We pick 2 from the top 10, two from 11-50, and two 50+. Top four scores per day count. If you don't have four players left after the cut, you're done.

This makes it a little more interesting than everyone picking any four players and everyone choosing the same 6-8 players. It also forces you to either know about golf or really do research to figure out some of the lower ranked players who might be sleepers.

 

[JohnnyM]

HH - i break mine into 6 tiers, and then there are options for picking six players from a variety of tiers. check out the website if you like masterspool

 

[HoustonHorn]

That's pretty impressive. Definitely count me in for next year. No wonder you know how to manipulate an excel spreadsheet.