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.
run into trouble with the last columns of my table that calculates max
points remaining based on future picks and the teams that are stil
alive. The formula is:
=(SUM(COUNTIF(AI4:AX4,B29:AG29))*20)+(SUM(COUNTIF(AZ4:BG4,B29:AG29))*40)+(SUM(COUNTIF(BI4:BL4,B29:AG29))*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 ge
multiplied by 20)
AZ4:BG4 are the picks for the 3rd round games (correct picks ge
multiplied by 40)
BI4:BL4 are the picks for the 4th round games (correct picks ge
multiplied by 80)
BN4:BO4 are the picks for the 5th round games (correct picks ge
multiplied by 160)
BQ4 is the pick for the championship game (correct pick gets multiplie
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. Th
ranges (AI4:AX4, AZ4:BG4, etc..) correctly became AI5:AX5 (6, 7,
etc...) when pasting below but the list they get compare to (B29-AG29
needs to stay constant but it did not with copy-paste (they becam
B30:AG30, B31:AG31 etc... ). I had to go back and change this rang
individually. Now the problem - when I sorted based on current tota
points the B29-AG29 range in the formula was given new and uniqu
values (B30:AG30, B42:AG42, B37:AG37 etc...). How can I lock B29:AG2
so this does not change after sorting.
Sorry for being so long but it was difficult to explain. Also this i
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.
run into trouble with the last columns of my table that calculates max
points remaining based on future picks and the teams that are stil
alive. The formula is:
=(SUM(COUNTIF(AI4:AX4,B29:AG29))*20)+(SUM(COUNTIF(AZ4:BG4,B29:AG29))*40)+(SUM(COUNTIF(BI4:BL4,B29:AG29))*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 ge
multiplied by 20)
AZ4:BG4 are the picks for the 3rd round games (correct picks ge
multiplied by 40)
BI4:BL4 are the picks for the 4th round games (correct picks ge
multiplied by 80)
BN4:BO4 are the picks for the 5th round games (correct picks ge
multiplied by 160)
BQ4 is the pick for the championship game (correct pick gets multiplie
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. Th
ranges (AI4:AX4, AZ4:BG4, etc..) correctly became AI5:AX5 (6, 7,
etc...) when pasting below but the list they get compare to (B29-AG29
needs to stay constant but it did not with copy-paste (they becam
B30:AG30, B31:AG31 etc... ). I had to go back and change this rang
individually. Now the problem - when I sorted based on current tota
points the B29-AG29 range in the formula was given new and uniqu
values (B30:AG30, B42:AG42, B37:AG37 etc...). How can I lock B29:AG2
so this does not change after sorting.
Sorry for being so long but it was difficult to explain. Also this i
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