Formula Help - ABS Value of Summary

[Johnny Thunder]

Hello All,

I am running into a weird situation that sounded easy at first but as I look into the results they are all over the place. Any help is appreciated.

My issue:

1. I have a Journal Entry tool that converts a range of values from a raw report from their original state to ABS Values.

2. I then have a dashboard Sheet that takes the Journal Entry total and provides it to the user, I then have another cell that shows the report Total.

3. The problem that I am having is that currently my Journal is 1,932,345 but the original report total is only 102,629. The reason for the discrepancy is that the Original report has negative values that are being converted to Positive numbers on the Journal Entry.

So to compensate for the variation in total between the Original Report total and what the Journal Entry shows I tried doing a SumProduct formula on the Original Report with an ABS added to hopefully get the same result but I am seeing that if a SUMPRODUCT is used the formula is aggregating a total first, then converting it to ABS as opposed to taking each line and converting it before summarizing.

Here is my formula: =SUMPRODUCT((CompCode=E16)*AmtData) - Result is 102,629
Here is my formula with ABS =SUMPRODUCT(ABS((CompCode=E16)*AmtData)) - Result 4,375,863

If I manually get all the rows from the raw report and convert each to ABS then sum - I get the Journal Entry Amount that I am after 1,932,345.

 

[Rijnsent]

Hi JT,
I tried a small sheet with your formulas and both of them seem to work fine (made a small sample that I could check manually). My hunch is that there is something about cells that aren't picked up as numbers with the sumproduct formula (them being text or so), but that's hard to check from a distance. Could you share some data for a more thorough test?
Thanks,
Koen

 

[Johnny Thunder]

Here is a link to my Google Drive with the Sample Data from my question: Sample File.xlsx

I made a separate sheet to show the formulas being used that are not getting the results needed. Also, showed that if you manually convert each row of data to ABS then I get the result that I am after.

Thanks to anyone that looks into this and finds a solution.

 

[Rijnsent]

Hi JT,
found your problem: if you look at e.g. CC 1255 with CE 30000, this is the selection:

Comp Code	Cost Center	Cost Element	Var.val.in rep.cur.	ABS2	POS_NEG
0012	1255​	30000​	-12455,52​	12455,52​	neg
0012	1255​	30000​	-22699,49​	22699,49​	neg
0012	1255​	30000​	12455,52​	12455,52​	pos
0012	1255​	30000​	24911,04​	24911,04​	pos

As you see, I added 2 columns to your data: 1) the absolute value and 2) whether that line is positive or negative.
If you put that in a pivot table:
Sum of negative values: (35.155)
Sum of positive values: 37.367
Sum all values: 2.212 ->ABS and SUM to get 1.932.345
Sum of absolute values: 72.522 -> sum this to get 4.375.863
That works fine, but than you basically want the 2.212 and the absolute values of those groupings per CC & CE to get your "absolute sum". That's not what that sumproduct can do: group, than abs, than sum. I'm wondering whether an array formula can achieve what you want, but that's quite a tough cookie.
Cheers,
Koen

 

[Johnny Thunder]

I appreciate you looking into this. If you can come up with anything I would appreciate it. I know when I made the sumproduct formula an array formula, I got the same result so not sure what type of an array can achieve the desired result.

 

[Rijnsent]

In 3 formulas:
=UNIQUE(B2:C1089) -> a excel 2019 formula which returns an array of your unique groups, about 180 rows. Next to that:
=ABS(SUMIFS(Reclass!$D$2:$D$1089,Reclass!$B$2:$B$1089,F13,Reclass!$C$2:$C$1089,G13)) (or use a SUMPRODUCT instead).
And sum those values for your check total.
Maybe someone can squeeze those formulas into 1, but that does work.
Cheers,
Koen