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]

This is a circular reference because you're making C4 the result of a test to see if C4 > I4.

Are the below references all correct ?

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

Or are you saying ?

=IF(AND(B4>0,C2>0,H4>0,(B4+C2+H4)>I4),C2-B4,0)

 

[cgreen]

Thank you for your reply. Yes, the references are correct. Since I want the final formula to be in C4, maybe it would be better if I put a sum of C2-B4 in column AA. What do you think??

 

[snasui]

...

 

[DonkeyOte]

I still dont' understand if you cell references are correct it implies one of the tests to determine as to whether or not to make C4 = C2 - B4 is whether C4 > I4... this is circular.

snausi - your formula doesn't work as it does not test that EVERY component is > 0 only the sum of those components.
EDIT: snausi's formula has been removed.

 

[cgreen]

sorry for the confusion .. let me try again

give me a second to figure out how to attach the spreadsheet

 

[hema kangya]

You can try and evaluate your calculation manually and check does it gives you a logical result.

 

[cgreen]

A B C D E F G H I
2 Project Open Closed 6-Jun 13-Jun 20-Jun 27-Jun Total if Closed
4 MR8007 5/6/08 6/5/08 30 -- -- -- -- 30
5 MR8009 6/5/08 6/12/08 1 7 -- -- -- 7
6 MR8010 6/13/08 6/20/08 -- -- 7 -- -- 7
7 MR8011 6/19/08 6/26/05 -- -- 1 7 -- 7
8 MR8012 6/25/08 -- -- -- 2 -- --
9 HL8024 4/15/08 52 59 66 73 -- --
10 HL8031 5/25/08 12 19 26 33 -- --

D4 thru I10 are formulas ... does this make sense as to what I am trying to do?? I think I am making this harder then I need to.

I am trying to do a month ending report week by week, but if the project closes I want the clock to stop, or if the project starts mid-month I only what to count days for the week it applies only.

 

[DonkeyOte]

so which formulas are you trying to write in terms of column ?

also I'm presuming the values in the weeks are days passed on the project in which case from where do you get the prior week roll forward value for wk1 column ?

Sorry if we're dragging this out -- just trying to get all the pieces together before providing a possible solution.

 

[cgreen]

sorry lasw10 ... i posted a seperate thread trying to change the thought process ... as you can tell I am not having much luck.

D4 = IF(C4="blank",AA4), --IF(AND(C4>0,D2>B4),C4-B4), --IF(and(C4>0,D2<B4),"")

Trying to make something work ... how would I rewrite this formula??

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.