Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting Occurences
I was given the array formula below to count the occurences of "12" &
"11.5" in a column of numbers. =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5 })) The number of teams competing can vary, the above example assumes 12 teams. If there is a dead heat, the points are shared so they get 11.5 each. I have a cell AN1 that now holds the number of teams competing, so I would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep getting an error. If I can do this, then instead of changing all my formulas, all I need to do is change the value in AN1. Hope that makes sense Pete |
#2
|
|||
|
|||
Try using 2 cells?
Put in AN1: 12, in AN2: =AN1-0.5 Then use, array-entered (press CTRL+SHIFT+ENTER): =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),AN1:AN2) ) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pete" wrote in message oups.com... I was given the array formula below to count the occurences of "12" & "11.5" in a column of numbers. =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5 })) The number of teams competing can vary, the above example assumes 12 teams. If there is a dead heat, the points are shared so they get 11.5 each. I have a cell AN1 that now holds the number of teams competing, so I would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep getting an error. If I can do this, then instead of changing all my formulas, all I need to do is change the value in AN1. Hope that makes sense Pete |
#3
|
|||
|
|||
Hi Pete
One way: =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5})) -- Best Regards Leo Heuser Followup to newsgroup only please. "Pete" skrev i en meddelelse oups.com... I was given the array formula below to count the occurences of "12" & "11.5" in a column of numbers. =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5 })) The number of teams competing can vary, the above example assumes 12 teams. If there is a dead heat, the points are shared so they get 11.5 each. I have a cell AN1 that now holds the number of teams competing, so I would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep getting an error. If I can do this, then instead of changing all my formulas, all I need to do is change the value in AN1. Hope that makes sense Pete |
#4
|
|||
|
|||
Hi Leo,
Nice to see you around. It's much neater, but perhaps there was a typo? Should it be: =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0;0.5})) (with the semicolon instead of the comma in " {0;0.5} ") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Leo Heuser" wrote in message ... Hi Pete One way: =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5})) -- Best Regards Leo Heuser Followup to newsgroup only please. |
#5
|
|||
|
|||
Hi Max
You are very kind :-) Yes, you are right. It should have been a semicolon. Thanks! Regards LeoH "Max" skrev i en meddelelse ... Hi Leo, Nice to see you around. It's much neater, but perhaps there was a typo? Should it be: =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0;0.5})) (with the semicolon instead of the comma in " {0;0.5} ") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Leo Heuser" wrote in message ... Hi Pete One way: =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5})) -- Best Regards Leo Heuser Followup to newsgroup only please. |
#6
|
|||
|
|||
It's good to see you around, Leo !
Cheers -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Thanks Leo works fine.
|
#8
|
|||
|
|||
You're welcome, Pete, and thanks for the feedback :-)
LeoH "Pete" skrev i en meddelelse ups.com... Thanks Leo works fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurences of a specific day between two dates | Excel Worksheet Functions | |||
Counting... | Excel Worksheet Functions | |||
Counting Number of Occurences | Excel Discussion (Misc queries) | |||
Back Dating (Counting Backwards) | Excel Discussion (Misc queries) | |||
Counting Rainfall Data | Excel Discussion (Misc queries) |