problem in formula

[debeerj@hotmail]

I want to work out the difference between numbers and then between the results until there is only one number.

The formula is: =IF(A1>A2,A1-A2,A2-A1) I do not want to work with negatives

55
03
34
40
14


55
03 52
34 31 21
40 06 25 04
14 26 20 05 01

My problem is that I now want to RE-CALCULATE the last row starting from the second number from last to give me 14(the 1st number in the last row)
I should be able to do this because I have two givens.. 01(the last number) of the last row and 04 the last last number of the second last row. I should then get the same results, ie. 14 26 20 and 05!

55
03 52
34 31 21
40 06 25 04
00 00 00 00 01

the formula for the bottom row from the second last number is =IF(D4>E5,D4+E5,E5-D4)
The calculation starts now from right to left.

but I do not get all the numbers correct to get to the 1st number of the last row which is 14

I get
55
03 52
34 31 21
40 06 25 04
64 24 30 05 01

Where am I going wrong with the formula?

I have attatched the excel document.
Thanks in advance

[Bryan Hessey]

I want to work out the difference between numbers and then between the results until there is only one number.

The formula is: =IF(A1>A2,A1-A2,A2-A1) I do not want to work with negatives

55
03
34
40
14


55
03 52
34 31 21
40 06 25 04
14 26 20 05 01

My problem is that I now want to recount the last row starting from the second number from last to give me 14

55
03 52
34 31 21
40 06 25 04
00 00 00 00 01

the formula for the bottom row from the second last number is =IF(D4>E5,D4+E5,E5-D4)

but I do not get all the numbers correct to get to the last number which is 14

I get
55
03 52
34 31 21
40 06 25 04
64 24 30 05 01

Where am I going wrong with the formula?

I have attatched the excel document.
Thanks in advance

Hi,

If you want differences shoul A18 not be

=IF(A17>B18,A17-B18,B18-A17)

rather than + ?

hth
---

[debeerj@hotmail]

Hi,
No, because in the first instance the differences are calculated.
The last 2 rows become the focus in the 2nd instance, with the last number being 01( that has been calculated.)
I want to calculate from the second last number of the last row to the 1st number of the last row( from right to left)which should then work out to be 14, but it does not.

PLEASE LET ME KNOW IF I AM NOT CLEAR IN EXPLAINING MY OBJECTIVE

[Bryan Hessey]

Hi,
No, because in the first instance the differences are calculated.
The last 2 rows become the focus in the 2nd instance, with the last number being 01( that has been calculated.)
I want to calculate from the second last number of the last row to the 1st number of the last row( from right to left)which should then work out to be 14, but it does not.

Please Let Me Know If I Am Not Clear In Explaining My Objective

Hi,

Your objective is not possible.

From your original set 55, 3, 34, 40, 14 you selected the positive difference.

You now cannot determine (in reverse) which method was used to make that initial determination, see E18 and D17, then D18 and C17
You need 5 from the first pair, they need to add.
You need 20 from the second pair, they need to subtract.
Using only the 4 references above you cannot determine which is add and which is not, ie
D17 is < E18 needs to add
C17 is > D18 needs to subtract

There is no formula to correlate the pair.

That would give 20, then 6 would give 26 and 40 would give 14, but without a constant formula it's not going to happen.

Are you permitted to test the original pair to determine + or - ?

---

[debeerj@hotmail]

Hi,

I can do this anyway, If you have an idea on how to be able to determine when to subtract and when to add, I will greatly appreciate it. I even thought that the results higher up in the calculation might have an influence on whether I should add or subtract.

I just can not figure out how!
Johan

[Bryan Hessey]

Hi,

I can do this anyway, If you have an idea on how to be able to determine when to subtract and when to add, I will greatly appreciate it. I even thought that the results higher up in the calculation might have an influence on whether I should add or subtract.

I just can not figure out how!
Johan

Hi,

As I said, you cannot, your objective would need to retain a complete set of formulae to determine whether to add or subtract.

The 5 at D5 (D18) was made by comparing C4 & C5, now you only have one of those. You cannot compare C4 and determine how C5 was before.

hth
---

[daddylonglegs]

Hello Johan,

I agree with Bryan, you can't deduce the answer from the information given.

I know it doesn't help with your specific dilemma but rather than

=IF(A1>A2,A1-A2,A2-A1)

use

=ABS(A1-A2)

[Bryan Hessey]

Hello Johan,

I agree with Bryan, you can't deduce the answer from the information given.

I know it doesn't help with your specific dilemma but rather than

=IF(A1>A2,A1-A2,A2-A1)

use

=ABS(A1-A2)

Hi,

It would need a second table, where the numbers would be read from the first table and the act rather than the numbers be recorded on the second table.
The third table could then use the second table to determine what had happened.

Now, if a Formula could colour it's own cell (as well as do the number), and another formula detect that colour, well then you could (as it were) cut-out the middle-man.



----

[Bryan Hessey]

Hi,

I can do this anyway, If you have an idea on how to be able to determine when to subtract and when to add, I will greatly appreciate it. I even thought that the results higher up in the calculation might have an influence on whether I should add or subtract.

I just can not figure out how!
Johan

Hi,

solved, the same formula applies in A15, A16, B16, A17, B17, C17, A18, B18, C18, D18.

hth
---

[debeerj@hotmail]

Hi,

Thanks

Johan

[Bryan Hessey]

Hi,

Thanks

Johan

The only other method was to revert back to testing the original formula, anyway, thanks for the response.
---