Calculate by Match Criteria between Start/End Cells

[davidpierce]

Hi all,
I started a new post on this with a much simpler sample than previous. Seeking a number extracted between two columns, between 2 matched rows. Sample comments are in RED!

Thanks
Dave

[jayajaya_4]

hey dear..can I know what is the final output needed...I need a bit more clarification on ur calculation...

[davidpierce]

Hi Jayajaya,

The final output would be a #(The Amount of Days in) of MAX F - MIN D between the Criteria I specified.
Thanks

[davidpierce]

I forgot I hid column B so when I reference Col B, it is actually Col C
Me Bad

[Glenn Kennedy]

Surely the min date is ALWAYS going to be E7, simplifying everything... I used this approach a day or two back on another similar problem for you. How's this? I've left the intermediate steps in (for now).

[jayajaya_4]

ok ur column d doesn't contain any data??? a bit confusing even if I get max of end date,min of start date what after dat ....?after doing that I am getting =(MAX(F7:F22)-MIN(E7:E22))

[davidpierce]

Hi Glenn
Thank you and just wondering if we can get it not to calculate the rows 7 - 10 in column E in Sample? Everything else works great
Thanks

[Glenn Kennedy]

The way it's set up is that it's using a "complicated" match to get the end date from the row before the completion date row (Column F). The start date is taken from E7. Is it possible for there to be dates elsewhere that are BEFORE the date in E7? If you think that the formatting change in e11 to E18 has anything to do about it - it hasn't. That was just me "thinking aloud".

Or have I picked you up incorrectly?

[davidpierce]

Here are my though in the calculation. When I schedule a project, we have a mobilization time which is really not included in the actual working dates of the schedule, however, they do reflect general conditions and need to be on schedule. I need to get to the actual starting date of schedule which in this case would be Col A "2". Would it help if there was lets say a "start Date" Field on the page. Lets Say Schedule starts 2/18, any date before this would not be calculated? Would that help with formula. So, when I schedule Mobilization and it's dates are less than 2/18, then they would not affect this calculation making the MIN Date still remain 2/18?
Thanks

[davidpierce]

Hi Glenn,
I made a start date and changed $E$7 to $E$2 and that seams to work just fine, however may need a msg box if a date is entered in the section selected, to say, "date is before starting date" so it will give an error and notify user? What are your thoughts on that possibly?
Thanks

[davidpierce]

I guess that doesn't actually work either as changing that to $E$2 then it wont subtract them within the criteria MIN date? I don't know! Any thoughts on how to accomplish this?
Thanks
Dave

[Glenn Kennedy]

OK. So.... f I understand you. the preparatory work doesn't count. You only want to start the count once the boots hit the ground. Isn't that always going to be E11? If so, why complicate things by having ANYTHING in E2, and just set E11 to be the last erm in the formula? That's what I had originally (the brown shaded cells) until I read your requirement again and thought that you wanted the minium value of column E.

[davidpierce]

Hi Glenn,
Yeah, I need the MIN of E but only between the criteria. Row 11 will change but not the criteria. The Number 2 will always be in some row weather it be in 11, 19, 25...

The only thing I have in this for criteria that wont change is the Col A #2 and Col C project completion. I may add 1000 task between those two and I may add above and below them, but I don't want to calculate what comes before and after that, just between, yet I just cant remove them from the schedule if that makes sense to you

I cant imagine there is not a formula that will start the count based on matching Col A and #2 and between Col B PC.

I do need to seek a formula from VBA? That may be simpler?

Thanks

[Glenn Kennedy]

OK. I now understand why (I think) we need to calculate MIN. Leave it with me...

[davidpierce]

Hi Glenn,
Yeah, I need the MIN of E but only between the criteria. Row 11 will change but not the criteria. The Number 2 will always be in some row weather it be in 11, 19, 25...

The only thing I have in this for criteria that wont change is the Col A #2 and Col C project completion. I may add 1000 task between those two and I may add above and below them, but I don't want to calculate what comes before and after that, just between, yet I just cant remove them from the schedule if that makes sense to you

I cant imagine there is not a formula that will start the count based on matching Col A and #2 and between Col B PC.

I do need to seek a formula from VBA? That may be simpler?

Thanks

[Glenn Kennedy]

Oh No I Don't....... Am I looking for the date on which the boots hit the ground (i.e. matching "Site work" in B, on whatever row that might happen to be), or the lowest date in column E?

[Glenn Kennedy]

Your post at #15 appears to be a system duplicate. I have ignored it. can you answer my Qs at Post #16?

[davidpierce]

Sure will, thank you Glenn
Dave

[davidpierce]

I sure will Glenn and Thanks a bunch!

[davidpierce]

Sorry Glenn, I missed this post! In any event, I am looking for the boots on the ground such as "Site Work in B" yet I went with the "#2 in A" as site work wont always be site work. It depends on project what it start with but the #2 will always be the boots on the ground. The #2 is txt!

[Glenn Kennedy]

Arrgghh I forgot about you... Is this it? It's finding "the row of "site work" in C; returing the corresponding date from E and subtracting that from the previously agreed completion date. I'm here for an hour or so, before pub time comes around...

[davidpierce]

Hi Glenn,
This does work however is there a way to get the "2" in col a instead of Site Work in col C? The "2" in col A will always be the same "2" but Site work may change to Demolition..... depending on what type of project we are doing.
Thanks

[Glenn Kennedy]

Dohh! The penny drops...

[davidpierce]

That is perfect Glenn, thank you so much! Now I will figure out how to get the NETWORKINGDAYS out of that calc and will be wrapped up!

Thanks
Dave

[Glenn Kennedy]

That shouldn't be difficult... Like this, maybe?

[davidpierce]

Well now isn't that just fantastic! Thanks so much Glenn! You are just great at what you do, that's for sure.
Dave

[Glenn Kennedy]

Another satisfied "customer"... thanks!

[davidpierce]

Hi Glenn,
I took your formula and changed it to fit my actual schedule sheet I am using and getting an error #N/A. I have checked and checked the difference in formulas and the only thing that has changed is the columns and rows. Any idea why I am getting this error? I attached the actual file I am using. Your formula is in cell "L2"
Thanks
Dave

[Glenn Kennedy]

Hopefully this is it.

1. The problem was (probably) that I took a shortcut in the dummy sheet setting the startin point of the range from boots-on-ground to project completion. I've fixed it by getting it (again) to search for a 2 in the left hand column.

2. The formula also got a bit confused 'cos you have 2 entered as text, in the LH column, not as a number. That's fine, but I needed to get the formaul to look for a text 2 instead of a number 2 (the joys of Excel...).

3. The starting point for looking at the boots-to completion date had somehow wound up as being row 26. That was fixed by the actions at 1), above.

4. The 6 that was added to the range row to return the sheet row had not been changed from 6 (dummy sheet) to 11 (real sheet).

Any other teething probelems, just shout...

[davidpierce]

Thanks Glenn,
Sure something will come up before I am all done, LOL!
Thanks again,
Dave

[Glenn Kennedy]

It's bound to...

[davidpierce]

Hi Glenn,
I am having a major problem trying to get a couple of other formulas to work on this. If I could get you to assist me that would be great since you are familiar with the whole project so far. Sample Attached "MySample8"

I am trying to get "G7- (Max F - Min D)" From our boots on the ground day, "2" Site Work, Row 26 this sample" all the way to "Row 49, this sample" and put that result in "L4" Scheduled Lag Days. I tried this but it really doesn't work correctly and cant figure out how to subtract the G7 before it?
=MAX(INDIRECT("F"&11+MATCH("2",$A$12:$A$49,0)&":F"&11-1))-INDIRECT("D"&11+MATCH("2",$A$12:$A$49,0))

Second, I am trying to Do a "Rounddown for a total of G Column * total of L Column, from our boots on the ground day, "2" Site Work to the end, row 49, this sample" and put result in "O5"

And Finally, I am trying to "Max Days Column E - Max Days in Column L" from our boots on the ground day, "2" Site Work to the end, Row 49, this sample and the result of that would go in "O7".

[davidpierce]

Glenn,
I was thinking that maybe I am making these last calculations harder than they need to be? I don’t know how to write the formulas, but here is what I am thinking may work?

Scheduled Lag Days (L4)
=E10 which is =IFERROR(MAX(F12:F50)-D10+1,0) - G7
If any dates in Column D are >= (This would be for all dates being >=F3)
Start Date (F3)

“L4 =G7- E10 If D10:D50 >= F3” Something like this? So any date that is before F3 will not calculate!

Completed Days (O5)
=J10 Which is =ROUNDDOWN(G10*L10,0)
If any dates in Column M are >= (This would be for all dates being >=F3)
Start Date (F3)

“O5 = J10 if M10:M50 >= F3” So, if any dates in Column M are before F3 they will not claclulate!

Loss/Gain -/+ (O7)
"=(F10-N10)-(D10-M10) if Column D10:D50 or M10:M50 is >= F3" So if any dates in Col D or M is less than F3 they wont calculate!

I don’t know if this would be simpler or not but it seams to me it would be workable? Basically they all add the whole column with exceptions to dates before the start date! If that makes sense?

Thanks
Dave