12 Replies Latest reply on Feb 7, 2018 5:08 PM by Jonathan Drummey

    Educational Brain Teaser : Colorful Conditional Format

    Shinichiro Murakami

      My first Post for Educational Brain Teaser.

       

      The answer thread is already here:

      Educational Brain Teaser ==> Answer : Colorful Conditional Format

       

      Little bit complicated conditional format request.

      This is based on yesterday's Q&A, then some people already know the answer : )

       

      You could duplicate the data with union, but expect to solve without Union, if possible.

       

      [Adding Comment on 2/1]

      Actually there are various approaches, and I'm sorry that I did not explain condition clearly.   And if possible, without Modifying Data source such as Pivot, Join, Union.....

      Data set. (Excel Attached)

       

      Shop sizeShopSalesProfit
      BigShop A         125,000         21,054
      MediumShop B           98,200         20,031
      SmallShop C           48,500         12,498
      BigShop D         154,200         24,500
      MediumShop E         126,510         22,004
      SmallShop F           35,621         10,400
      BigShop G         210,000         24,500
      MediumShop H         106,540         18,540
      SmallShop J             9,800           2,540

       

       

      Target View

       

      [comment added on 01/31]]

      Below two tables are independent, not on the single worksheet at the same time.

      I was imaging the hierarchy.

      (And I am so sorry, I miss-set color at original post...  )

       

       

       

      Color Criteria

       

      [Sales]

      if min([Shop size])="Big" then

          if avg([Sales])>250000 then "Green"

          elseif avg([Sales])>200000 then "Yellow"

          else "Red" end

       

      elseif min([Shop size])="Medium" then

          if avg([Sales])>150000 then "Green"

          elseif avg([Sales])>100000 then "Yellow"

          else "Red" end

       

      elseif min([Shop size])="Small" then

          if avg([Sales])>100000 then "Green"

          elseif avg([Sales])>500000 then "Yellow"

          else "Red" end

      end

       

      [Profit]

      if min([Shop size])="Big" then

          if avg([Profit])>25000 then "Green"

          elseif avg([Profit])>20000 then "Yellow"

          else "Red" end

       

      elseif min([Shop size])="Medium" then

          if avg([Profit])>20000 then "Green"

          elseif avg([Profit])>15000 then "Yellow"

          else "Red" end

       

      elseif min([Shop size])="Small" then

          if avg([Profit])>15000 then "Green"

          elseif avg([Profit])>10000 then "Yellow"

          else "Red" end

      end

       

      [Ratio]

      if min([Shop size])="Big" then

          if sum([Profit])/sum([Sales])>0.15 then "Green"

          elseif sum([Profit])/sum([Sales])>0.1 then "Yellow"

          else "Red" end

       

       

      elseif min([Shop size])="Medium" then

          if sum([Profit])/sum([Sales])>0.2 then "Green"

          elseif sum([Profit])/sum([Sales])>0.15 then "Yellow"

          else "Red" end

       

       

      elseif min([Shop size])="Small" then

          if sum([Profit])/sum([Sales])>0.25 then "Green"

          elseif sum([Profit])/sum([Sales])>0.2 then "Yellow"

          else "Red" end

       

      end

       

      Thanks,

      Shin