multi IF arguments to answer before calculating

[cgreen]

This one has me ... any ideas or help?!?!?

formula in C4
If B4>0, C2>0, H4>0, and sum in C4>I4 = C2-B4

 

[DonkeyOte]

ok so this is going in D4 -- this makes more sense, your earlier posts didn't specify yet implied C4 which was causing much consternation on my part as it would be circulalr (and thus not really work very well without much iteration)

So if C4 is blank then give me the value in AA4, if C4 has a value and D2 is greater then B4 then subtract C4 from B4. If D2 is less then B4 then leave cell blank.

D4 = IF(ISBLANK($C4),$AA4,IF($D$2>=$B4,$B4-$C4,""))

I am presuming from earlier posts that D2 is a constant and so I've made absolute reference (ie won't change if you copy this formula across a matrix)

I have also assumed if D2 => B4 then D4 = B4 - C4 (ie great then OR EQUAL to)

 

[cgreen]

almost there ... This formula is giving me the answer I'm looking for, but I'm still looking for a little more.

D4 =IF(ISBLANK($C$4),$AA$4,IF($D$2>=$C$4,$C$4-$B$4,IF($D$2<$C$4,$D$2-$B$4,"")))

E4 =IF(ISBLANK($C$4),$AB$4,IF($E$2>=$C$4,$C$4-$B$4,IF($E$2<$C$4,$E$2-$B$4,"")))

F4 =IF(ISBLANK($C$4),$AC$4,IF($F$2>=$C$4,$C$4-$B$4,IF($F$2<$C$4,$F$2-$B$4,"")))

G4 =IF(ISBLANK($C$4),$AD$4,IF($G$2>=$C$4,$C$4-$B$4,IF($G$2<$C$4,$G$2-$B$4,"")))

D2= 7/4/08, E2= 7/11/08, F2= 7/18/08, G2= 7/25/08, If B4= 7/2/08 and C4= 7/8/08, then I want D4= 2, E4= 6, F4= --, G4= --

Can this be done?

 

[DonkeyOte]

I think you tweaked my last formula a bit...

copy this into D4 -- then you can simply copy this formula across your entire matrix, there is no need to adjust it per column/row etc as I have set the relative references to meet your needs.

=IF(ISBLANK($C4),$AA4,IF(AND(COLUMN(D4)>4,C$2>=$C4),"",IF(D$2>=$C4,$C4-$B4,D$2-$B4)))

 

[cgreen]

perfect!! you have made me a very happy person ... thank you