Several If Formulas in one If statement not bringing back what i want.

[Jaffabfc]

Hi,

So i have a set of data that i want to pull information of and deplete as i get more and more for example.

Below, in column Z i have the current number, so if cell A2 matches the above (this one doesnt due to it been the first cell with data) i want it to see if the number in Z2 is bigger than I2 if it is then the answer is I2.
If it is smaller then the answer is Z2. But if AB1 is populated with a number and A2 and A1 are the same number then use AB1-I2.
But then in column AB2 this would be Z2 - I2 but only if A2 and A1 match, if they dont it would be AB1-AA2

The formula i have in AA2 is "=IF(A2=A1,IF(AB1=0,0,AB1-(AB1-I2)),IF(Z2>I2,I2,Z2-I2))"
The formula i have in AB2 is "=IF(I2>AA2,0,IF(A2=A1,IF(AB1-I2<=0,0,AB1-I2),Z2-I2))"
So really on the table below AA3 should show 31 because AB2 is 31 and its less than I3.

When you get to A13 the number changes so it would start back from scratch and not use any data from above.

Hope this explains it well enough

 

[ExceLoki]

with your formula you have: AB2-(AB2-I3)=31-(31-200)=31-(-169)=31+169=200
hope this helps explain why you are getting 200 instead of 31

 

[Jaffabfc]

that still doesnt seem to be working, doesnt make any difference.

Its really frustrating this one, even though i think it should be so easy

 

[Jaffabfc]

anyone able to help?

 

[Jaffabfc]

got it....

"=IF(A2=A1,IF(AND(A2=A1,AB1=0),0,IF(AND(A2=A1,AB1>I2),I2,IF(AND(A2=A1,AB1<I2),AB1,IF(SUM(Z2-I2)>0,I2,Z2-I2)))),IF(I2>Z2,Z2,IF(AND(A2=A1,AB1=0),0,IF(AND(A2=A1,AB1>I2),I2,IF(AND(A2=A1,AB1<I2),AB1,IF(SUM(Z2-I2)>0,I2,Z2-I2))))))"
Even though i think it could be a lot smaller

 

[AlanY]

try this

=IFERROR(IF(OR(A2<>A1,AB2=0),MIN(Z2,I2),IF(AB2<=I2,AB2,MIN(Z2,MAX(I2,AB2)))),0)

 

[ExceLoki]

got it....

"=IF(A2=A1,IF(AND(A2=A1,AB1=0),0,IF(AND(A2=A1,AB1>I2),I2,IF(AND(A2=A1,AB1<I2),AB1,IF(SUM(Z2-I2)>0,I2,Z2-I2)))),IF(I2>Z2,Z2,IF(AND(A2=A1,AB1=0),0,IF(AND(A2=A1,AB1>I2),I2,IF(AND(A2=A1,AB1<I2),AB1,IF(SUM(Z2-I2)>0,I2,Z2-I2))))))"
Even though i think it could be a lot smaller

if you just want it smaller, can try this

=LET(a,IF(AND(A3=A2,AB2=0),0,IF(AND(A3=A2,AB2>I3),I3,IF(AND(A3=A2,AB2<I3),AB2,IF(SUM(Z3-I3)>0,I3,Z3-I3)))),IF(A3=A2,a,IF(I3>Z3,Z3,a)))