3 Replies Latest reply on Nov 26, 2013 6:46 AM by Matt Lutton

    Calculation Issue - Median

    Adrien Guguin



      I am looking to perform some analysis to find out which businesses generate high/low commission. I have calculated the Median for my commissions. Now I am trying to  see how many commissions are above Median +20% and under Median -20%.


      I have been trying to get this by calculating the below:


      IF [Commission amount] > ([Median]*1.20) THEN 1 END


      I was hoping to be able to Count this calculation and that would give me what I need.


      Can anybody assist me with this issue?


      thanks in advance,



        • 1. Re: Calculation Issue - Median
          Matt Lutton

          I assume you are getting an error with this calc?  You can try (untested):

          IF sum([Commission amount]) > (sum([Median])*1.20) THEN 1 END


          If this doesn't help, can you post a packaged workbook that demonstrates the problem and let us know what error you are receiving?

          • 2. Re: Calculation Issue - Median
            Adrien Guguin

            Hi Mattew,


            I realized i never got back on you regarding my question so apologies for that. Appreciate your time to review and propose way of resolving it. The formula that you proposed did not work, but in the meantime i was asked to display average and stdev. I added a trend line on the graph and added the standard deviation of -1;1 based on the average. That solved the issue for this piece of work.




            • 3. Re: Calculation Issue - Median
              Matt Lutton

              No problem; please mark your question answered (you don't have to mark my reply as the correct answer, you can mark it as "assumed answered") so other helpers know this issue is resolved. Cheers!