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

 

[Nocean]

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.

Thanks for your response, this would be great if all entries were similar.

These are some working examples I need to achieve. The results are on a printed document which I am trying to convet into an excel sheet to automate the process for other users.

<table border="0" cellpadding="0" cellspacing="0" width="375"><col style="width: 56pt;" span="5" width="75"> <tbody><tr style="height: 24.95pt;" height="33"> <td class="xl67" style="height: 24.95pt; width: 56pt;" height="33" width="75">Datum Qty</td> <td class="xl67" style="border-left: medium none; width: 56pt;" width="75">Actual Qty</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Qty Lost</td> <td class="xl71" style="border-left: medium none; width: 56pt;" width="75">Qty New</td> <td class="xl67" style="border-left: medium none; width: 56pt;" width="75">Net Change</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl68" style="height: 24.95pt;" height="33">38</td> <td class="xl65">38</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl73" style="border-left: medium none;">+0</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt;" height="33">45</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">45</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">+0</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" height="33">35</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">35</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">30 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">30</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">+0</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" height="33">79</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">85</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">+6</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" height="33">20</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl69" style="border-left: medium none;">6 </td> <td class="xl66" style="border-left: medium none;">0</td> <td class="xl72">-6</td> </tr> <tr style="height: 24.95pt;" height="33"> <td class="xl65" style="height: 24.95pt; border-top: medium none;" height="33">20</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl69" style="border-left: medium none;">6 </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl73" style="border-left: medium none;">+0</td> </tr> </tbody></table>
Does the above help in understanding what I am trying to achive?

 

[Andrew Poulsom]

Those results look like:

=D2-C2

 

[Nocean]

Those results look like:

=D2-C2

No, this will not work either. All four column rows have to be evaulated simultaneously to achieve the correct - or + results using " IF " type senario.

I will have to review what I am trying to achieve and come back.

Many thanks for your assistance, it has given me something to think about i the meantime.

 

[jasonb75]

Maybe you should consider asking whoever supplied the data you already have.

The way your figures appear would suggest that parts of the New / Loss figures are based on the variance between Datum and Actual.

For example, if Actual is Greater than Datum, then the difference is added to New, or if less then the difference is added to Loss.

There could also be additional New included from other sources, without more information this is going to be close to impossible to find a consistant rule for checking all values.

 

[Nocean]

Maybe you should consider asking whoever supplied the data you already have.

The way your figures appear would suggest that parts of the New / Loss figures are based on the variance between Datum and Actual.

For example, if Actual is Greater than Datum, then the difference is added to New, or if less then the difference is added to Loss.

There could also be additional New included from other sources, without more information this is going to be close to impossible to find a consistant rule for checking all values.

Thanks for your response, I am in the process of reviewing the same. Appreciate all your assistance. Will come back when resolved.

Once again, thank you