8 Replies Latest reply on Jan 8, 2013 4:42 PM by RayGerman

    Newbie - Lost in Value List and Calculations

    RayGerman

      Title

      Newbie - Lost in Value List and Calculations

      Post

            

           Hello,

            

           I am just getting my feet wet with FileMaker, so please bear with me. I am probably using the incorrect mechanism to perform this action, but with my limited knowledge of FileMaker... it is all I can come up with at the moment.

            

           I am building a Form to calculate potential bonuses for employees and am getting stummped on how to set up the calculation

            

           The first field (Maximum Bonus Potential) will hold a dollar value. 

            

           The second field (Company Performance Weight) which will hold a number which I would like to multiply against the first field as a percentage.

            

           The third field (Company Performance Score) will have a drop down value list, of the numbers 1, 2, 3, 4 and 5.

           I would like to convert the numbers in this fields for my calculation, so the number "1" actually equals "20%" The number "2" equals "40%" the number "3" = 60%" the number "4" = "80%" the number "5" equals "100%"

            

            

           For example if 

            

           Maximum Bonus Potential  675.00

           (x times)

           Company Performance Weight  50% 

           (x times)

           Company Performance Score  "Number 4 selected from value list"  = 80%

            

           I would like the calculation to be 675 x 50% x 80% = 270.00

            

            

           I cobbled this calculation together which appears horrible wrong.

            

           Maximum Quantitative Bonus Potential * Company Performance Weight & "%" * Case (Company Performance Score = 1 ; "20%" ; 2 ; "40%" ; 3; "60%" ; 4; "80%" ; 5; "100%" ; )

            

      If anyone has any input or suggestions... I would be very grateful.

            

           Thanks,

           Ray

            

            

        • 1. Re: Newbie - Lost in Value List and Calculations
          Sorbsbuster

               Why not just have the values 20, 40, 60, 80, and 100 in the value list, and make the calculation:

               Maximum Quantitative Bonus Potential * Company Performance Weight * Company Performance Score / 100

          • 2. Re: Newbie - Lost in Value List and Calculations
            philmodjunk

                 You can also use:

                 Maximum Bonus Potential  * Company Performance Weight/100 * Company Performance Score/5

                 1/5 = 0.2 = 20%

                 2/5 = 0.4 = 40%

                 and so forth.

                 Whichever approach you use, the key detail is that you have to use the decimal equivalent of your percent values so if you are going to multiply by 50%, you have to either use 0.5 or 50/100 in your calculation.

            • 3. Re: Newbie - Lost in Value List and Calculations
              RayGerman

                   The CFO is set on having the values appear as a drop down rating system. Each rating caries a hidden percentage. I guess the logic is to keep the bonus numbers out of view so Managers will rate the employee on performance as to avoid them from just giving them percentages. Smoke and mirrors... I don't know. I just want to give the person with "C" in their job title what they asked for so I can get 5 = 100% :)

                   1= poor = 20%

                   2 = somewhat = 40%

                   3 = for the most part = 60%

                   4 = almost always = 80%

                   5 = always = 100%

                    

                    

              • 4. Re: Newbie - Lost in Value List and Calculations
                philmodjunk

                     Please see my post that preceded yours by about 4 minutes...

                • 5. Re: Newbie - Lost in Value List and Calculations
                  RayGerman

                       Humm... I am not sure that will work since the Weight is input by the Acounting Department. The weight percentage will change regularly, thus basing the last part of the calcualtion off of the weight value may not work in my situation.

                       I don't know if I am being a hard head (could be) but it seems if I had a way to translate the numbers 1,2,3,4,5 to 20,40,60,80,100 in the calculation it would be smooth sailing. The Case function seemed like it would work... but I either don't understand it correctly or I am missing something in the syntax.

                       I do like the "Company Performance Weight/100" trick though... it seems like a good way to hide the Percentage decimal placement issue from the end user.

                       Thanks!

                        

                  • 6. Re: Newbie - Lost in Value List and Calculations
                    RayGerman

                         I actually was able to get the Case function to work partially but it is only pulling the 1=20% and 2=40%      3,4,5 do not appear to be working.

                         Case (Company Performance Score = 1 ; "20%" ; 2 ; "40%" ; 3 ; "60%" ; 4 ; "80%" ; 5 ; "100%" ; )

                    • 7. Re: Newbie - Lost in Value List and Calculations
                      philmodjunk

                           The expression that I posted should work for you. You cannot use percentages in a calculation. You must use the decimal equivalents. The fact that a value in a field is constantly being changed will not keep a calculation field from updating and calculating an updated value with each change.

                           Maximum Bonus Potential  * Company Performance Weight/100 * Company Performance Score/5

                           Should work for you as it converts the numbers 1 through 5 into the decimal equivalents of 20% through 100%.

                      • 8. Re: Newbie - Lost in Value List and Calculations
                        RayGerman

                             Hi Phil,

                             I needed to walk away from the problem for a minute. I sat down, and ran your solution and it worked!!!!

                             Very nice and elegant!

                             Thank you so much!!!

                             Ray