Running % of absolute difference

[sm_Napa]

I have a table of data with value 3 columns: two columns have values, the third column has the difference bteween these values, can be a negative or positive number. Without sorting the data, I want to calculate the running % of total of the difference column ignoring the sign (ie treating the difference as absolute). The purpose is this: the differences are 'variances' between actual and budget. I want to pick those items with highest absolute differences that account for at least 80% of all differences. I can do this by adding a column to convert the differences to absolute, add another column with Rank and a third column that has the running total based on the preceding two columns. But I want to do all the culations in a single column, without having to add 2 new columns (for absolute difference and rank).

 

[Gerald Higgins]

Hi, welcome to the board.

Your request may well be doable but it's difficult to be certain because your request is not as clear as it could be.
Could you please post back a SMALL sample of your data, showing exactly what you want to do, and what the results should be ?
If you don't want to post real data, make some up.

 

[Eric W]

Gerald is right, your request is a bit vague. But here's my first stab at it, let me know if it's close:

	A	B	C	D	E
X
X
X
X
X
X

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]73%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]-7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]44[/TD]
[TD="bgcolor: #00B050, align: right"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]-1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]-5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="bgcolor: #00B050, align: right"]-10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #00B050, align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]-1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #00B050, align: right"]30[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]-5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]-7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]210[/TD]
[TD="bgcolor: #00B050, align: right"]-20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]170[/TD]
[TD="bgcolor: #00B050, align: right"]30[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

D1: =IF(SUM(IF(ABS($C$1:$C$20)>=ABS(C1),ABS($C$1:$C$20)))>SUM(ABS($C$1:$C$20))*80%,"","X")

This is an array formula, confirm with Control+Shift+Enter. Then copy down the column. The values that have an X beside them are part of the top 80% variance. And as you can see by the green coloring, you can use this formula in Conditional Formatting.

E1: =SUM(IF(D1:D20="X",ABS(C1:C20)))/SUM(ABS(C1:C20))
with Control+Shift+Enter.

The values marked with an X probably won't hit 80% exactly, so I created this formula to see how close to 80% we got. The next lower value to be included would be ABS(-7), twice, which would raise the percentage to 82%.

Let us know if this is close at all.

 

[sm_Napa]

My apologies, I'll try again with same data. So I want to generate the output in Column G without having to create columns E and F

Excel 2013 32 bit

	A	B	C	D	E	F	G
Item	Actual	Budget	Difference	Abs Diff	Rank	Running %
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
TOTAL

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 7,310 [/TD]
[TD="align: right"] 9,700 [/TD]
[TD="align: right"] (2,390)[/TD]
[TD="align: right"] 2,390 [/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]98.9%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] - [/TD]
[TD="align: right"] 1,100 [/TD]
[TD="align: right"] (1,100)[/TD]
[TD="align: right"] 1,100 [/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]99.7%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 16,104 [/TD]
[TD="align: right"] 16,362 [/TD]
[TD="align: right"] (258)[/TD]
[TD="align: right"] 258 [/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]99.9%[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 32,209 [/TD]
[TD="align: right"] 21,600 [/TD]
[TD="align: right"] 10,609 [/TD]
[TD="align: right"] 10,609 [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]64.1%[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] 47,553 [/TD]
[TD="align: right"] 64,800 [/TD]
[TD="align: right"] (17,247)[/TD]
[TD="align: right"] 17,247 [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]48.2%[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 2,655 [/TD]
[TD="align: right"] 5,400 [/TD]
[TD="align: right"] (2,745)[/TD]
[TD="align: right"] 2,745 [/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]91.1%[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 16,105 [/TD]
[TD="align: right"] 21,600 [/TD]
[TD="align: right"] (5,495)[/TD]
[TD="align: right"] 5,495 [/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]84.2%[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] 18,948 [/TD]
[TD="align: right"] 10,800 [/TD]
[TD="align: right"] 8,148 [/TD]
[TD="align: right"] 8,148 [/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]70.2%[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"] 26,941 [/TD]
[TD="align: right"] 16,200 [/TD]
[TD="align: right"] 10,741 [/TD]
[TD="align: right"] 10,741 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]56.2%[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"] 378,182 [/TD]
[TD="align: right"] 351,000 [/TD]
[TD="align: right"] 27,182 [/TD]
[TD="align: right"] 27,182 [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20.3%[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"] 35,024 [/TD]
[TD="align: right"] 32,400 [/TD]
[TD="align: right"] 2,624 [/TD]
[TD="align: right"] 2,624 [/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]97.1%[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"] 106,419 [/TD]
[TD="align: right"] 86,400 [/TD]
[TD="align: right"] 20,019 [/TD]
[TD="align: right"] 20,019 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]35.3%[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"] - [/TD]
[TD="align: right"] 2,700 [/TD]
[TD="align: right"] (2,700)[/TD]
[TD="align: right"] 2,700 [/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]95.2%[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"] 198,802 [/TD]
[TD="align: right"] 202,500 [/TD]
[TD="align: right"] (3,698)[/TD]
[TD="align: right"] 3,698 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]87.0%[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"] 5,299 [/TD]
[TD="align: right"] 5,400 [/TD]
[TD="align: right"] (101)[/TD]
[TD="align: right"] 101 [/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"] 34,767 [/TD]
[TD="align: right"] 27,000 [/TD]
[TD="align: right"] 7,767 [/TD]
[TD="align: right"] 7,767 [/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]76.0%[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"] 89,122 [/TD]
[TD="align: right"] 86,400 [/TD]
[TD="align: right"] 2,722 [/TD]
[TD="align: right"] 2,722 [/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]93.1%[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"] 5,272 [/TD]
[TD="align: right"] 10,800 [/TD]
[TD="align: right"] (5,528)[/TD]
[TD="align: right"] 5,528 [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]80.1%[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"] 2,651 [/TD]
[TD="align: right"] 5,400 [/TD]
[TD="align: right"] (2,749)[/TD]
[TD="align: right"] 2,749 [/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]89.1%[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"] 32 [/TD]
[TD="align: right"] 35 [/TD]
[TD="align: right"] (3)[/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"] 222 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 7 [/TD]
[TD="align: right"] 7 [/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"] 1,023,617 [/TD]
[TD="align: right"] 977,812 [/TD]
[TD="align: right"] 45,805 [/TD]
[TD="align: right"] 133,833 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[sm_Napa]

Eric W, that formula acheives what I want (I saw your reply after I posted my data). Thank you! Still need to decipher the formula logic but the output is correct!

 

[Eric W]

I wasn't too far off. Put this formula in G4 and copy down:

=SUM(IF(IFERROR(ABS($D$4:$D$25),0)>=IFERROR(ABS(D4),0),IFERROR(ABS($D$4:$D$25),0)))/SUM(IFERROR(ABS($D$4:$D$25),0))

confirmed with Control+Shift+Enter.


I just saw your latest reply. Let me know if you need help figuring out how it works.

 

[Tetra201]

@Eric W:

Just to mention: those minus signs are actually zeros under Accounting format.
So, your nice formula would work without the IFERROR's.

 

[Eric W]

@Eric W:

Just to mention: those minus signs are actually zeros under Accounting format.
So, your nice formula would work without the IFERROR's.

Good to know! When I did the copy/paste, they showed up as a dash, which caused errors without the IFERROR. The formula would be

=SUM(IF(ABS($D$4:$D$25)>=ABS(D4),ABS($D$4:$D$25)))/SUM(ABS($D$4:$D$25))

without them. Thanks for the tip.