help with my if statement

[James]

I am trying to compare some numbers from the original entry that I have.
Below is a small subset of the data that I have:
(H) (I) (J)
42007000400000 25
42007000400001 31 6.00
42007008620000 29
42007008620001 22 -7.00
42007008760000 25
42007008760001 14 -11.00
42007008760002 17 -8.00
42007307120001 23
42007307120003 12
42007307310000 34
42007307310001 12 -22.00

Here is what my if statement looks like:
=IF(AND(H4-H3=1,H4-H2=2),I4-I1,IF(H4-H3=1,I4-I3,)) I know this formula
doesn't work for the first two rows of data but it works through the rest.

It is basically subtracting column I numbers from each other when column H
is =1. This works great when the numbers in column H are in numerical order
for that group, however when there not my formula does not work for it. The
example above would be 42007307120001 and 42007307120003, when subtracting
these numbers it is equal to 2, and my formula does not catch that.

I need help with my formula to take these instances in consideration. Thank
you for the help.

 

[Shane Devenshire]

Hi,

You show us a formula and say it doesn't work. But if you are going to put
that formula in say K1 and copy it down, it is not clear what you want it to
do. Why don't you explain what you want it to do, in words, - " if two
accounts are different then I want to..., if they are the same I want them
to....". Are the accounts always in numerical order and if not what is it you
want to do.

 

[James]

Shane,
Thank you for your comment. You were right I did not go into enough detail
on my problem. I guess it is harder to put these more complex problems into
writting, when you are having a hard enough time to come up with some thing
that works.

However, I did fix my problem I am using the following formula:
=IF(AND(G3=G2,G3=G1),I3-I1,IF(G3=G2,I3-I2,""))

James

 

[David Biddulph]

=IF(AND(G3=G2,G3=G1),I3-I1,IF(G3=G2,I3-I2,""))
can probably be shortened to
=IF(G3=G2,IF(G3=G1,I3-I1,I3-I2),"")