MS Excel Help please

[Junior]

Hi guys and gals.

I started my winter league this past Sunday and posted the results.  I'm using TD2.2.2 for the league stats and points calculations and then enter points into a spread sheet to post on my site.  I'm not a frequent user of Excel and I'm not sure how to enter a formula, nor can I find it in the help menu so i'm asking here.

What I've done is enter a formula in the points column to give the sum of all 8 satellite points.  (sum:C2,J2)
What I'd like it to do is to give me the sum of the 6 highest scores from those 8 columns.  Does anyone know how to modify the formula to drop the two lowest totals?

Thanks for your help.  To see our poker site visit here..... I owe thanks to Wedge as I fed off of his set up a little.

[halfnelsen]

I'm not an EXCEL guru but I liked your idea and wanted to do the same for my point standing a did a little research this expression should work
=SUM(C2:J2)-SMALL(C2:J2,1)-SMALL(C2:J2,2)
This will do a sum and then subtract the two smallest numbers. I'm sure there is an easier way, but I'm not much of a programmer.

[austin5string]

Well that's pretty darn cool.  I'm not an Excel guru, but I'm not bad with it, and I never knew about the SMALL function.  Question, though.  What happens if there are multiple entries w/ the same value?  Say, you have 1,1,2,2,3,4,5 and ask for the 2nd smallest?  Will it give you '1', as it should, or will it give you '2'?

[halfnelsen]

I just did it with multiple 1's and it realized the 1 was the smallest both times.

[austin5string]

I just did it with multiple 1's and it realized the 1 was the smallest both times.

Right, but what if you ask for the 2nd smallest?  Does it realize that the second smallest is also 1, or does it choose the next highest value (which isn't what you would want).

Use 1,1,2,2,3,4,5 and ask for the 2nd smallest and see if it gives you 1 or 2.

[halfnelsen]

Yeah the second smallest still gave 1 as an answer.

[Pops28]

I have posted a sample of the Excel sheet we use for our league. The small function is the way to go. Here is an example from our spread sheet:
=(F2-SMALL(G2:T2,1)-SMALL(G2:T2,2))
I have attached blank sample for you but as I couldn't attach an xls I had to change the extension to .txt. You will need to change it back to .xls. Column D (verage Denominator) is used to figure out column C (average) and needs to be modified each week. You can get rid of these columns if you would like. I have filled in the first line for you as a sample.

[icehawk]

If you are going to use the SMALL function, remember that you have to enter a zero for people that are on the roster, but did not play in a particular tournament.

Player 1 = 10,10,8, ,6,2,7,9 gives lows of 2 and 6.
Player 1 = 10,10,8,0,6,2,7,9 gives lows of 0 and 2.

[Junior]

Thanks a bunch guys.  This should do the trick perfectly.  I knew you guys were all good for something!!! lol

[DrunkAss]

I don't see my name in there shmeggie!

[Wedge Rock]

=SUM(C2:J2)-SMALL(C2:J2,1)-SMALL(C2:J2,2)

Funny...  I used Large(c2:j2,1)+Large(c2:j2,2)+Large(c2:j2,3)+Large(c2:j2,4)+Large(c2:j2,5)+Large(c2:j2,6)

Wanna get really slick?  Use conditional formatiing.  If [cell value] < Large (c2:c7,6) then switch the background to grey.  If you do this, it will automatically grey out the cells it is not using to calculate your best 6 of 8 score.  Makes it easier to understand how the formula is calculated, and it updates itself whenever you add a new tournament.

[halfnelsen]

=SUM(C2:J2)-SMALL(C2:J2,1)-SMALL(C2:J2,2)

Funny... I used Large(c2:j2,1)+Large(c2:j2,2)+Large(c2:j2,3)+Large(c2:j2,4)+Large(c2:j2,5)+Large(c2:j2,6)

Wanna get really slick? Use conditional formatiing. If [cell value] < Large (c2:c7,6) then switch the background to grey. If you do this, it will automatically grey out the cells it is not using to calculate your best 6 of 8 score. Makes it easier to understand how the formula is calculated, and it updates itself whenever you add a new tournament.

Thats how I first saw how to do it, but I thought it got a little long. The conditional formating will really make it tie together and get the point across. I'll have to add that to mine as well. Thanks.

[Dr. Neau]

Guys guys guys.  Just use an array formula!

The following formula will sum up the top 10 values found in A1 through A100.

{=SUM(LARGE(a1:a100,ROW(INDIRECT("1:10"))))}

Type the formula without the curlies, then instead of hitting "ENTER", hit "CNTL-ALT-ENTER".  That turns it into an array formula.

You can send donations via PayPal to drneau@drneau.com 

[ChrisChip10]

Counseling is available at "www.imacomputergeek.com".   

[Wedge Rock]

Neau, I tried your suggestion, but when I hit CTRL-ALT-DEL, my system shuts down...   

Any suggestions?

 

[Dr. Neau]

Neau, I tried your suggestion, but when I hit CTRL-ALT-DEL, my system shuts down...   

Any suggestions?

 

Your computer is clearly too heavy.  Try deleting some files from the hard drive.

[Slyy]

Neau, I tried your suggestion, but when I hit CTRL-ALT-DEL, my system shuts down...   

Any suggestions?

 

Your computer is clearly too heavy.  Try deleting some files from the hard drive.

It might be a good idea to start with the files in the C:\Windows folder.  I never can recognize most of those files, anyway.

[Wedge Rock]

Your computer is clearly too heavy.  Try deleting some files from the hard drive.

I spent an hour with an eraser yesterday...  I can get rid of any of the files...  And the retractable cup holder tray keeps getting in the way...

[Slyy]

Just don't use the foot pedal.  You might start losing files you need to keep.

[ChrisChip10]

Buy a frog, they like flies.

[Wedge Rock]

I've gotten a few PM's about conditional formatting.  I'll answer it here, so it only has to be answered once.

You have to do this player by player, because you are comparing the value of the cell to the array of that player's games.

1. Highlight the cells you want to apply the conditional formatting to (basically, the first game to the last game).

2. From the top menu bar select Format->Conditional Formatting

3. Condition 1: Set first drop down to "Cell Value Is"

4. Condition 1: Set second drop down to "less than"

5. Condition 1: Set the text box to =LARGE($B$2:$I$2,6)

Note: This assume eight games and you want to keep the highest 6.  Adjust your variables as needed.  Also, you could use set steo 4 to "greater then" and set step six to =SMALL($B$2:$I$2,2) and get the same results.

Note2: I use Dr. Neau's formula which generally produces results with decimals.  You may have to override this automatic formatting if the player has identical scores for some events (especially if those identical scores are right on the threshold of the cutoff).

7. Click the Format button.  You now need to set the formatting you want to display if the condition is true (i.e. if the number is too small to be used in the season totals calculations).  I grey out the background.  You could also set the font color to a light grey, assuming all the others are black, or set the font to regular when all the rest of the cells are bold.

To set the background as grey, from the "Format Cells" pop-up box, select the Patterns tab,  then choose the light grey shade (last color of the second to last row).  Click OK.

Repeat these steps for each player in your league.

There you go.  Conditional formatting done for that row.  You never have to do it manually.

[Junior]

Thanks Wedge....I'll edit my leader board when I get home tomorrow.

[Junior]

Ok wedgie,

I tried this and then filled out the fields across for the first player.  The total points formula worked fine, adding only the highest 6.  The conditional formatting however, only crossed out the lowest score and not the lowest 2!?  I'm baffled.  That was using the 'less than' =LARGE(C2:J2,6) formula. 

After that I tried reversing it to 'greater than' =(C2:J2,2) and it crossed out 4 of 8 scores.

I was almost at the point of my avatar being a home video for you all! lol

[Dr. Neau]

Ok wedgie,

I tried this and then filled out the fields across for the first player.  The total points formula worked fine, adding only the highest 6.  The conditional formatting however, only crossed out the lowest score and not the lowest 2!?  I'm baffled.  That was using the 'less than' =LARGE(C2:J2,6) formula. 

After that I tried reversing it to 'greater than' =(C2:J2,2) and it crossed out 4 of 8 scores.

I was almost at the point of my avatar being a home video for you all! lol

While conditional formatting is cool, I don't think it's going to do exactly what you want in this case.

Say your values are 1,1,2,2,3,3.  The 3rd largest value is 2.  You're now essentially saying "format everything less than 2 a special way".  The 1's get highlighted.  There's no way to make it format 1,1,2 rather than just 1,1.

BTW, did anyone bother trying my function?? 

[Wedge Rock]

Neau is exactly correct.  See my Note #2 above...  If you are dealing with whole numbers that may replicate, I wouldn't suggest conditional formatting, as you'll have too many over rides.

Shmegma, what numbers were you using?

I have this conditional formatting in place on the season totals page of my standings.  You can open the file directly in Excel and see how the formulas look.

Dr., I haven't messed with my Excel spreadsheet since you posted that, so no, I haven't tried it yet.  But I will.

[Junior]

I'm used numbers with 2 decimal points, same as yours.  I just filled in the board across with a bunch of numbers to see if the formatting would work.  I may have missed something but I'll look it over again and see if I can remedy it. 

Then if that doesn't work I'll see if I can attempt the good Dr.'s solution.

[Junior]

Um, ok I've got it working.  Stupid error.  Noticed the $ you had in the post you made and thought I was supposed to replace them with the column letter and row # I was using.  I added the $ symbols into each formula and it works like a charm now.  Thanks guys.

[Dr. Neau]

Um, ok I've got it working.  Stupid error.  Noticed the $ you had in the post you made and thought I was supposed to replace them with the column letter and row # I was using.  I added the $ symbols into each formula and it works like a charm now.  Thanks guys.

For those who don't know, putting the "$" in front of a row or column in a formula locks that value for copying and pasting.  Otherwise, the rows and columns are automatically adjusted based on the paste.

[Wedge Rock]

When you are inputting the formula, there's a small box over at the right of the typing box...  clicking that box will allow you you select cells from the spread sheet so you don't have to type a cumbersome formula manually...

[Junior]

When you are inputting the formula, there's a small box over at the right of the typing box... clicking that box will allow you you select cells from the spread sheet so you don't have to type a cumbersome formula manually...

sure, NOOOOOWWWW you tell me!  Thanks Wedgie.

[flipflop1970]

=SUM(LARGE(A:A,{1,2,3,4,5,6,}))