assigning a point value to cells

[AlienBeans]

Hey everyone.

I'm working on a college football poll for a website I moderate. What I'm trying to do is put a heading for each pollster listing their top 10 teams. Now, I have the sheets set up as individualweek 1..up to...individualweek10...etc.. and totalsweek1..up to...totalsweek10. I have the individualweek pollsters listed and when I enter their poll I want it to automatically put the right point value under the appropriate pollster in the appropriate week. The way i want it is the Number 1 ranked team to get 10 points...Number 2 to get 9 points....all the way to number 10 ranked team recieving ONE point. What is the best formula combination to use for this particular workbook?? Any help will be greatly appreciated. I've attached the workbook to this thread for anyone willing to look at it to help and understand it easier.

[Bryan Hessey]

Hey everyone.

I'm working on a college football poll for a website I moderate. What I'm trying to do is put a heading for each pollster listing their top 10 teams. Now, I have the sheets set up as individualweek 1..up to...individualweek10...etc.. and totalsweek1..up to...totalsweek10. I have the individualweek pollsters listed and when I enter their poll I want it to automatically put the right point value under the appropriate pollster in the appropriate week. The way i want it is the Number 1 ranked team to get 10 points...Number 2 to get 9 points....all the way to number 10 ranked team recieving ONE point. What is the best formula combination to use for this particular workbook?? Any help will be greatly appreciated. I've attached the workbook to this thread for anyone willing to look at it to help and understand it easier.

Not quite sure on your request.

In your worksheet, the top 10 ranked teams can be allocated the 10 down to 1 points with the formula

=15-ROW()

or with the formula

=LARGE(AB$5:AB$14,ROW()-4)

or, if you want to pretend that the list isn't sorted into rank order, with the formula

=11-RANK(Y5,Y$5:Y$29,0)

---

[AlienBeans]

Let me see if i can explain it a little better. Grab the attachment to this message, and take a look at the sheet labeled: IndividualWeek1 and look at Column B cells 5-14.

I want cell B5 to have a value of 10 points, cell B6 to have a value of 9 points...etc...all the way to cell 14 having a value of 1point. Now notice the heading in cell B4. I want these values to then go to the appropriate sheet titled: TotalsWeek1 (in this case). UNDER the appropriate person,in this case, pcolatiger. Does this make more sense now??

I want this value to continue across the sheet all the way to Column J. So J5 will equal 10 points. Then when Florida is entered in the number 5 spot(like you see in the example) then 6 points are put under the appropriate person(pcolatiger) and on the appropriate totalsweek sheet. Does this make better sense??

[Mallycat]

I have read through this and looked at the attachments. It is still very hard to understand what you are trying to do. Here's what I can't understand

I want cell B5 to have a value of 10 points, cell B6 to have a value of 9 points...etc...all the way to cell 14 having a value of 1point.

You can't make cell B5 = Ohio State and also = 10 at the same time. The cell is either Ohio State or 10. Do you mean that if the user selects Ohio State for their pick in week 1, that they are somehow allocated 10 points (because Ohio State is the first team in the list)?

Now notice the heading in cell B4.

I have looked at cell B4 and there is no heading there. It is a blank cell.

I want these values to then go to the appropriate sheet titled: TotalsWeek1 (in this case). UNDER the appropriate person,in this case, pcolatiger. Does this make more sense now??

No, it doesn't. How are you going to enter the selections each week? Are you planning on writing the name of the team selected in cell F5 on sheet TotalsWeek1? If so, then what happens with it.

How about you enter the selections for one person as you want to see it, and then enter the values that you want automatically generated, and enter some comments in all the cells so it is easy to see what you want to happen.

Matt

[AlienBeans]

[QUOTE=Mallycat]I have read through this and looked at the attachments. It is still very hard to understand what you are trying to do. Here's what I can't understand


You can't make cell B5 = Ohio State and also = 10 at the same time. The cell is either Ohio State or 10. Do you mean that if the user selects Ohio State for their pick in week 1, that they are somehow allocated 10 points (because Ohio State is the first team in the list)?[/quote

Yes..well, they will be picking (ranking) 10 teams...so yes if they rank ohio state first then that is worth 10 points on the totals sheet for that week. Whatever team they pick 2nd will get 9 points...

I have looked at cell B4 and there is no heading there. It is a blank cell.

sorry, meant B3

No, it doesn't. How are you going to enter the selections each week? Are you planning on writing the name of the team selected in cell F5 on sheet TotalsWeek1? If so, then what happens with it.

I will copy and paste the top 10 for each person. I want it to automatically put the proper point value in the totals sheet under the appropriate player.

How about you enter the selections for one person as you want to see it, and then enter the values that you want automatically generated, and enter some comments in all the cells so it is easy to see what you want to happen.

Matt,

Look at the PreSeason sheets and you will see what i mean. Look at the sheet INDIVIDUAL-PRESEASON and look at column B... See B3 heading?? Now look at the 10 teams under pcola. Now if you go to the TotalsPresason sheet, you will see 10 points assigned to Texas (under pcolas column)because they were ranked Number 1. 9 points assigned to USC because they were 2nd...etc.... now does it make more sense??

[Bryan Hessey]

Let me see if i can explain it a little better. Grab the attachment to this message, and take a look at the sheet labeled: IndividualWeek1 and look at Column B cells 5-14.

I want cell B5 to have a value of 10 points, cell B6 to have a value of 9 points...etc...all the way to cell 14 having a value of 1point. Now notice the heading in cell B4. I want these values to then go to the appropriate sheet titled: TotalsWeek1 (in this case). UNDER the appropriate person,in this case, pcolatiger. Does this make more sense now??

I want this value to continue across the sheet all the way to Column J. So J5 will equal 10 points. Then when Florida is entered in the number 5 spot(like you see in the example) then 6 points are put under the appropriate person(pcolatiger) and on the appropriate totalsweek sheet. Does this make better sense??

Cell B5 has the value of 10 points in the formula

=11-Match( ?what? ,B$5:B$14,0)

but where do you get what pcoltiger selected?

---

[AlienBeans]

Cell B5 has the value of 10 points in the formula

=11-Match( ?what? ,B$5:B$14,0)

but where do you get what pcoltiger selected?

---

On the sheet labeled IndividualWeek1 Look at Column B underneath pcola. If cell b5 has a value of 10 points, then in this case Ohio State would receive 10 points on the totals sheet

[Bryan Hessey]

On the sheet labeled IndividualWeek1 Look at Column B underneath pcola. If cell b5 has a value of 10 points, then in this case Ohio State would receive 10 points on the totals sheet

But cell B5 has 'Ohio State' - - where do the '10 points' come from?

cell b5 could be classifird as 'worth' 10 points if you used the prior formula, otherwise what you ask is making no sense.


added, or did you mean that cell F5 on Totals Week 1 was

=11-Match(B5,Individualweek!B$5:B$14,0)

and F9

=11-Match(B9,Individualweek!B$5:B$14,0)
for 10 points?

except that you wanted to further Match the name to give the column B offset for the lookup range?

---

[AlienBeans]

But cell B5 has 'Ohio State' - - where do the '10 points' come from?

cell b5 could be classifird as 'worth' 10 points if you used the prior formula, otherwise what you ask is making no sense.

Thats exactly what i meant.... WORTH 10 points.

added, or did you mean that cell F5 on Totals Week 1 was

=11-Match(B5,Individualweek!B$5:B$14,0)

except that you wanted to further Match the name to give the column B offset for the lokup range?

the team listing on column B of the totals sheet is random, but wherever ohio state is listed then if they are ranked number 1, it will find them on column B of the totals sheet and add the appropriate number of points....

Where do you live? Could I call you?? lol...

send me a PM...
i figured the vlookup function would work but not sure how to go about implementing it...

[Bryan Hessey]

Thats exactly what i meant.... WORTH 10 points.




the team listing on column B of the totals sheet is random, but wherever ohio state is listed then if they are ranked number 1, it will find them on column B of the totals sheet and add the appropriate number of points....

Where do you live? Could I call you?? lol...

send me a PM...
i figured the vlookup function would work but not sure how to go about implementing it...

Perhaps it would help if your example pcoltiger were listed on IndividualWeek1, it might be less confusing.

For each person you will need to match THEIR correct column on IndividualWeek1 (not column B)

Easy way is to have matching columns and use the same column name on both sheets.
Second is to (at least) have the same name on each sheet and do a Match to set the column.
Third way is to alter the formula:

=11-Match(B5,Individualweek!B$5:B$14,0)

=11-Match(B5,Individualweek!C$5:C$14,0)

=11-Match(B5,Individualweek!D$5:D$14,0)

---

[AlienBeans]

Perhaps it would help if your example pcoltiger were listed on IndividualWeek1, it might be less confusing.

For each person you will need to match THEIR correct column on IndividualWeek1 (not column B)

Easy way is to have matching columns and use the same column name on both sheets.
Second is to (at least) have the same name on each sheet and do a Match to set the column.
Third way is to alter the formula:

=11-Match(B5,Individualweek!B$5:B$14,0)

=11-Match(B5,Individualweek!C$5:C$14,0)

=11-Match(B5,Individualweek!D$5:D$14,0)

---

pcolatiger is listed on Week1.....it might be labeled as pcola, but its the same person....cell B3 has pcola.....and his picks are listed underneath. I'm at a lost to get these pages linked and get the formula to calucate itself. Otherwise i've got to manually enter every value in the right cell and that would be a lot of work...

[Bryan Hessey]

pcolatiger is listed on Week1.....it might be labeled as pcola, but its the same person....cell B3 has pcola.....and his picks are listed underneath. I'm at a lost to get these pages linked and get the formula to calucate itself. Otherwise i've got to manually enter every value in the right cell and that would be a lot of work...

Didn't the three options work for you ?

[AlienBeans]

Didn't the three options work for you ?

Nope....got a #REF! error....

[Bryan Hessey]

Nope....got a #REF! error....

Perhaps Excel is not sure of what you are trying to do, or from which cells to draw information from to do that.

Which cell did you put the formula in, and what was the formula that produced the error?

---

[AlienBeans]

Perhaps Excel is not sure of what you are trying to do, or from which cells to draw information from to do that.

Which cell did you put the formula in, and what was the formula that produced the error?

---

=11-Match(B5,Individualweek!B$5:B$14,0)

entered the above formula on the totalssheet1 page, in cell F5 and it returned an #NA

perhaps i'm not putting the formula in the right place?? I'm not sure....I know this can be done...I'm sure of it....just don't know how to go about getting in accomplished....

[Bryan Hessey]

=11-Match(B5,Individualweek!B$5:B$14,0)

entered the above formula on the totalssheet1 page, in cell F5 and it returned an #NA

perhaps i'm not putting the formula in the right place?? I'm not sure....I know this can be done...I'm sure of it....just don't know how to go about getting in accomplished....

You do not have a worksheet of that name, my original entry was a typo.

Correct the sheetname and try again.

---

[AlienBeans]

You do not have a worksheet of that name, my original entry was a typo.

Correct the sheetname and try again.

---

OK...I'm an idiot..it works...now, only one more issue. Let's use Ohio State as an example for just a minute. Let's say, pcola doesn't put ohio state in his top 10. That means next to Ohio State under pcola's column will be an #NA....I need this to be a zero (0). How do I accomplish that???

[Bryan Hessey]

OK...I'm an idiot..it works...now, only one more issue. Let's use Ohio State as an example for just a minute. Let's say, pcola doesn't put ohio state in his top 10. That means next to Ohio State under pcola's column will be an #NA....I need this to be a zero (0). How do I accomplish that???

Test if it will produce an error, and put a zero

=11-Match(B5,Individualweek!B$5:B$14,0)

becomes

=if(iserror(11-Match(B5,Individualweek!B$5:B$14,0)
),0,
=11-Match(B5,Individualweek!B$5:B$14,0))

or
=If(iserror(11-Match(B5,Individualweek1!B$5:B$14,0)),0,11-Match(B5,Individualweek1!B$5:B$14,0))

(untested)

---

[AlienBeans]

Test if it will produce an error, and put a zero

=11-Match(B5,Individualweek!B$5:B$14,0)

becomes

=if(iserror(11-Match(B5,Individualweek!B$5:B$14,0)
),0,
=11-Match(B5,Individualweek!B$5:B$14,0))

or
=If(iserror(11-Match(B5,Individualweek1!B$5:B$14,0)),0,11-Match(B5,Individualweek1!B$5:B$14,0))

(untested)

---

AWESOME....appears to have worked....thank you so much for your help and your patience...

[Bryan Hessey]

AWESOME....appears to have worked....thank you so much for your help and your patience...

Thanks for your response, and remember to keep the names the same, you can (later on) use those to match so that one formula will do a block rather that a row.
You can always 'pick up' the name from =Sheet1!A1 etc
-------------