Not sure if this can be done and have tried a couple different formulas ( conditional )

I have a spreadsheet for a cow show that lists the exhibitors in column A

in columns c:v same row as name I have their point value according to how they place in each class

I need to pick out the top 6 placings from their row and put that total in end total column

is there a formula that will do that or am I wasting time

after each exhibitors top 6 are figured we then take top one from those numbers

didn't know how to attach file here but this gives general idea of what spreadsheet would look like like

under each class number 1r etc would be the box if they showed in that class to place their points awarded

thanks in advance!!

Heifer/dry cow class Points Cow class Points

Class # 1r r2 3r 4r 5r 6r 7r 17r 9r 10r 11r 12r 13r 14r 15r 16r total

placing 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10 Points

points awarded 10 9 8 7 6 5 4 3 2 1 20 18 16 14 12 10 8 6 4 2

Exhibitor

1

2

3

4

Register a free account to unlock additional features at BleepingComputer.com

Welcome to

**BleepingComputer**, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having**no ads**shown anywhere on the site.**Click here to Register a free account now!**or read our

**Welcome Guide**to learn how to use this site.

# excel formula

Started by
katiecalf
, Jul 01 2009 10:14 AM

4 replies to this topic

### #1

Posted 01 July 2009 - 10:14 AM

### BC AdBot (Login to Remove)

m

### #2

Posted 01 July 2009 - 03:36 PM

It will help greatly if you could post a clear picture of sample data and lookup tables, as well as a clear picture of expected results for the sample data.

I suspect the enswer lies somewhere in nested-IF statement, sumIF, and perhaps Advanced filter.

If the lookup tables aren't clear, Match and Index functions in addition to Vlookup or Hlookup might come handy.

I suspect the enswer lies somewhere in nested-IF statement, sumIF, and perhaps Advanced filter.

If the lookup tables aren't clear, Match and Index functions in addition to Vlookup or Hlookup might come handy.

### #3

Posted 01 July 2009 - 04:24 PM

A little bit about part 1 of your post even though I still don't imagine your data

I think you will also need the rank function.

So if your points are in a row under those 20 or so "placing" headings, you may need to make a row for ranks under the points, where in each of the cells you'll have something like this:

Imagining further 2 rows with 10 in cell c14, and 14 in cell c15

points 10 9 8 7 6 etc

rank 14 13 11 10 8 etc

=RANK(C14,$C$14:$V$14,1) in colC

=RANK(D14,$C$14:$V$14,1) in colD

which you'll get by just dragging across the formula in c15.

Then you'll do SUMIF of those values in the points row which correspond to ranks above 14 or the highest rank-6.

But what if there are ties? What if you don't have 20 values filled? What if you have less than 6 values filled?

I think you will also need the rank function.

So if your points are in a row under those 20 or so "placing" headings, you may need to make a row for ranks under the points, where in each of the cells you'll have something like this:

Imagining further 2 rows with 10 in cell c14, and 14 in cell c15

points 10 9 8 7 6 etc

rank 14 13 11 10 8 etc

=RANK(C14,$C$14:$V$14,1) in colC

=RANK(D14,$C$14:$V$14,1) in colD

which you'll get by just dragging across the formula in c15.

Then you'll do SUMIF of those values in the points row which correspond to ranks above 14 or the highest rank-6.

But what if there are ties? What if you don't have 20 values filled? What if you have less than 6 values filled?

### #4

Posted 03 July 2009 - 11:24 AM

If you're still listening:

I may have come to grips with your design once pasted your message into excel.

It will actually be better if you put the ranks to the right of your points 20 columns C:V. For instance to X:AQ. Perhaps the total could be in between, in colW.

That will make it easier to calculate the max of the entries as well as expand/fill the range with formulas for ranks.

Also, while the columns C:V are fixed, row number have to move, therefore the formula should be

=RANK(C14,$C14:$V14,1) in colC where we drop $ before the row number in my previous version.

Then in column W you pick ranges above 14 and add up the points corresponding to those ranges, like this:

=SUMIF($X73:$AQ73,">14",$C73:$V73) and pull them down for all Exhibitor rows.

Finally, to declare the overall winner, get a max of column W

=MAX(W14:W114) assuming you got 100 or so Exhibitors.

I have a problem with picking 6 highest because ties could/will do funny things. But since this is only guessing this design, I leave it like that.

I may have come to grips with your design once pasted your message into excel.

It will actually be better if you put the ranks to the right of your points 20 columns C:V. For instance to X:AQ. Perhaps the total could be in between, in colW.

That will make it easier to calculate the max of the entries as well as expand/fill the range with formulas for ranks.

Also, while the columns C:V are fixed, row number have to move, therefore the formula should be

=RANK(C14,$C14:$V14,1) in colC where we drop $ before the row number in my previous version.

Then in column W you pick ranges above 14 and add up the points corresponding to those ranges, like this:

=SUMIF($X73:$AQ73,">14",$C73:$V73) and pull them down for all Exhibitor rows.

Finally, to declare the overall winner, get a max of column W

=MAX(W14:W114) assuming you got 100 or so Exhibitors.

I have a problem with picking 6 highest because ties could/will do funny things. But since this is only guessing this design, I leave it like that.

### #5

Posted 06 July 2009 - 02:07 PM

I know it was difficult to understand and I couldn't figure out how to attach the original spreadsheet.

I actually do not do so well with formulas in excel so get lost very easily

the simple way to explain what I was trying to do was this

each exhibitor has a row

across the row there are 20 different boxes exp: c:6 thru v6 with a possibility of a set amount of points in any of the columns

say for example a person showed 10 animals c6: would have value of 10

E6: would have value of 8

G6:2 J6: 8 F6:5 I6:20 k6: 18 L3:12 S6:20 T6:5

that would give you 10 placings and I need the top 6 placings from that row

some exhibitors will only have 4 animals entered then of course the total would be just the 4 placings

really don't think this can be done at any rate not how I would know how to do it

thanks for your help though

I actually do not do so well with formulas in excel so get lost very easily

the simple way to explain what I was trying to do was this

each exhibitor has a row

across the row there are 20 different boxes exp: c:6 thru v6 with a possibility of a set amount of points in any of the columns

say for example a person showed 10 animals c6: would have value of 10

E6: would have value of 8

G6:2 J6: 8 F6:5 I6:20 k6: 18 L3:12 S6:20 T6:5

that would give you 10 placings and I need the top 6 placings from that row

some exhibitors will only have 4 animals entered then of course the total would be just the 4 placings

really don't think this can be done at any rate not how I would know how to do it

thanks for your help though

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users