Help with a CASE calculation, please?
BonnieHammond Jul 20, 2013 2:35 PMTitle
Help with a CASE calculation, please?
Post
I am trying to write a formula that will calculate points for a horse show. Points are based on the placing in the class and the number of exhibitors in the class. The formula I've written works perfectly for first place (ie. the point value goes up as the number of exhibitors increases, just like I want it to) but it doesn't quite work for second through fifth place (ie., it returns the number of points for up to 8 exhibitors no matter how many exhibitors are in the class)
I can't for the life of me figure out what is wrong. Can anyone help, please?
Point System Used to Calculate High Points

Number of exhibitors in the class  

Up to 8 
916 
1724 
25 + 
1^{st} Place 
10 
15 
20 
25 
2^{nd} Place 
6 
10 
15 
20 
3^{rd} Place 
4 
6 
10 
15 
4^{th} Place 
2 
4 
6 
10 
5^{th} Place 
1 
2 
4 
6 
Here is the formula I am using:
Case(Placing = 1 and Entrants <=8; "10"; Placing = 2 and Entrants <=8; "6"; Placing = 3 and Entrants <=8; "4"; Placing = 4 and Entrants <=8; "2"; Placing = 5 and Entrants <=8; "1"; Placing = 1 and Entrants >=9 and Entrants <=16; "15"; Placing = 2 and Entrants >=9 and Entrants <=16; "10"; Placing = 3 and Entrants >=9 and Entrants <=16; "6"; Placing = 4 and Entrants >=9 and Entrants <=16; "4"; Placing = 5 and Entrants >=9 and Entrants <=16; "2"; Placing = 1 and Entrants >=17 and Entrants <=24; "20"; Placing = 2 and Entrants >=17 and Entrants <=24; "15"; Placing = 3 and Entrants >=17 and Entrants <=24; "10"; Placing = 4 and Entrants >=17 and Entrants <=24; "6"; Placing = 5 and Entrants >=17 and Entrants <=24; "4"; Placing = 1 and Entrants >=25; "25"; Placing = 2 and Entrants >=25; "20"; Placing = 3 and Entrants >=25; "15"; Placing = 4 and Entrants >=25; "10"; Placing = 5 and Entrants >=25; "6")
Here's the same formula broken into lines so it's easier to read:
Case(
Placing = 1 and Entrants <=8; "10";
Placing = 2 and Entrants <=8; "6";
Placing = 3 and Entrants <=8; "4";
Placing = 4 and Entrants <=8; "2";
Placing = 5 and Entrants <=8; "1";
Placing = 1 and Entrants >=9 and Entrants <=16; "15";
Placing = 2 and Entrants >=9 and Entrants <=16; "10";
Placing = 3 and Entrants >=9 and Entrants <=16; "6";
Placing = 4 and Entrants >=9 and Entrants <=16; "4";
Placing = 5 and Entrants >=9 and Entrants <=16; "2";
Placing = 1 and Entrants >=17 and Entrants <=24; "20";
Placing = 2 and Entrants >=17 and Entrants <=24; "15";
Placing = 3 and Entrants >=17 and Entrants <=24; "10";
Placing = 4 and Entrants >=17 and Entrants <=24; "6";
Placing = 5 and Entrants >=17 and Entrants <=24; "4";
Placing = 1 and Entrants >=25; "25";
Placing = 2 and Entrants >=25; "20";
Placing = 3 and Entrants >=25; "15";
Placing = 4 and Entrants >=25; "10";
Placing = 5 and Entrants >=25; "6")