AnsweredAssumed Answered

Help with a CASE calculation, please?

Question asked by BonnieHammond on Jul 20, 2013
Latest reply on Jul 23, 2013 by philmodjunk

Title

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

               
                    

                         9-16

               
                    

                         17-24

               
                    

                         25 +

               
                    

                         1st Place

               
                    

                         10

               
                    

                         15

               
                    

                         20

               
                    

                         25

               
                    

                         2nd Place

               
                    

                         6

               
                    

                         10

               
                    

                         15

               
                    

                         20

               
                    

                         3rd Place

               
                    

                         4

               
                    

                         6

               
                    

                         10

               
                    

                         15

               
                    

                         4th Place

               
                    

                         2

               
                    

                         4

               
                    

                         6

               
                    

                         10

               
                    

                         5th 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")

Outcomes