Rank (What is driving $ change)

[linesy]

Good morning all,

I have financial results, some negative, some positive, thank I need to be able to identify the drivers of the period over period change. Is there something is can do with a formula, or does anyone have any suggestions?? Thank you much. Happy Friday !

Example when total negative:

Product A 7.4 Driver 5
Product B 37.6 Driver 4
Product C (50.4) Driver 2
Product D (105.4) Driver 1
Product E (2.6) Driver 3
Total Products (113.3)

Example when total is positive:

Product A 1.0 Driver 5
Product B 5.4 Driver 2
Product C 12.3 Driver 1
Product D 4.4 Driver 3
Product E 1.7 Driver 4
Total Products 24.8

 

[63falcondude]

I'm really not sure what you want. What would be the answer to both of your examples and why?

 

[Marcelo Branco]

It's not clear, at least for me, what is the logic behind the results you have showed. Could you explain why, in the first example (negative total), 37.6 is 4 and 7.4 is 5?

Do you want the rankings in ascending order when the total is negative? And in descending order when the total is positive (second example)?

Please, clarify

M.

 

[linesy]

Sorry for not being clear, the answer is the driver X to the right hand side.

We have to write commentary for our financials, which include over 200 products every quarter. Here is what we would've written for the first example above.

Total product sales decreased for the period by $113.3 million, primarily driven by a decrease in Product D of $105.4 million, Product C of $50.4 million, partially offset by increased sales of Product B, of $37.6 million.

In order to put this together, it is a shuffles of multiple report pages and extrapolation of data. I've got all the data into a spreadsheet, just trying to figure out a what of easily identifying what products etc are driving the change. Thanks

 

[linesy]

We list the drivers in ascending order if they are all positive or all negative. If there is a mix in the product results, we look at the total and if it is negative we list the negatives in ascending order and then we list the offset in descending order. If the total is positive, we would list the positives in descending order and then the negatives in ascending order. M, if you wouldn't mind, I also gave an example of why we use the data below. Thanks so much. S

 

[63falcondude]

Wouldn't Drivers 4 and 5 be switched in the negative example?

Excel 2010
	A	B	C
1	Product A	7.4	Driver 4
2	Product B	37.6	Driver 5
3	Product C	-50.4	Driver 2
4	Product D	-105.4	Driver 1
5	Product E	-2.6	Driver 3
6	Total Products	-113.4
Sheet4

Cell Formulas
Range	Formula
C1	="Driver "&IF($B$6<0,RANK(B1,$B$1:$B$5,1),RANK(B1,$B$1:$B$5,0))
B6	=SUM(B1:B5)

 

[Marcelo Branco]

See if this does what you need

All positives

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Value​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

1,0​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

5,4​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

12,3​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

4,4​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

1,7​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Total​

[/TD]
[TD]

24,8​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


All negatives

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Value​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

(1,0)​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

(20,1)​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

(3,4)​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

(10,9)​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

(2,0)​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Total​

[/TD]
[TD]

(37,4)​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Mix

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Value​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

7,4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

37,6​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

(50,4)​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

(105,4)​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

(2,6)​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Total​

[/TD]
[TD]

(113,4)​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=IF(OR(COUNTIF(B$2:B$6,">=0")=ROWS(B$2:B$6),COUNTIF(B$2:B$6,"<0")=ROWS(B$2:B$6)),RANK(B2,B$2:B$6,0),IF(SUM(B$2:B$6)<0,IF(B2>0,RANK(B2,B$2:B$6,0)+COUNTIF(B$2:B$6,"<0"),RANK(B2,B$2:B$6,1)),RANK(B2,B$2:B$6,0)))

Hope this helps

M.