Educational Brain Teaser : Colorful Conditional Format
Shinichiro Murakami Feb 20, 2018 7:06 PMMy 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 size  Shop  Sales  Profit 
Big  Shop A  125,000  21,054 
Medium  Shop B  98,200  20,031 
Small  Shop C  48,500  12,498 
Big  Shop D  154,200  24,500 
Medium  Shop E  126,510  22,004 
Small  Shop F  35,621  10,400 
Big  Shop G  210,000  24,500 
Medium  Shop H  106,540  18,540 
Small  Shop 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 missset 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

EBT_20180130_SM.xlsx 10.4 KB