How to filter out the last ten values and find how many times it exceeds X?

[Mattistangeraas]

Hi!

I do work with some sampling, and i do need to filter some data. My data set looks like this:

Sample point Date Value
A xx.xx.xxxx 10
B xx.xx.xxxx 5
C xx.xx.xxxx 15
A yy.yy.yyyy 12
B yy.yy.yyyy 10
A zz.zz.zzzz 21
.... ............. .....

I would now get the Excel to find the samples from the last ten dates of this data set, and the filter out how many time sample point A exceeds 5, 10 and 20.

Any good ideas of how i can this?

[tchock]

i'm not sure it's the best idea, but you could try it...at least until someone else comes up with a better solutionsample_.xlsx

[Mattistangeraas]

Hi there, and thanks a lot. I works fine.


But, i forgot to mention that what i really wants to know is how many of the values are between 0-5, how many between 5-10 and how many between over 20 of the last 10 records. How to rebuild the formula to that?

Thanks a lot for the help!

[tchock]

Hi,

i've tried a different approach, but it's still taking too much time to do the calculations (maybe if you have an idea of how many lines your database would have, you could narrow the search and excel will do less calculations, therefore, it will take less time).
I've made a helper column though, in order to keep a short formula in the output table...and i've defined 2 named ranges.
I'm sure this can be optimized, but, for now, this is what I came up with!
cheers!

[Mattistangeraas]

Hi! Will this formula also take in consideration the samples for the 10 last dates? What controls that?

[bebo021999]

See attachment.

I dont know how many items like A,B,C is your real data, so I created a unique list of items A,B,C or D and more items if needed.

[tchock]

Hi! Will this formula also take in consideration the samples for the 10 last dates? What controls that?

the named ranges are defined as the last 10 rows in your list via a formula; if you populate your list downwards, it will still calculate the last rows.

[Mattistangeraas]

Hi again! That formal works but not exactly the way i want. Maybe i was not good enough in defining my problem.

The case is that i want to know the formula to filter out all the A´s and the find the last 10 dates A was sampled, and then return how many times A was between 0-5, 5-15 and so on.

Would there be a good way to solve this?

I appreciate it!

[tchock]

Hi there,

Will there be a problem to use a pivot table?
Check out the attachment.
I made 2 helper columns though and I defined 2 dynamic named ranges, the first one is for the pivot table data source (it will update if you fill downwards your table with data) and the second one is for the sample points, so that we can count the last 10 entries for each of them.
Unless you don't have something against manually refreshing the pivot table, you can use a line of code in vba to refresh the pivot table automatically.
Let me know what you think.
cheers!

[Mattistangeraas]

Hi There!


This was useful, and i can use that. The only thing is that i have one more criteria to meet. Both customer 1 and 2 may have the sample "sample-point" and i need to make sure that they do not mix. All of the raw data is stored in one table, since i have a lot of customers.

I have been able to extract the Sample-point and the last dates for the sample by this formula, but it is not taking care of the customer:

=IF(IFERROR(INDEX($F:$G;LARGE(IF($F:$F=$L$4;ROW($F:$F));ROW(1:1));2));"";INDEX($F:$G;LARGE(IF($F:$F=$L$4;ROW($F:$F));ROW(1:1));2))

This works fine and by adding 2:2 and 3:3 etc to the ROW-function i get the last ten dates for the sample point, and by that list i do extract the ones larger than X and larger than Y.

But how to get this formula to check if the customer mentioned in A1 corresponds to the results are a big mystery. They are at the same row though.

Thanks for answers!

[tchock]

Is it possible to attach an example?

[Mattistangeraas]

Yes, here it is :-)Example.xlsx