Formula: Finding todays date minus min value from certain range + extra condition

[rain4u]

Hi all
I need a help with one formula. I almost have it but need help with last condition.
Data2 is a defined name of column B to column BG. I use this with some other sumproduct formulas.

Please Login or Register  to view this content.

Current formula that I have is this
=TODAY()-MIN((INDEX(_Data2,0,8)))
So it is todays date (lets say 04/06/2011) minus minimum date from (INDEX(_Data2,0,8) which happens to be column I. Lets have an example

Column C --------------- Column I
1000137 ------- 05/06/2011
1003606 ------- 03/06/2011
1003606 ------- 03/06/2011
1003606 ------- 04/06/2011
1000137 ------- 01/06/2011
1000137 ------- 06/06/2011
1000137 ------- 07/06/2011
1000137 ------- 08/06/2011
1000137 ------- 09/06/2011

Minimum date on column I is 01/06/2011
so 04/06/2011 - 01/06/2011 would be 3 days.

Now I would like to add extra condition to my formula and I have not managed to get it right so far. I would like the formula to look at (INDEX(_Data2,0,2) which is Column C and (INDEX(_Data2,0,8) Column I together and check only rows where the Column C value is 1003606. So when looking the minimum date, it would look the red area only.

Lets have an example:
Column C --------------- Column I
1000137 ------- 05/06/2011
1003606 ------- 03/06/2011
1003606 ------- 03/06/2011
1003606 ------- 04/06/2011
1000137 ------- 01/06/2011
1000137 ------- 06/06/2011
1000137 ------- 07/06/2011
1000137 ------- 08/06/2011
1000137 ------- 09/06/2011

So the right answer would be:
Minimum date on column I is 03/06/2011
so 04/06/2011 - 03/06/2011 would be 1 day.

So how to change =TODAY()-MIN((INDEX(_Data2,0,8))) to suit my needs???

Any help is much appreciated.
Cheers
Rain

[p24leclerc]

you could use the formula BDMIN with a criteria on col C. See attached file where I show how it works. As my Excel is in french, I can't copy ans paste the formula because you won't understand it as the function's name are in french.
Pierre

[rain4u]

Hi Pierre
Thank you for trying to help me. I think I should been more accurate with my description. Col C and Col I stand for Column C and Column I. They are not header text. I will edit my original post to make it less confusing.

My main problem is that the data is on another sheet. So formula is on one sheet and data is on the other sheet. Thats why I'm trying to use using defined name _data2 to get the job done. I couldn't use your offered formula to get it working.

Any other ideas?
Cheers
Rain

[anchuri_chaitanya]

Hey i too have similar query regarding.. please can anyone help me out..

Find the data i have attached..

Data is in Column A..

Data in Column D is the fixed set.. using column D have to map this data to column A, as the column A had repeated/unique values in it.. the data in column D should be mapped accordingly i have typed in column B.. Please help out with any formula..

[rain4u]

Hi
My formula would differ from the previous guys requirements. So I thought I will attach a spreadsheet as well.
Current formula is in E13 which returns value 84

Please Login or Register  to view this content.

I would like the formula to find the minimum date from column I as it does at the moment but only from the rows where column C value is 1003606. Column C-s range as per defined name would be INDEX(_Data2,0,2). I have highlighted the correct range in yellow where it should look the date and I highlighted the minimum date in red.
Today is 4th of June. Minimum date on this spreadsheet that will meet the criteria is 10/05/2011. so the difference between these would be 25 days.

Can someone look the spreadsheet and see if this can be figured out.

Many thanks
Rain

[rain4u]

After messing about with it for the last day or so, I figured it out myself.

Needs to be entered as array formula. So when you are finished with formula you need to press Ctrl+Shitf+Enter

Cheers
Rain