How to ignore non numeric values with TAKE function?

[Monty85]

Hi team,

I'm trying to find a solution where I can exclude values that are not numeric from the following formula.

=SMALL(TAKE(VSTACK(range1,range2),-n),1)

I've found some similar scenarios online but i'm struggling to incorporate those into my specific need to have the formula include VSTACK to pick up 2 ranges in separate columns.

Currently the above formula is treating non numeric cells as valid when it calculates the "-n" value to determine the range to look for the smallest value in.

 

[6StringJazzer]

I don't understand what "n" is. At first I thought it was a placeholder for an actual number of rows you put there, but then you said it calculates it. This formula doesn't calculate n. Can you clarify what you mean? Also, why do you need to use TAKE at all?

I suspect you're going to need to use FILTER in some way.

 

[Monty85]

I don't understand what "n" is. At first I thought it was a placeholder for an actual number of rows you put there, but then you said it calculates it. This formula doesn't calculate n. Can you clarify what you mean? Also, why do you need to use TAKE at all?

I suspect you're going to need to use FILTER in some way.

"n" was just a placeholder - sorry i might used the wrong terminology when I said calculates.

In the context of the formula, n = -10. So I want the formula to select the last 10 values from the range.

What i'm struggling with is getting the formula to ignore non-numeric values when it selects/calculates/works out what the last 10 values are.

 

[6StringJazzer]

Tested:

=LET(VS,VSTACK(range1,range2),SMALL(TAKE(FILTER(VS,ISNUMBER(VS)),-n),1))

 

[Monty85]

Tested:

=LET(VS,VSTACK(range1,range2),SMALL(TAKE(FILTER(VS,ISNUMBER(VS)),-n),1))

Thanks, that seems to work.

If I take away the VSTACK requirement, is this still the simplest way to achieve the result if the source is just from a single range?

 

[6StringJazzer]

Well, it's the simplest way I can think of, but the minute I say that someone else may come along with something simpler.

Fortunately you have 365. Without TAKE and FILTER this gets a lot more complicated.