multi IF arguments to answer before calculating

cgreen

Active Member
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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DonkeyOte

MrExcel MVP
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

Active Member
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??

...

Last edited:

DonkeyOte

MrExcel MVP
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

Active Member
sorry for the confusion .. let me try again

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

hema kangya

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

cgreen

Active Member
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

MrExcel MVP
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

Active Member
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.

Replies
17
Views
562
Replies
9
Views
259
Replies
5
Views
230
Replies
8
Views
133
Replies
4
Views
205

1,191,182
Messages
5,985,167
Members
439,944
Latest member
Vangelis74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back