Choosing & Adding Six Numbers from Eight in Excel

[Tarquine]

A pal of mine would like to have a list of runners in the left-hand column and in each of the next eight columns would be the event placings for eight different races. From these eight positions he would like to select only the first six placings and total them<br>
<br>
We decided that he would need to use SUMIF. However we cannot figure out how to setup the criteria for such a calculation. Does anyone know how this can be done? The names of each runner must be down the left-hand side.<br>
<br>
Any help would be nice<br>
<br>
Regards<br>
<br>
Tarquine

 

[DougP]

=SUMIF(B1:B8,&quot;&lt;6&quot<br>
so you would get a total at the bottom of each column then total or whatever all of the columns<br>
Maybe???? <p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[Tarquine]

Ah! I think I didn't explain this correctly. Each competitor will receive some points between 0 and 200. The competitor's names must be down the left-hand side, see below for example:<br>
<br>
Names Loc1 Loc2 Loc3 Loc4 Loc5 Loc6 Loc7 Loc8 Total<br>
<br>
Fred 60 52 150 25 25 132 78 65<br>
Harry 75 200 175 30 0 67 190 185<br>
Tom 169 93 115 95 165 100 150 111<br>
Etc (imagine that the numbers line up under each heading)<br>
<br>
The loc will donate the venues. What we are trying to do is take the top 6 races, from a possible 8 races, for each competitor, total them and place the result to the right-hand side of the row. Of course we cannot sort them on this page as it cause the results to get messed up.<br>
<br>
I hope this makes it a lot clearer and you can provide me with an answer.<br>
<br>
Regards<br>
<br>
Tarquine

 

[dsawford]

You will need a area of your spreadsheet set up for intermediate calculations. You could use any place on this sheet, or even another sheet. I think you could then write a macro that would sort each line individually in the intermediate area and leave the original data unsorted; however the following method should give you the results you want without a macro.<br>
<br>
For this example, I am inserting 11 columns between Loc8 and Total (J through T).<br>
After the formulas are all entered, you can then hide these columns. You could also put these columns elsewhere as mentioned above, and adjust the formulas accordingly.<br>
<br>
J: =MINA(B2:I2) *** find first minimum value<br>
K: =COUNTIF(B2:I2,J2) *** count occurrences of this minimum value<br>
L: =IF(B2=$J2,300,B2) *** get Loc1 value unless it is the min. value, in which case make it a large value (i.e. 300)<br>
M through S: *** copy column L into these columns.<br>
T: =MINA(L2:S2) *** find minimum value of new table.<br>
U (Total column): =SUM(B2:I2)-(IF(K2&gt;=2,J2*2,J2+T2)) *** This takes the sum from the original row, subracts from it either: (first min. * 2) if 2 or more occurrences, or if only 1 occurrence, subtracts (first min + 2nd min).<br>
This will give the total of the top 6 values for this row. You can then copy J through<br>
U down as far as you need to cover all runners.

 

[Tarquine]

Many many thanks for your suggestion. I have made up a spreadsheet using your instructions and the results appear to be exactly what we were looking for. <br>
<br>
Once again thanks for taking the time out to answer my questions.<br>
<br>
Regards<br>
<br>
Tarquine

 

[Tarquine]

Ah! I think I might have been a bit premature with my praise. When I said that I had placed it into a spreadsheet I should have said that it was in Excel 2000. And yes it works a treat. However, if the same is attempted in Excel 97 then it will not accept MINA(L2:S2) as a valid value. At least that is what Excel 2000 says when I try to save it that format.<br>
<br>
I have tried to carry out the same instructions using Excel 97 and there appears to be a problem in that all the values in columns M through S return the row one value all the way down that column.<br>
<br>
Is this correctable or should I get my pal to spend a bit more money and purchase the latest edition<br>
<br>
Regards<br>
<br>
Tarquine

 

[dsawford]

Tarquine: <br>
I used Excel97 myself to set up and test this, so it should work.<br>
The first thing to check if it is always bringing in the first row values when you copy it down is where the $ sign is in the formula.<br>
=IF(B2=$J2,300,B2) is the correct formula: the $ before J column tells it to always refer to J column even when copied elsewhere. Make sure that a $ did not also get in front of the 2 which would always refer to the second row instead of the same row relative to it's current position. The other columns on row 2 should also read $J2 in the formula after copying, but the 2 should automatically change to the current row as you copy it down if there is no $ in front of the #.<br>
I have not used Excel 2000, so have no idea why 'Save As' might give problems.<br>
Check this and let me know,<br>
dsawford

 

[Tarquine]

Ok! You win I had followed your instructions to the letter and for some reason the computer with Excel 97 on it was not refreshing correctly. After I had sorted that bit out all was well.<br>
<br>
Again many thanks for the answer my query.<br>
<br>
Regards<br>
<br>
Tarquine