Excel2007- numeric difference accross four columns

[Nocean]

Hi,
I am attempting to find a "net" difference between four cells of numeric values accross four columns. I am able to achieve a partial answer but I am experiencing difficulties when one cell is changed. I have shown an excerpt of how I require the results to be below:

<table border="0" cellpadding="0" cellspacing="0" width="481"><col style="width: 80pt;" width="106"> <col style="width: 56pt;" span="5" width="75"> <tbody><tr style="height: 24.95pt;" height="33"> <td class="xl66" style="height: 24.95pt; width: 80pt;" align="center" height="33" width="106"> </td> <td class="xl67" style="border-left: medium none; width: 56pt;" align="center" width="75">Datum Qty</td> <td class="xl67" style="border-left: medium none; width: 56pt;" align="center" width="75">Actual Qty</td> <td class="xl71" style="border-left: medium none; width: 56pt;" align="center" width="75">Qty Lost</td> <td class="xl71" style="border-left: medium none; width: 56pt;" align="center" width="75">Qty New</td> <td class="xl67" style="border-left: medium none; width: 56pt;" align="center" width="75">Net Change</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl63" style="height: 24.95pt;" align="center" height="33">A</td> <td class="xl64" style="border-left: medium none;" align="center">20</td> <td class="xl64" style="border-left: medium none;" align="center">14</td> <td class="xl70" style="border-left: medium none;" align="center">6 </td> <td class="xl65" style="border-left: medium none;" align="center">0</td> <td class="xl69" style="border-left: medium none;" align="center">-6 </td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl63" style="height: 24.95pt; border-top: medium none;" align="center" height="33">A</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">14</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">20</td> <td class="xl70" style="border-left: medium none;" align="center">6 </td> <td class="xl65" style="border-left: medium none;" align="center">0</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="center">+ 6 </td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl63" style="height: 24.95pt; border-top: medium none;" align="center" height="33">AA</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">20</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">14</td> <td class="xl70" style="border-left: medium none;" align="center">0 </td> <td class="xl65" style="border-left: medium none;" align="center">6</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="center">#VALUE!</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl63" style="height: 24.95pt; border-top: medium none;" align="center" height="33">AA</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">14</td> <td class="xl64" style="border-top: medium none; border-left: medium none;" align="center">20</td> <td class="xl70" style="border-left: medium none;" align="center">6 </td> <td class="xl65" style="border-left: medium none;" align="center">0</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="center">#VALUE!</td> </tr> </tbody></table>
The first two (Rows A) shows correctly what I am trying to achieve.

The third and fourth (Rows AA) shows the result after changing the inputs with the various formulas tried below.

The first formula I used worked for the columns Datum, Actual and Lost IF(SUM(B3-C3=D3),SUM(C3+E3)-B3,"ERROR"), but of course will not work when Lost and New column entries are changed, so I compiled the following variations with no success:
<table border="0" cellpadding="0" cellspacing="0" height="116" width="334"><col style="width: 56pt;" width="75"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 56pt;" height="20" width="75">IF(SUM(B3-C3=D3),SUM(C3+E3)-B3,"ERROR")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
IF(SUM(B4+E4=C4),SUM(C4+G4)-B4,"ERROR")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
IF(SUM(B8-8=E8),SUM(C8+G8)-B8,"ERROR")*OR(IF(SUM(C8-B8=G8),OR(IF(SUM(C8-G8=B8),SUM(C8+G8)-B8,"ERROR"))))</td> </tr> </tbody></table>
I can achieve more simplified results to identify the difference, but cannot seem to include changes between Lost and New columns.

This sheet will have around 500 rows upon completion, with various numeric values in each of the four cells (left to right).

I am unfamiliar with VBA, and would prefer not to use a macro.

I look forward to any assistance to resolve my problem, which I suspect I am trying to over complicate as usual!

Thanks in advance

 

[Andrew Poulsom]

Why are you using the SUM function in your formulas? You aren't summing anything.

Please try to explain in words what you want your formula to do.

 

[Nocean]

Thanks for coming back to me.

All I wish to calculate is the net change column (positive or negative outcome) based upon the figures inserted within the previous 3 column cells for each row so I may track if the result is positive, negative or no change numerically.

E.g. Col A Row1, Col B Row1, Col C Row1, Col D Row1 is calculated to provide the resultant value either positive, negative or no change in Col E Row 1.

Eg. Using my example, I start with a datum figure of 20 items, at time of review, the current datum has fallen to 14, due to 6 items being lost, with no new items being added, therefore the result is -6.

Equally, if I start with a datum figure of 14 items, at time of review, the current datum has risen to 14, due to 6 items being added, with no lost items being recored, therefore the result is +6.

Additionally, if I start with a datum figure of 79 items, at time of review, the current datum has risen to 85, due to 6 items being added, with no lost items being recored, therefore the result is +6.

I hope this makes more sense ??!!??

Thank you

 

[Andrew Poulsom]

I don't understand your second example:

Equally, if I start with a datum figure of 14 items, at time of review, the current datum has risen to 14, due to 6 items being added, with no lost items being recored, therefore the result is +6.

If you started with 14 and still have 14 how can 6 have been added? Also in the second line of sample data that you posted, how can 6 be lost when the number has increased from 14 to 20?

 

[Nocean]

I don't understand your second example:



If you started with 14 and still have 14 how can 6 have been added? Also in the second line of sample data that you posted, how can 6 be lost when the number has increased from 14 to 20?

Sorry, my typo, it should have read started with 14, risen to 20, due to 6 being added.....

I am off to a meeting now, will return later to continue.

Thanks

 

[Nocean]

Sorry, my typo, it should have read started with 14, risen to 20, due to 6 being added.....

I am off to a meeting now, will return later to continue.

Thanks

Hi I'm back, I am not really sure how to further describe what I am trying to achieve.

I am looking to view the results for each row of data, based upon the inputs of the four previous cells, in the same row.

An altrnative way to understand this might be:

I start with 20 items my datum figure, when reviewed during say a meeting, the figure now stands at 14, as 6 items have been used, I have no new items to add, therefore my current status is -6 (a loss), which I can achieve with my old formula. However, if, using the above example, I recieve 6 new items, the result would be zero (no change).
Equally, if in another row, I have 79 items as my datum, but during the meeting the figure stands as 85, I have gained 6 items, which will be listed in the appropriate column (new).
I cannot achieve a formula to look at the data, either summing, comparing or other and provide the result, which in some circumstances would be negative, or positive or no change (- or + or 0).

I require the sheet to calculate automatically upon the user entering the data in the first four columns.

I am sure there is a way to deal with this which I have not investigated or perhaps I am making my life too difficult.

 

[Andrew Poulsom]

Is it?

=C2-(B2-D2+E2)

 

[Nocean]

Is it?

=C2-(B2-D2+E2)

Thanks but no, this returns an incorrect figures. The results are shown below using your suggestion in the Net Change. I will have a play around.

<table border="0" cellpadding="0" cellspacing="0" height="120" width="367"><col style="width: 56pt;" span="6" width="75"> <tbody><tr style="height: 24.95pt;" height="33"> <td class="xl67" style="height: 24.95pt; width: 56pt;" align="center" height="33" width="75">Datum Qty</td> <td class="xl67" style="border-left: medium none; width: 56pt;" align="center" width="75">Actual Qty</td> <td class="xl72" style="border-left: medium none; width: 56pt;" align="center" width="75">Qty Lost</td> <td class="xl72" style="border-left: medium none; width: 56pt;" align="center" width="75">Qty New</td> <td class="xl67" style="border-left: medium none; width: 56pt;" align="center" width="75">Net Change</td> <td class="xl68" style="width: 56pt;" align="center" width="75">Should be</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt;" align="center" height="33">20</td> <td class="xl65" style="border-left: medium none;" align="center">14</td> <td class="xl71" style="border-left: medium none;" align="center">6 </td> <td class="xl66" style="border-left: medium none;" align="center">0</td> <td class="xl70" style="border-left: medium none;" align="center">+ 0 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">- 6</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" align="center" height="33">20</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">14</td> <td class="xl71" style="border-left: medium none;" align="center">0 </td> <td class="xl66" style="border-left: medium none;" align="center">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="center">-12 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">+ 0</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" align="center" height="33">14</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">20</td> <td class="xl71" style="border-left: medium none;" align="center">6 </td> <td class="xl66" style="border-left: medium none;" align="center">0</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="center">+ 12 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">+ 0</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" align="center" height="33">14</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">20</td> <td class="xl71" style="border-left: medium none;" align="center">0 </td> <td class="xl66" style="border-left: medium none;" align="center">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="center">+ 0 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">+ 6</td> </tr> </tbody></table>
Any other suggestions would be appreciated.

Thank you

 

[Andrew Poulsom]

Please explain the arithmetic behind your required results.

 

[jasonb75]

Should it not be (Actual - Datum) + new ?

Where should the lost column come into the equation? That appears to be what we're trying to calculate.