Hi All,
I'm struggling to get my head around the following and I hope someone can help.
Here's an example of what's happening:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Forecast[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Diff.[/TD]
[TD="class: xl63, width: 64"]ABS Diff.[/TD]
[/TR]
[TR]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]95[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]135[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]110[/TD]
[TD="class: xl63, align: right"]-10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]75[/TD]
[TD="class: xl63, align: right"]-25[/TD]
[TD="class: xl63, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl63"]Total[/TD]
[TD="class: xl63, align: right"]450[/TD]
[TD="class: xl63, align: right"]425[/TD]
[TD="class: xl64, align: right"]25[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]
I have created the following SUMX formula to calculate the ABS Diff., iterating through each row at a time:
The row values returned are correct (in as much as they're the absolute difference between Forecast and Sales), but the total isn't correct and is a sum of the positive and negative values, i.e. it hasn't summed up the absolute values.
What have I done wrong?
Cheers,
Matty
I'm struggling to get my head around the following and I hope someone can help.
Here's an example of what's happening:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Forecast[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Diff.[/TD]
[TD="class: xl63, width: 64"]ABS Diff.[/TD]
[/TR]
[TR]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]95[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]135[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]110[/TD]
[TD="class: xl63, align: right"]-10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]75[/TD]
[TD="class: xl63, align: right"]-25[/TD]
[TD="class: xl63, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl63"]Total[/TD]
[TD="class: xl63, align: right"]450[/TD]
[TD="class: xl63, align: right"]425[/TD]
[TD="class: xl64, align: right"]25[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]
I have created the following SUMX formula to calculate the ABS Diff., iterating through each row at a time:
Code:
ABS Diff. = ABS(SUMX(Table,Table[Forecast]-Table[Sales]))
The row values returned are correct (in as much as they're the absolute difference between Forecast and Sales), but the total isn't correct and is a sum of the positive and negative values, i.e. it hasn't summed up the absolute values.
What have I done wrong?
Cheers,
Matty