Reputation: 1737

Filter Cells as argument to a function in a formula

I am a programmer, so please bear with me. I understand that Excel isn't necessarily what I am used to in other domains, but I'm cracking my head open on how to accomplish something that seems somewhat simple.

I have a column of numbers that are themselves the basis of a formula. I want to filter those cells based on some criteria and pass them to another function to perform a calculation.

I understand that this can be done with "filters" in the excel sense. This would mean I would have to click multiple times for each calculation, filter the results, copy the value and paste it where I need it to be. If the data ever changes, I will have to do it all again.

What I am looking for is the equivalent of filtering in a programming language, here's an example:

let range = [1,2,3,4,0,-1,-2,-3,-4];
let subrange = range.filter(function (cell) { return cell > 0; });
subtotal(1,subrange);

So what my excel is like.

I have a column G, that has 12,000+ results in it, each one of these columns is like this:

=(En-Bn)/Bn

These are copied down, n means the row number from 5-12,000+

Now I would like to create a cell, M2 such that it contains:

=SUBTOTAL(1,[ Gn in G5:G12000 where Gn > 0 ])

The goal is that I do not want to have to point and click, because actually, there are many more cells I need to create (about 20) with similar kinds of "filter" predicates.

It would be nice, as much as possible, if I also don't have to specify the n...n-1 range of the column, as ideally that can change. Could be 10, could be 20,000, shouldn't matter.

The best formula or solution would be like:

SUBTOTAL(1, [ Gn in G0:GLENGTH where Gn > SOMECELL ])

Any pointers, or suggestions where to read, or a solution would be awesome. I've been searching on google, and it seems that I lack the right understanding to find the answer in the material presented.

Also, please excuse me for using programmer speak, I know that Excel formulas are not necessarily a 1:1, I'm just looking for a way to save time. Answers in VBA or using Macros are welcome, the main thing is to find a way to do it...

Best, Jason

Update

I should specify that it needs to be a bit backwards compatible, so I can't use the FILTER function that is only available in >= 365

Upvotes: 0

Answers (3)

Variatus

Reputation: 14383

I'm not at all sure that your attempts at saving time by talking in programming language instead of English really saves either time or space. My best effort determines that you got us all confused. Please tell me why the simple formula below doesn't work.

=AVERAGEIF(G2:G15000,">"&A1,G2:G15000)

This formula requires A1 to hold a number and the formula supplies the > sign. A variation would have A1 contain both, number and comparison, like >1.2`

=AVERAGEIF(G2:G15000,A1,G2:G15000)

The above formulas start the range at G2. Change to G5 if that is what you need. G15000 is a random number intended to be larger than anything you will ever need. The function ignores blanks. However, if you are worried about having a sheet with 16000 rows just on the day you forgot where to adjust the formula I would recommend the use of a named range which you could format to be dynamic.

Named ranges are neater to handle than range addresses and names can be given descriptively, such as HourlyReadings. The above formula would then look like this:-

=AVERAGEIF(HourlyReadings, ">"&A1,HourlyReadings)

Theoretically, the formula by which HourlyReadings is defined could also be written into the worksheet formula but it would become unwieldy. As shown above, you would have to look into the Name Manager to know if the range is dynamic or not but, of course, once defined you can use the same name in many functions and formulas which saves a lot of maintenance time.

Upvotes: 1