P
phillyfan
I have a spreadsheet to track my offices NCAA tournament selections.
Everything works great until I try to sort based on total points. I run
into trouble with the last columns of my table that calculates max.
points remaining based on future picks and the teams that are still
alive. The formula is:
=(SUM(COUNTIF(AI4:AX4,B29:AG29))*20)+(SUM(COUNTIF(
AZ4:BG4,B29:AG29))*40)+(SUM(COUNTIF(BI4:BL4,B29:AG
29))*80)+(SUM(COUNTIF(BN4:BO4,B29:AG29))*160)+(SUM
(COUNTIF(BQ4,B29:AG29))*320)+(AH4*10)
B29-AG29 are the list of the previous round's winners
AI4:AX4 are the picks for the 2nd round games (correct picks get
multiplied by 20)
AZ4:BG4 are the picks for the 3rd round games (correct picks get
multiplied by 40)
BI4:BL4 are the picks for the 4th round games (correct picks get
multiplied by 80)
BN4:BO4 are the picks for the 5th round games (correct picks get
multiplied by 160)
BQ4 is the pick for the championship game (correct pick gets multiplied
by 320)
AH4 is the number correct from the 1st round (get multiplied by 10)
The formula works great but I ran into trouble copy and pasting. The
ranges (AI4:AX4, AZ4:BG4, etc..) correctly became AI5:AX5 (6, 7, 8
etc...) when pasting below but the list they get compared to (B29:AG29)
needs to stay constant but it did not with copy-paste (they became
B30:AG30, B31:AG31 etc... ). I had to go back and change this range
individually. Now the problem - when I sorted based on current total
points the B29-AG29 range in the formula was given new and unique
values (B30:AG30, B42:AG42, B37:AG37 etc...). How can I lock B29:AG29
so this does not change after sorting.
Sorry for being so long but it was difficult to explain. Also this is a
free pool for my office with the winner getting 2 Phillies tickets -
just in case if some were concerned about gambling.
Thanks in advance, this board has always provided excellent help.
Everything works great until I try to sort based on total points. I run
into trouble with the last columns of my table that calculates max.
points remaining based on future picks and the teams that are still
alive. The formula is:
=(SUM(COUNTIF(AI4:AX4,B29:AG29))*20)+(SUM(COUNTIF(
AZ4:BG4,B29:AG29))*40)+(SUM(COUNTIF(BI4:BL4,B29:AG
29))*80)+(SUM(COUNTIF(BN4:BO4,B29:AG29))*160)+(SUM
(COUNTIF(BQ4,B29:AG29))*320)+(AH4*10)
B29-AG29 are the list of the previous round's winners
AI4:AX4 are the picks for the 2nd round games (correct picks get
multiplied by 20)
AZ4:BG4 are the picks for the 3rd round games (correct picks get
multiplied by 40)
BI4:BL4 are the picks for the 4th round games (correct picks get
multiplied by 80)
BN4:BO4 are the picks for the 5th round games (correct picks get
multiplied by 160)
BQ4 is the pick for the championship game (correct pick gets multiplied
by 320)
AH4 is the number correct from the 1st round (get multiplied by 10)
The formula works great but I ran into trouble copy and pasting. The
ranges (AI4:AX4, AZ4:BG4, etc..) correctly became AI5:AX5 (6, 7, 8
etc...) when pasting below but the list they get compared to (B29:AG29)
needs to stay constant but it did not with copy-paste (they became
B30:AG30, B31:AG31 etc... ). I had to go back and change this range
individually. Now the problem - when I sorted based on current total
points the B29-AG29 range in the formula was given new and unique
values (B30:AG30, B42:AG42, B37:AG37 etc...). How can I lock B29:AG29
so this does not change after sorting.
Sorry for being so long but it was difficult to explain. Also this is a
free pool for my office with the winner getting 2 Phillies tickets -
just in case if some were concerned about gambling.
Thanks in advance, this board has always provided excellent help.