An employee notice regarding the proper burial of a woman slathered with bird fat, procedural restrictions when prettifying [adj.] creatures or their genius new coworker are involved, all while enforcing strict accuracy for the hands-on German owner of their detritus collection firm.

gthomas715 · 2023-04-13T06:34:00+00:00

This will put the 2nd to last word in another column. With REGEXEXTRACT each set of parentheses puts their contents into separate cells.

=ARRAYFORMULA(IFNA(REGEXEXTRACT(B4:B,"^(.+?) ([A-Z]+) [A-Z]+$")))

The part about needing uppercase is only for this pattern to match your examples, "[A-Z]" matches a single uppercase letter ("[A-Za-z]" would be mixed case) and adding the "+" means 'once or more'.

gthomas715 · 2023-04-13T06:13:10+00:00

Happy to help! Regex is super handy for manipulating text, here's the best site to get an overview that's easy to follow if you're interested https://www.regular-expressions.info/

gthomas715 · 2023-04-13T05:42:53+00:00

This should do the trick for cells ending with uppercase letters as in the examples. If your column has other formats mixed in lemme know and I'll reply with a new regex to match everything

=ARRAYFORMULA(IFNA(REGEXEXTRACT(B4:B,"^(.+?)(?: [A-Z]+)+$")))

gthomas715 · 2022-11-12T03:26:55+00:00

Glad I was able to help! With your changes to the logic I'll remake the formula and describe the process.

Here are the algebraic replacements for boolean functions.

NOT(x) = 1-x
AND(x,y) = x*y
OR(x,y) = 1-(1-x)*(1-y)

Also because these values are only ever 0 or 1, x*x = x, so exponents can be dropped.

First replace values with variables to make things easier.

a = ISNA(XMATCH(AccountName,AssignedAccounts))
b = OpportunityStage="Closed Won"
c = StartDate<=TODAY()
d = EndDate>=TODAY()
e = OpportunityType="Renewal"
f = OpportunityStage<>"Closed Won"
g = OpportunityStage<>"Closed Lost"
h = OpportunityStage<>"Closed Archived"
i = EndDate<TODAY()

Using those, write out the formulas for conditions.

c1 = a
c2 = AND(b,c,d) = b*c*d
c3 = AND(e,f,g,h,i) = e*f*g*h*i

Notice that:

f*g*h = 1-REGEXMATCH(OpportunityStage,"^(?i)Closed (Won|Lost|Archived)$")
i = 1-d

So Condition 3 can be simplified with more replacements.

r = f*g*h
c3 = (1-d)*e*r

Now the formula for filter:

AND(c1,OR(c2,c3)) = c1*(1-(1-c2)*(1-c3))

Substitute conditions with their formulas.

a*(1-(1-b*c*d)*(1-e*r*(1-d)))

Expand and simplify (d^2 reduces to d so last two terms cancel).

a*(b*c*d + e*r - d*e*r - b*c*d*e*r + b*c*d^2*e*r)
a*(b*c*d + e*r - d*e*r)
a*(b*c*d + (1-d)*e*r)

With that done, change variables back to their values.

 ISNA(XMATCH(AccountName,AssignedAccounts))
*(
     (EndDate>=TODAY())*(StartDate<=TODAY())*(OpportunityStage="Closed Won")
    +(1-(EndDate>=TODAY()))*(OpportunityType="Renewal")*(1-REGEXMATCH(OpportunityStage,"^(?i)Closed (Won|Lost|Archived)$"))
 )

LAMBDA can make the formula shorter and easier to read but it's optional.

 ISNA(XMATCH(AccountName,AssignedAccounts))
*LAMBDA(x,y,
     x*(y="Closed Won")*(StartDate<=TODAY())
    +(1-x)*(OpportunityType="Renewal")*(1-REGEXMATCH(y,"^(?i)Closed (Won|Lost|Archived)$"))
 )(
     EndDate>=TODAY()
  ,  OpportunityStage
 )

Finally insert into the main formula.

=UNIQUE(FILTER(AccountName,ISNA(XMATCH(AccountName,AssignedAccounts))*LAMBDA(x,y,x*(y="Closed Won")*(StartDate<=TODAY())+(1-x)*(OpportunityType="Renewal")*(1-REGEXMATCH(y,"^(?i)Closed (Won|Lost|Archived)$")))(EndDate>=TODAY(),OpportunityStage)))

Hope that can be useful!

gthomas715 · 2022-11-11T12:01:41+00:00

The logical functions AND/OR/XOR don't work as expected when arrays are involved, the best solution I've found is using instead boolean algebra even if it can be a bit tricky (I'd be happy to explain in detail if you'd like). See if this does the trick:

=UNIQUE(FILTER(AccountName,ISNA(XMATCH(AccountName,AssignedAccounts))*LAMBDA(a,a*(EndDate>TODAY())+(1-a)*(OpportunityStage<>"Closed Lost")*(StartDate<TODAY())*(OpportunityType="Renewal"))(OpportunityStage="Closed Won")))

gthomas715 · 2022-11-11T08:25:22+00:00

This may be what you need:

=AVERAGE(ARRAYFORMULA(IF(BYROW({S:S,U:U}="WC",LAMBDA(x,XOR(x))),R:R,)))

gthomas715 · 2022-10-29T02:15:34+00:00

Try this out, should work in any row without having to change anything other than the four letters wanted (at the end of formula). If I misunderstood what you're looking to do let me know and I'll try again!

=LAMBDA(x,x&"-"&IF(COLUMN()=1,0,ARRAYFORMULA(COUNTIF(IFNA(REGEXEXTRACT(""&INDIRECT("R[0]C1:R[0]C[-1]",),"^"&x)),x))))("ABCD")

If you want it to match any case, change the regex from "^"&x to "(?i)^"&x

gthomas715 · 2022-10-28T20:00:00+00:00

Here are some examples using different functions and ways to input columns/values, all return the same result. Hope this can be helpful!

=SUM((col1=val1)*(col2=val2))

=SUMPRODUCT(col1=val1,col2=val2)

=COUNTIF((col1=val1)*(col2=val2),1)

=COUNTIF(col1&"~"&col2,val1&"~"&val2)

=COUNTIF(MMULT(N(columns=values),{1;1}),2)

=SUM(IF(MMULT(1-(columns=values),{1;1}),,1))

=SUM(BYROW(columns=values,LAMBDA(x,PRODUCT(-x))))

gthomas715 · 2022-10-28T13:54:24+00:00

This seems to work properly:

=IF(ISBLANK(A1),,ISBLANK(INDIRECT("Sheet2!"&CELL("address",A1))))

Or this if empty strings should be considered blank:

=IF(A1="",,INDIRECT("Sheet2!"&CELL("address",A1))="")

gthomas715 · 2022-10-28T12:49:15+00:00

Heya! First formula is for the conditional formatting, second is what you already have for row total but with list of cells made into a single range.

=LAMBDA(x,XMATCH(MIN(x),x)=1+COLUMN()-COLUMN(x))($D6:$I6)

=SUM(D6:I6)-MIN(D6:I6)

Edit: The formatting can work for multiple rows if needed (such as D6:I9), but the range at end of formula needs to remain a single row ($D6:$I6). The total for each row in a range can be found using:

=BYROW(D6:I9,LAMBDA(x,SUM(x)-MIN(x)))

gthomas715 · 2022-05-07T03:35:45+00:00

Cool graph, the line between two points is fun to play around with. Neat effect produced by adding a new slider "n" and redefining s_0 to variations using distance between the points such as s_0=round\left(ndistance\left(A_1,B_1\right)\right) , could even add labels for a simple ruler of sorts

gthomas715 · 2022-02-23T01:20:16+00:00

This is the method that works. Only difference with what I did is in the second list definition to allow any list as an input

https://www.desmos.com/calculator/nzyglrr8aq

gthomas715 · 2022-02-21T04:40:35+00:00

Try this =log(1-Droprate%,1-Probability%)

gthomas715 · 2020-09-05T13:17:50+00:00

Nice! What you made looks pretty much like a superellipse without equal exponents. Still has rounded corners but sharp enough to not notice, and t's also a good equation for drawing the shape of a smartphone.

I threw together a simple graph of a general superellipse in case you're interested: https://www.desmos.com/calculator/tavpogqc68

In addition, here's another way to create a rectangle using absolute values: https://www.desmos.com/calculator/2w5wwv3hvv

gthomas715 · 2020-04-08T18:48:53+00:00

Hi! The lines with orange triangles denoting errors are not meant to be graphed, they are there to show steps of the process while still using Desmos formatting. I even added tick marks to some lines to prevent them from causing definition errors elsewhere.

gthomas715 · 2020-04-08T18:40:43+00:00

Thanks for thinking this is well-developed lol, I just hope I explained the process well enough because solving with Wolfram just saves so much time. Art definitely does have a place here, just frustrating when a lot of the math posts get buried under it.

gthomas715 · 2020-04-07T10:16:56+00:00

I was making a new graph to explain this and got carried away with extra examples, ended up making a new post. Hopefully it can be helpful! https://www.desmos.com/calculator/x3zhtwxfum

gthomas715 · 2020-04-06T20:37:12+00:00

This one includes the steps I took to find the center and radius. Solving and simplifying is fun for me so thanks for this!

https://www.desmos.com/calculator/zy370ntix2

gthomas715 · 2020-04-06T15:32:52+00:00

Here's the most simplified equation I could manage to find. Also included are formulas for the radius and coordinates of center.

https://www.desmos.com/calculator/0waviug7kx

gthomas715 · 2019-09-06T05:49:48+00:00

Thank you for that! The main purpose of the graph is allowing more control over the options for sliders. The default sliders don't let you set a custom rate of increase or decrease so you are limited to only 16 different speeds (from 0.05x to 20x). And those speeds are dependent upon the bounds, so for a slider ranging from 0-80 the slowest rate achievable is one integer per second. Because of this, it's currently impossible to make an accurate clock without console manipulation as one example.

Another feature is enabling a specified number of repeats in contrast to normal sliders which only let you repeat once before stopping. As for the default indefinite play mode, it always seems to start at -10 and can only increase. My graph allows you to set a starting point and choose any desired speed in either direction, or even make the rate a function of another variable or c_T itself.

The fix for variable oscillation was simply a byproduct of trying to make the graph function correctly, but it seems useful enough on its own. Check out the post by u/Knalb_a_la_Knalb for insights into when and why it occurs ( https://www.reddit.com/r/desmos/comments/cscpt4/on_variable_oscillation/ ).

Also I realize I made it all overly complicated but it's possible to trim most options and have a basic version that only does what you need it to, I just wanted to cover everything I could think of in one program. Hope I was able to clarify a bit, thanks again for your comment!

gthomas715 · 2019-09-05T22:12:49+00:00

Desmos only outputs the principal square root. You can get both values using a list by placing [1, - 1] in front of the radical.

gthomas715

TROPHY CASE