Using array formula with if and or

[Howlin]

Hi,

I've got a spreadsheet where we put in product data for a number of different products, labelled a, b and c for ease.

Now as part of our process control I want to be easily able to see the last 10 moisture results of a certain product in a graph.

So I can do this with =IFERROR(INDEX(Process!I:I,LARGE(IF(Process!E:E="A",ROW(Process!I:I)),ROWS($1:1))),"") and just change the rows down to 10. I can then plot this as a graph and easily see the last 10 data points of product a

Now one of our two products are very similar and our infrequently made so it makes sense for us to combine them b and C

So I think I need to use an or function in that so that it picks either the last incident of B or C. Now I've tried this multiple times and the best outcome I've got is a zero.

What I wanting is for excel to look up the last 10 incidents where there has been a B or C in column E and the moisture results in column I.

Any help will be greatly appreciated.

[Khalidngo]

Hi Howlin,

I am not 100% sure is that you're looking for:

In Moisture sheet A23:
=INDEX(Process!$A$2:$A$9999,MATCH(LARGE(IF(OR(Process!$E$2:$E$9999={"B","C"}),Process!$A$2:$A$9999),ROW(A1)),Process!$A$2:$A$9999,0))

To be confirmed with Ctrl+Shift+Enter
copy down...

Blessing

[Howlin]

Hi,

That's not giving the outcome I'd expect. :S

Seems to be finding the highest values.

I've update the spreadsheet here.

On Sheet excel query 6 response in Cell D40-D49, E40-E49 there is the expected dates and the Expected moistures.

A better explanation of what I'm trying to do is filter by product code B/C and then just pick the last 10 data points.

Cheers,

Tom

[Khalidngo]

Hi Tom,
Sorry I wasn't sure 100% as mentioned in first post.

It may required vba, I am not sure, hope someone will update you.

Regards,

[MarvinP]

Hi Tom,

It seems to me there should be a way to do this problem using Pivot Tables. Pivots can filter the B and C to only show. If your example had lots of dates then using a "Date After" in the pivot would also work. See the attached for a quick guess at what you might need.

[bebo021999]

Maybe:

D40:

Please Login or Register  to view this content.

E40:

Please Login or Register  to view this content.

Confirmed with CSE

Drag both down

[Howlin]

Thanks guys,

I got it with bebo021999. Thanks for your help guys.

Cheers,

Tom