Running % of absolute difference

sm_Napa

New Member
Joined
Nov 7, 2016
Messages
3
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).
 
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.
 
Upvote 0
Gerald is right, your request is a bit vague. But here's my first stab at it, let me know if it's close:

ABCDE
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.
 
Last edited:
Upvote 0
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
ABCDEFG
ItemActualBudgetDifferenceAbs DiffRankRunning %
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
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0
@Eric W:

Just to mention: those minus signs are actually zeros under Accounting format.
So, your nice formula would work without the IFERROR's.
 
Upvote 0
@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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top