finickyone

If we wanted to filter W where X = 3 or 12 or 5, we could set up

=FILTER(W,(X=3)+(X=12)+(X=5))

But it’s a preference of mine to apply COUNTIF, by setting those values in Z2:Z4 then

=FILTER(W,COUNTIF(Z2:Z4,X))

With some inversion, we could set Z2 to >=3, Z3 to <12 and Z4 to <>5, and then

=FILTER(W,BYROW(X,LAMBDA(i,AND(COUNTIF(i,Z2:Z4)))))

Which allows us to set the comparisons operators in the source cell(s).

It’s around this that I’ve tinkering with setting series of critieria, which may apply in OR or AND conditions per set, but am a bit stuck. Example below. I can approach each critieria field independently, and byrow AND/PRODUCT the seperate results, but how do I get MAP to parse t

Just tabling a little challenge/discussion piece for us. To generate all repeating, non repeating permutions and combinations of a set of inputs.

Context: we have a list of 4 items in E2. {"P";"Q";"R";"S"}. We can refer to this list (set) as E2#. Above in E1 we have COUNTAed that set to 4. In F1 we have defined 2. In A1:C1 we’ve printed {"Power","Permut","Combin"}.

In A2 we want to define all pairs (re 2) of those 4. Colon seperated. So we’ll end up with 16 outputs.

P:P

P:Q

P:R

P:S

Q:P

…

S:S

In B2, we want all permutations that pair those items. Similar to A but items can’t be repeated within the pairing. They’ll amount to 12:

P:Q

P:R

P:S

Q:P

Q:R

…

S:R

In C2, all combinatio

Apologies in advance if this contravenes; I did find a similar post from 2 years ago, but thought the topic worth revisiting. I also promise I’m not a MSFT BA fishing for innovations.

What would you like to see next added to the function library? What do you think most challenges people in Excel, that ought to be met with applicable native functions? It may be that LAMBDA now enables many of us to create broadly useful enhancements; would there be any you’d nominate? Do we want GET.CELL back?

As a starter, I’ve long found it surprising that there isn’t a single formula which, plainly at least, declares the area of a range. A1:F10 is 6x10 cells, but our only routes to eliciting 60 as a val

I’ve been messing around for a bit trying to use MEDIAN as a way to determine whether a value lays between two bounds. Say G3 = 4 and H3 = 6, then we can test if C3 lays between them via COUNTIFS(B2,"<="&G3,B2,">="&H3). We can also test via B2=MEDIAN(B2,G3:H3). For isolated tests it’s pedantic, but I’ve been trying to refine an approach that assess which of a series of bounds a series value falls between.

I think I’ve gone a bit inside out with the LAMBDA. Does anyone have any advice (besides doing it the easy way with COUNTIFS/combo MATCH tests.

Boredom has struck me, and spurred by a couple of recent posts regarding tackling complicated formulas, I wondered how one might analyse a formula for complication. Which lead me without irony to create some rather complicated formulas to do so.

These both aim at a formula in C2. The first attempts to extract the functions that it calls for. The latter aims to call out cell/range references.

=LET(p,TEXTSPLIT(MID(FORMULATEXT(C2),2,40000),,MID("(&^/*+-,=",SEQUENCE(9),1),1),c,LEN(p),m,MAX(c),GROUPBY(p,p,COUNTA,,,,BYROW(MID(REPT("A",m-c)&p,SEQUENCE(,m),1),LAMBDA(x,AND(x>="A",x<="Z")))))

=LET(p,MID(FORMULATEXT(C2),2,40000),l,LEN(p),c,MID(p,SEQUENCE(,l),1),b,ISEVEN(MATCH(CODE(c),{0;36;37;

Hello all. An earlier post led me back to looking at GET.CELL, the XLM function which can elicit cell metadata, and in turn has me convinced that that suite also had some sort of RANGE.AREA function.

As we know we have ROWS and COLUMNS functions in the main ws library. For rng = B2:D7, ROWS(rng) returns 6, COLUMNS(rng) = 3, and the product of those tells us that rng is made of 18 cells.

Methods to determine that rng is 18 cells are abundant, and in many cases quite snappy. I’d suggest that the above is common, as is ROWS(TOCOL(rng)), or (the only single function approach I can think of) COUNTA(rng&0), but does anyone know of a dedicated function that returns a scalar representing the size

I’m working on a concept project and curious what approaches could be taken for a certain stage of it. The short of it is that I’m wondering what string validation, potentially dictionary, services could be employed into an Excel workbook.

Context:

I intend to generate an array of randomly selected letters (repeatable). Against that, I would like to:

1. Supply words that I believe could be constructed from the available letters, and turn to some sort of dictionary service to validate that they are legitimate words.

2. Likely employing such a service again, call in the words that could be generated from the available letter set. Length bounds would probably be prudent constraint.

My h

Perhaps only a TIL to me, but seen in another post it appears that that SUMIFS-etc suite can ignore errors, to some degree at least.

TL;DR: SUMIF(rng,">=0") sums all positive values in range, even if errors are present in range. SUMIF(rng,">-9e307") sums all values over roughly the lowest negative val that can be stored. So effectively {=SUM(IFFEROR(rng),"")}.

I would have expected errors to float out from these functions (I might argue that’s actually more like expected behaviour). As we likely know, these functions don’t allow for arrays to be supplied as into the range arguments, so we don’t get to apply something like SUMIFS(IFERROR(values,""),IFERROR(names,""),"Bob") but owing an

Perhaps only news to me, but seen in another post it appears that that SUMIFS-etc suite can ignore errors, to some degree at least.

TL;DR: SUMIF(rng,">=0") sums all positive values in range, even if errors are present in range. SUMIF(rng,">-9e307") sums all values over roughly the lowest negative val that can be stored. So effectively {=SUM(IFFEROR(rng),"")}.

I would have expected errors to float out from these functions (I might argue that’s actually more like expected behaviour). As we likely know, these functions don’t allow for arrays to be supplied as into the range arguments, so we don’t get to apply something like SUMIFS(IFERROR(values,""),IFERROR(names,""),"Bob") but owing an i

Conditionally/electively selecting a function to apply to common variables

Happy weekend everyone. I’ve got this idea on my mind. It’s just academic, curiosity based, so no IRL challenge, just after ideas & discussions.

Q: is it possible to set up LAMBDA(?) in such a way that one of a similar set of functions could selectively be applied against a common set of variables? Or ideally that multiple functions could be applied to those variables. Will add some example data as a screenshot in comments as Reddit is being tricky, but for context:

. A B C D E     F     G     H
1               SUMIF AVGIF MAXIF MINIF
2 A 1   A 
3 A 2
4 A 5
5 B 2
6 B 3

Where along E2:

Happy FriYAY to you too! To think you've only 4 of these to go before a summer in paradise! I'm just home - my phone died in the locker at church. It didn't charge fully last night and then I rinsed it listening to some Reimagining Love on the way down. Spin was good, morning sessions are always trickier. Thank you for the evening offer next week. I've a bit of a plan for a Tuesday that I think you'd like...

Will ofc let you know how the career chats go - thank you for the encouragement. My voice is a bit scratchy but I should be good. How will your day look? Did Shola and Mummane (sp?) get back to you?

Hope you are feeling well, and that an absolutely amazing day lays ahead for you. Revel

Looking to mix things up with a formula challenge. From Project Euler, via an earlier recommendation as training material from u/Downtown_Economics26:

** If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.**

** Find the sum of all the multiples of 3 or 5 below 1000.**

Given these tend to instigate a codegolfing race toward baffling brevity, I’m going to ask that, for the benefit of learning and confidence building, those that propose approaches outline:

1. The approach they’d have taken with the skills amassed in their first year of using Excel.

2. The most novel approach they have with their skills to date.

Just the latest inane matter to cross my mind. Can we elect a function to employ, by referring out to a location where that function is named? Imagine, somewhat akin to INDIRECT:

A2:A6 : {1;2;3;4;5}

C2: 'CONCAT

D2: formula(A2:A6,C2) = "12345"

Where CONCAT is basically a variable.

I’m aware of the Evaluate() function tucked away in the macro/4.0 suite, as well as employing a reference table to use C2 to determine a subfunction value for AGGREGATE/SUBTOTAL, to apply one or many from a limited suite of functions, but was just curious if anyone has done some worksheet LAMBDA magic on this front.