Product and Index functions

[ssword]

I've attached a sheet that goes with this explanation of what i want it to do. Its a bit hard to explain.

I want to have Sheet1!C5 find the last date in the given month on Sheet2!f5:S5 which will be used to find the product of any data that's due date is in that month. I've sat and tried a whole bunch of things but i can't quite figure the logic out in this beast.

[NBVC]

Are you also matching up with a specific product (like Thing 4) and get the intersecting percentage value?

[ChemistB]

In your example, on Sheet1 C5
=MAX(IF(TEXT(Sheet2!$F$5:$I$5,"mmmm")=C$4,Sheet2!$F$5:$I$5,0)) (CNTRL SHFT ENTER)
It is an array formula so requires CNTRL SHFT ENTER instead of ENTER
You'll need to spell February correctly for it to work.

[ssword]

I've attached a sheet that goes with this explanation of what i want it to do. Its a bit hard to explain.

I want to have Sheet1!C5 find the last date in the given month on Sheet2!f5:S5 which will be used to find the product of any data that's due date is in that month. I've sat and tried a whole bunch of things but i can't quite figure the logic out in this beast.

Yes and no, I need the percentages to find the product of that column.

[NBVC]

Who are you addressing.. you quoted yourself?

Either way, what exactly are the givens and what are the expected results? (show examples).

[ssword]

Meant to quote you,the sniplet chemistb gave me didn't work.

[NBVC]

So are you looking for a list of products that have a positive percentage on the last date of the chosen month? I am still at a loss as to what you are needing.

[ssword]

I need it to do the following:
1. Find the last date given in a month base on row 5 in sheet2
2. Using the cell above's column find the average not ignoring zeros of rows 6-35

The end result for jan should be 7%(H would be the column), for feb it should be 23.5(F would be the column).

[NBVC]

Since it is still about uncertain your actual requirement, I will go out on a limb and guess...

Here attached, I have extracted all products that fall under the latest dates in the table for the specific months indicated.

Firstly, I added a couple of helper rows above to get the last respective monthly dates, and the count of percentages over 0%

Respective formulas in C2 and C3 are:

=MAX(IF(TEXT(Sheet2!$F$5:$I$5,"mmmm")=C4,Sheet2!$F$5:$I$5))

confirmed with CTRL+SHIFT+ENTER not just ENTER

and

=COUNTIF(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C2,Sheet2!$F$5:$I$5,0)),">0")

confirmed with just ENTER..

Both formulas copied across the columns.

Then to extract the products from Sheet2, in Sheet1, C5:

=IF(ROWS($A$1:$A1)>C$3,"",INDEX(Sheet2!$B$6:$B$35,SMALL(IF(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C$2,Sheet2!$F$5:$I$5,0))>0,ROW(Sheet2!$B$6:$B$35)-ROW(Sheet2!$B$6)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied over to next column(s) and down as far as you need to... you will see blanks after all items are extracted.

Hope that is what you were after.

[ssword]

NBVC that is something of what i want but instead of it outputting "thing 1" or "thing 5" I want it to output the average of only the things' data that occurred for that month.

[NBVC]

Revise formula in C5 to:

=IF(ROWS($A$1:$A1)>C$3,"",INDEX(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C$2,Sheet2!$F$5:$I$5,0)),SMALL(IF(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C$2,Sheet2!$F$5:$I$5,0))>0,ROW(Sheet2!$B$6:$B$35)-ROW(Sheet2!$B$6)+1),ROWS($A$1:$A1))))

confirm it with CTRL+SHIFT+ENTER and copy over to next column and down.

You may need to format results as Percentage.

[ChemistB]

Taking into account your requirement 2 from post #8 above, take a look at the attached spreadsheet. Am I getting closer?

[ssword]

Taking into account your requirement 2 from post #8 above, take a look at the attached spreadsheet. Am I getting closer?

You sir are awesome! This is exactly what i need, now to pull it apart and figure out how you did it. Setting as solved!

[NBVC]

I didn't read that the same way or didn't notice your edit...... but possibly:

=AVERAGE(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C2,Sheet2!$F$5:$I$5,0)))

copied across.

[ssword]

After plugging that into my i realized that it isn't correct. The C5 cell should only average the rows that have a due date of the column that they are currently averaging. And my original explanation was wrong, Sheet1!C5 should end up being 16, ((7+5)/2), and Sheet1!D5 should be 5, ((10+0)/2).

[NBVC]

So both averages in C5 and D5 are coming from column H (1/25/11) column?

If so, why?

[ChemistB]

Okay, I updated my worksheet in Post #12. In Sheet1C6 (dragged to D6)

=SUMPRODUCT(--(TEXT(Sheet2!$D$6:$D$11,"mmmm")=C$4), INDEX(Sheet2!$F$6:$I$11,,MATCH(Sheet1!C$5,Sheet2!$F$5:$I$5,0)))/ SUMPRODUCT(--(TEXT(Sheet2!$D$6:$D$11,"mmmm")=C$4))

For Jan = (25+7)/2 = 16, For Feb = (54+0)/2 = 27

[NBVC]

With same assumption that you got the 5 (10,0) wrong...

I added formula:

=AVERAGEIFS(INDEX(Sheet2!$F$6:$I$35,0,MATCH(C3,Sheet2!$F$5:$I$5,0)),Sheet2!$D$6:$D$35,">="&EOMONTH(Sheet1!C3,-1)+1,Sheet2!$D$6:$D$35,"<="&EOMONTH(Sheet1!C3,0))

copied across

[ssword]

Will test this tomorrow, thanks.