merlin_the_magician
Active Member
- Joined
- Jul 31, 2002
- Messages
- 480
A | B | C | D | E | F | G | H | I | J | |
1 | color | points | color | points | color | points | color | points | color | points |
2 | blue | 10 | green | 20 | blue | 10 | red | 30 | blue | 10 |
Points blue: |
in the simpe example sheet above, I have a table with values. I need to determine the count of each color. Eventually, this count is used in a function calculating weighted average, affecting the score points.
For example blue: i need the count number of the entry "blue" in row 2 (3). In a weighted average, points for "blue" should result in (10/3=3.33)
So......... i figured to make a named range "COLOR" (A2,C2,E2,G,I2) using formula COUNTIF(COLOR,A2) however, returns #VALUE
COUNTIF(A2:J2,A2) does the trick, but... only half of it. That only works outside the table. When i use that the calculate weighted averge directly into cell A2, COUNTIF(A2:J2,A2) ends up in a circular reference.
It apppears as if the named range should be a solid, cojoined range, rather than a collection of cells.
Does anybody have a keen idea on how to fix this?