Add a Column to MIN OFFSET COUNT Formula

[emgo]

I hope this is simple question. These formulas work great for one column.

=MIN(OFFSET($AE$11,COUNT($AE$11:$AE$200)-20,0,20,1))

I've searched, tried several guesses of my own and can't find a way to add a column to this formula. I need to the earliest / minimum date from the combination of two different columns with a dynamic range. The column I need to add will have the same row range and something like column AO. Of course I'll need the maximum / latest date also.

=MAX(OFFSET($AE$11,COUNT($AE$11:$AE$200)-20,0,20,1))

I can upload an example of the data if that would help.

Thanks,
Ken

 

[bosco_yip]

Try,

1] The earliest date of the last 20 dates in Column AO in respect of Column AE :

=OFFSET($AO$11,COUNT($AE$11:$AE$200)-20,0,)

2] The latest date of Column AO in respect of Column AE :

=OFFSET($AO$11,COUNT($AE$11:$AE$200)-1,0,)

Regards
Bosco

 

[emgo]

Bosco,
I think I didn't do a very good job of explaining what I needed or I didn't enter your formulas in correctly. I need the earliest and latest dates from the last 20 entries(dynamic range) from the combination of the two separate column date ranges. I should have included an example so here it is, maybe that will clarify my request.

Again, thank you so much for taking your time to help.
Ken

I'm mostly guessing but this is what I have come up with. They provide results but not the correct results.

Placed in BP23
=MIN(OFFSET($AE$11,0,0,MAX(COUNTA($AE$11:$AE$200),COUNTA($AO$11:$AO$200)-20,0,20,1)))

Placed in BP25
=MIN(OFFSET($AE$11,0,0,MAX(COUNTA($AE$11:$AE$200),COUNTA($AO$11:$AO$200)-20,0,20,1)))

 

[bosco_yip]

Bosco,
I think I didn't do a very good job of explaining what I needed or I didn't enter your formulas in correctly. I need the earliest and latest dates from the last 20 entries(dynamic range) from the combination of the two separate column date ranges. I should have included an example so here it is, maybe that will clarify my request.

Again, thank you so much for taking your time to help.
Ken

I'm mostly guessing but this is what I have come up with. They provide results but not the correct results.

Placed in BP23
=MIN(OFFSET($AE$11,0,0,MAX(COUNTA($AE$11:$AE$200),COUNTA($AO$11:$AO$200)-20,0,20,1)))

Placed in BP25
=MIN(OFFSET($AE$11,0,0,MAX(COUNTA($AE$11:$AE$200),COUNTA($AO$11:$AO$200)-20,0,20,1)))

Try,

1] Add 1,2,3,4…..to cell A1, F1, K1, P1…..etc.

2] Add new column named "Table no." in Column BJ

3] Names of Items and Characteristics all followed the Table heading.

4] In Column BP "Date Range" formula :

BP8 =OFFSET(INDEX(A$11:BG$11,,MATCH(BJ8,A$1:BG$1,0)),COUNT(INDEX(A$11:BG$200,,MATCH(BJ8,A$1:BG$1,0)))-20,)

BP9 =TEXT(BP8,"mm/dd")&" - "&TEXT(BP10,"mm/dd")

BP10 =OFFSET(INDEX(A$11:BG$11,,MATCH(BJ8,A$1:BG$1,0)),COUNT(INDEX(A$11:BG$200,,MATCH(BJ8,A$1:BG$1,0)))-1,)

Select BP8:BP10 >> Copy and paste to the items in below .

5] In Column BQ "Ave" BQ8, formula copy down :

=OFFSET(INDEX(A$2:BG$2,,MATCH(BJ8,A$1:BG$1,0)),,MOD(ROWS($1:1)-1,3)+1)

6] In Column BR "Cpk" BR8, formula copy down :

=OFFSET(INDEX(A$4:BG$4,,MATCH(BJ8,A$1:BG$1,0)),,MOD(ROWS($1:1)-1,3)+1)

Regards
Bosco

 

[emgo]

Bosco,

I kind of see what you did and I am amazed by your knowledge and willingness to help someone like me who has little knowledge of Excel. The “Summary Table” format with 10 tire size/brand rows located in the Summary Sheet is currently being used in Power Point presentation that gets updated about every 2 weeks. Currently some of that data is manually copied and pasted into the Summary table. I was hoping to make the process a little more automated without altering the “Summary Table” format because it’s what the recipients of the Power Point are accustom to seeing. Since the P54J and P54k share 2 tire size/brand combinations that’s why they are combined in the “Summary Sheet”. I was hoping to avoid having to alter the size/format of the “Summary Table”.

I’m basically a mechanic in test lab that occasionally make charts with Excel. I have some added responsibilities this year so that’s why I’m having to expand my Excel usage. Many times I have tried help someone with a simple engine noise or brake judder question and been pulled into a 2 hour explanation of how an engine works or how a warped rotor can make your brake pedal pulsate. So don’t let me use too much of your time. I want you know I appreciate the time you spent on this so far.


Ken