Hi All,
I am trying to design a formula to see which group of sales has contributed the most to a bench mark (Say as the below and aim is to see which row/material is most impact deviation from the benchmark one in terms of volume effect or % deviation effect) I try to fiter the biggest result from column E but did not achieve what i wish. Does anyone has got any better design? Just want to see which material play the key role in terms of margin / sales % for entire average especially in contrast to a benchmark margin/sales %? thanks a lot!!! Cheers, Peter
I am trying to design a formula to see which group of sales has contributed the most to a bench mark (Say as the below and aim is to see which row/material is most impact deviation from the benchmark one in terms of volume effect or % deviation effect) I try to fiter the biggest result from column E but did not achieve what i wish. Does anyone has got any better design? Just want to see which material play the key role in terms of margin / sales % for entire average especially in contrast to a benchmark margin/sales %? thanks a lot!!! Cheers, Peter
A | B | C | D | E | |
1 | Sales Material 1 | Margin | Margin/Sales % | A1/A7 | (C1-C9)*D1 |
2 | Sales Material 2 | Margin | Margin/Sales % | A2/A7 | (C2-C9)*D2 |
3 | Sales Material 3 | Margin | Margin/Sales % | A3/A7 | (C3-C9)*D3 |
4 | Sales Material 4 | Margin | Margin/Sales % | A4/A7 | (C4-C9)*D4 |
5 | Sales Material 5 | Margin | Margin/Sales % | A5/A7 | (C5-C9)*D5 |
6 | |||||
7 | Total Sales | Total Margin | Total Margin / Total Sales | ||
9 | Benchmark % |