The Official Status Thread
-
@anotherusername It gives me a headache. In return, I am going to find a formula from the worst abuse of spreadsheeting I have yet perpetrated.
-
-
@hungrier I can't find my freaking character sheet. There was an array formula in there that I wrote after staring in silence at it for 10 minutes then once I started laughing I started typing, don't remember what's in there.
-
@anotherusername I'll pretty print it, even. That should help you out, right?
=IF( OR( ROW( ) < 3, ISBLANK( OFFSET( A760, -1, 0 ) ) ), IF( ROW( E760 ) <> ROW( $E$760 ), IF( AND( OFFSET( F760, -1, 0 ) <> "", ROW( ) > 2 ), OFFSET( F760, -1, 0 ) & " | ", "" ) & TRIM( LEFT( IF( ROW( ) < 3, OFFSET( A760, -1, 0 ), A760 ), FIND( "(", IF( ROW( ) < 3, OFFSET( A760, -1, 0 ), A760 ) & "(" ) - 1 ) ), OFFSET( F760, -1, 0 ) ), IF( E760 <> "", IF( E760 <> 0, OFFSET( F760, -1, 0 ) & " " & IF( E760 = INT( E760 ), TEXT( E760, "0" ), TEXT( E760, ".00" ) ), LEFT( OFFSET( F760, -1, 0 ), MAX( 0, FIND( CHAR( 160 ), SUBSTITUTE( " | " & OFFSET( F760, -1, 0 ), " | ", CHAR( 160 ), MAX( 1, LEN( " | " & OFFSET( F760, -1, 0 ) ) - LEN( SUBSTITUTE( " | " & OFFSET( F760, -1, 0 ), "|", "" ) ) ) ) & CHAR( 160 ) ) - 4 ) ) ), OFFSET( F760, -1, 0 ) ) )
Somehow, I think that actually made it worse. But now if you shrink it down and turn it sideways, it makes mountain ranges!
-
@anotherusername I actually threw this into Excel so I could use its color-coding of nested calculations and I'm trying to parse this out as I'm reading it and it hurts...
If (row is less than 3, OR the cell in column A in the row above the current one is blank):
-If (the row number of the current cell is not equal to the row number of an absolute reference):
--If the cell in column F in the row above the current one is blank AND the row number is greater than 2:
---Then set the value to the value of the cell in column F in the row above the current one plus the string " | "
---Else set the value to blank
--Concatenate the result of the previous nest with a trim of the leftmost X number of characters of:
---If the row number is less than 3, use cell in column A in the row above the current one, otherwise use the cell in column A of the current row, where X is determined by finding a left parenthesis within the string of:
----If the row number is less than 3, use cell in column A in the row above the current one, otherwise use the cell in column A of the current row; then concatenate a left parenthesis onto the string and move to the character before it
And that's about where I got to before my eyes started crossing and being unable to properly distinguish the colors because apparently the colors Excel uses aren't distinguishable enough when they're overlapping :/
-
@e4tmyl33t Do you have... a headache?
It is a formula for causing a headache in the reader. Quod Erat Demonstrandum.
-
@e4tmyl33t said in The Official Status Thread:
If (row is less than 3, OR the cell in column A in the row above the current one is blank):
-If (the row number of the current cell is not equal to the row number of an absolute reference):You're making a really valiant effort, so I'll give you a few more hints:
Since the formula is in the cells in the range F2:F760, the "row less than 3" (or "greater than 2") and "row number of an absolute reference" (which is $F$760) correspond to whether the cell is (or isn't, in the "greater than" or "less than" case) the first or last cell in that range, respectively. Also note that the formula isn't in F1, so it needs to be special-cased for row 2: if it's in F2, it grabs what's in A1 instead of A2 (I feel like this particular detail is too big of a hint, but meh).
The absolute reference $F$760 will change automatically if rows are inserted, so that it remains the last cell in the range. And the OFFSET formulas, which have a row offset of -1, are all pivoted on the current row, so regardless of rows being inserted or deleted within the range, the offset will always refer to the cell in the row directly above it.
The "concatenate a left parentheses" is actually a FIND for that left parenthesis, and if there's no left parenthesis in the string, I want it to "find" the end of the string. Hence the concatenation. It then takes the LEFT of that much, and does a TRIM just in case there's a trailing space (which would've been the character immediately preceding the parenthesis).
Other than that, the formula will depend on what is in columns A and E, in addition to the cell directly above it. Try putting some things in column A, and see what the formula does... then, try also putting some things in column E.
-
@anotherusername said in The Official Status Thread:
Anyone who can tell me what it does will win a .
Is it a financial balance sheet, or possibly a Bill of Materials?
-
@Tsaukpaetra It's part of the former, but that's what it is, not what it does.
-
@anotherusername said in The Official Status Thread:
@Tsaukpaetra It's part of the former, but that's what it is, not what it does.
Oh. Well, I'm not quite bored yet to parse out, if you're looking for a step-by-step description like @e4tmyl33t's doing...
-
Status: Um....
Let's get started!
-
@anotherusername Sorry, took a break to leave the office and eat dinner. Now that I'm on a machine with better monitors, back to trying to parse this...
I did walk through the Excel evaluator and played around with having various bits in the cells around it and it appears that this LOOKS like a really contrived way of taking the contents of the cell above it and concatenating the contents of the cell to its left at the end of it provided that there's an actual number in the cell to its left
So I think I'm now in the "If the original IF statement is false" bit, meaning that it's on "if the row is greater than 2 and the cell in A in the row above this isn't blank"...
If E760 isn't blank, then:
-
If E760 is not equal to 0, take the value from the cell above this one, add a space, then concatenate the following:
- If E760 is a full integer, return that as a standard text format. If it's not a full integer, return it in two-places-decimal format
-
If E760 is equal to 0:
-
Take the leftmost set of X characters from the cell above this one, with X determined by the biggest number of characters from the following list:
-
0
-
Finding the character position 4 characters before the first non-breaking space in a sequence of characters that ends up as : " | " (two spaces, a pipe, and two more spaces) plus the characters in the cell immediately above this one, where one specific instance of " | " is replaced by a non-breaking space, with that instance determined by the biggest position number of:
-
1
-
The length of the string " | " (two spaces, a pipe, and two more spaces) plus the characters in the cell immediately above this one, minus the length of that string if " | " (one space, a pipe, and one space) in that string was replaced by a blank space, plus a non-breaking space...
-
-
-
And now my head hurts again.
-
-
@e4tmyl33t
Going through another method, testing results:Test 1:
- Inputs:
- F759 contents: 123
- E760 contents: 456
- F760 Result: 123 456
Test 2:
- Inputs:
- F759 contents: Wibbly wobbly
- E760 contents: 456
- F760 Result: Wibbly wobbly 456
Test 3:
- Inputs:
- F759 contents: Wibbly wobbly
- E760 contents: 0
- F760 result: blank
Test 4:
- Inputs:
- F759 contents: Wibbly wobbly |
- E760 contents: 0
- F760 Results: Wibbly wobbly
Test 5:
- Inputs:
- F759 contents: Wibbly wobbly |
- E760 contents: 456
- F760 results: Wibbly wobbly | 456
Test 6:
- Inputs:
- F759 contents: Wibbly wobbly |
- E760 contents: blank
- F760 results: Wibbly wobbly |
- Inputs:
-
@e4tmyl33t said in The Official Status Thread:
I did walk through the Excel evaluator and played around with having various bits in the cells around it and it appears that this LOOKS like a really contrived way of taking the contents of the cell above it and concatenating the contents of the cell to its left at the end of it provided that there's an actual number in the cell to its left
It's grabbing the cell in column A. And that's the simple part, really!
Also, I'll give you another hint, in that the very first copy of the formula (F2) grabs the contents of A1. So you might want to fill it up to F2, and look there.
-
@e4tmyl33t Huh.
Ok, so what it SEEMS to do is concatenate together whatever's in A1 and then the successive values in all cells column E into one long string as long as there are values in column A. If you run out of values in column A, it just starts tacking on spaced pipes onto the end of what it's been concatenating.
-
@e4tmyl33t said in The Official Status Thread:
The length of the string " | " (two spaces, a pipe, and two more spaces) plus the characters in the cell immediately above this one, minus the length of that string if " | " (one space, a pipe, and one space) in that string was replaced by a blank space, plus a non-breaking space...
This is a trick. I'll let you figure out what it's for.
Also, I think you miscounted spaces.
-
@anotherusername said in The Official Status Thread:
@e4tmyl33t said in The Official Status Thread:
The length of the string " | " (two spaces, a pipe, and two more spaces) plus the characters in the cell immediately above this one, minus the length of that string if " | " (one space, a pipe, and one space) in that string was replaced by a blank space, plus a non-breaking space...
This is a trick. I'll let you figure out what it's for.
Also, I think you miscounted spaces.
It's also possible it's an artifact of copy/pasting that formula from the forum...
-
@e4tmyl33t Hm. I guess I should reveal that you're not supposed to put anything in column E, unless there also be something in column A in that row.
-
Status: The Status Thread has been appropriated for Reverse-Coding Challenge, it seems...
-
@e4tmyl33t said in The Official Status Thread:
@anotherusername said in The Official Status Thread:
@e4tmyl33t said in The Official Status Thread:
The length of the string " | " (two spaces, a pipe, and two more spaces) plus the characters in the cell immediately above this one, minus the length of that string if " | " (one space, a pipe, and one space) in that string was replaced by a blank space, plus a non-breaking space...
This is a trick. I'll let you figure out what it's for.
Also, I think you miscounted spaces.
It's also possible it's an artifact of copy/pasting that formula from the forum...
It's correct in my post... the string that's left concatenated to the first string is just a pipe with one space on either side,
" | "
, and then there's that same string but if all the pipe characters,"|"
, are replaced with an empty string,""
.So there's the same string twice, but the second time it has all of the pipe characters removed. And the difference in their lengths will be... what?
-
-
@e4tmyl33t but the first string might have pipe characters in it already, before
" | "
is concatenated onto the left side. And all of the pipe characters are removed.In hindsight, rather than
MAX(1,LEN(" | "&OFFSET(F760,-1,0))-LEN(SUBSTITUTE(" | "&OFFSET(F760,-1,0),"|","")))
I should have written it more simply, as
1+LEN(OFFSET(F760,-1,0))-LEN(SUBSTITUTE(OFFSET(F760,-1,0),"|",""))
I did need to concatenate that
" | "
elsewhere, though, so that's where it came from.
-
@anotherusername Whatever you're doing, you're doing it wrong.
-
@Gribnit It works, though.
-
@anotherusername In that case, see if you can make it more convoluted. Or are you up against the formular limit?
-
@anotherusername So the difference is just the number of pipes in the string...
I keep playing around with this and I still cannot fathom what the deus it's doing. It's concatenating together whatever is in the first thing in column A it sees, then individual values in column E that aren't 0s in rows below that (but not from the row it first sees the thing in column A in), until you leave both A and E in a row blank, at which point it seems to reset itself, until you hit the end of all those values, at which point it starts making a slope of pipes (which presumably wouldn't happen in whatever this actually is, because you wouldn't have a billion dead rows at the bottom of it)
I give up. This is clearly either madness or brilliance in a field I am unfamiliar with, and I cannot tell which.
-
@e4tmyl33t said in The Official Status Thread:
So the difference is just the number of pipes in the string...
Right. So that's the number of pipes (plus one), which I'll call n. And then it replaces the nth occurrence of
" | "
withCHAR(160)
(with" | "
concatenated onto the left side, which corresponds to the "plus one"). So it replaces the last pipe (and the spaces on either side) withCHAR(160)
. And then it finds the location of that, and it takes theLEFT
of the whole thing, up to that. So it's taking the left substring up to, but not including, the last occurrence of" | "
, or a zero-length string if" | "
doesn't occur anywhere in it.Also the string that it's taking the
LEFT
doesn't have" | "
concatenated onto the left side, so there's a-4
in the formula instead of a-1
.So that part of it removes the last thing from the cell above. And it does this if the cell to the left is zero.
-
@e4tmyl33t it basically does this:
Note how it adds "Again", but then when its subtotal in column E is 0, it removes it again. It's aggregating the non-zero subtotals. The last value is the important one... all the ones above it are intermediate steps. Their contents are all hidden in the real spreadsheet, so column F basically just looks like an empty column except that the totals row grabs its last value (that totals row is actually on a different sheet, but let's not get too far into the weeds).
Also, I've just now realized that there's a bug which means it won't grab a subtotal from row 2, if there is one on row 2, because it's in the first part of the
IF
statement. Maybe I'll fix it, but I'm not planning on running up against that case...
-
Status: I used to procrastinate excessively by reading TDWTF. Now I procrastinate excessively by reading WTDWTF.
Meanwhile, various personal tasks I should attend to go mostly undone...
-
@kazitor oh shit I have not visited the main site in forever. Need to catch up. Do we still have the rpeside t s daughter around?
-
@stillwater As a recurring theme? No.
-
The edit menu does not come up on mobile and I can't even correct a typo. It's president.
-
@stillwater said in The Official Status Thread:
The edit menu does not come up on mobile and I can't even correct a typo. It's president.
I noticed that a while back. I blame WTDWTF customizations, but I forgot how to disable them...
-
Status: alright, back to this stupid left wing
[further context intentionally excluded]
-
@kazitor said in The Official Status Thread:
Status: alright, back to this stupid left wing
[further context intentionally excluded]Just use some tape.
-
@Tsaukpaetra I'd make a joke about reflectivity, but as I'm omitting the relevant details nobody would get it.
-
-
@stillwater there's a main site?
-
@pie_flavor said in The Official Status Thread:
@stillwater there's a main site?
It's not incredibly mobile friendly, but features no pagination, so no infiniscroll to get in your personal way.
-
But it only works if you open it in a new tab.
-
@kazitor said in The Official Status Thread:
But it only works if you open it in a new tab.
Which is really weird...
-
@Tsaukpaetra SPAs and rewriting basic browser behaviour working normally.
-
@pie_flavor I was talking about www.thedailywtf.com
-
-
Status: I could try just restarting the services, it probably can't get any worse
Spoiler: It got worse.
-
@GÄ…ska said in The Official Status Thread:
Status: Back to work after two weeks of holidaying. First thing I do is partially revert colleague's commit because he didn't notice we've already had a function for what he wrote.
Stuck in review. Apparently, my teammates value minor readability improvements more than not crashing in horrible ways.
-
@Cursorkeys said in The Official Status Thread:
Status: I could try just restarting the services, it probably can't get any worse
Spoiler: It got worse.
When you
Get-ExchangeCertificate <thumbprint> | New-ExchangeCertificate | Enable-ExchangeCertificate -services pop,smtp
for the love of God don't selectNO
when it asks you if you're sure you want to amend the service assignments. What it actually means is that it's already done the certificate shuffle and telling it no at this point will just bugger things up.I think email is now working again.
-
@pie_flavor said in The Official Status Thread:
@ben_lubar said in The Official Status Thread:
@pie_flavor said in The Official Status Thread:
@anonymous234 said in The Official Status Thread:
I vehemently refuse to call them "methods". They're functions dammit. Why does it matter if it's in a class or not?
I guess it's because of the dead meaning of function as a mapping from inputs to outputs. But then it might be more correct to just start calling everything methods?
A method is a function with the concept of
this
. It's easy.More specifically, a method is a function where the first parameter goes before the function name.
struct Foo; impl Foo { fn bar(&self) {} } fn main() { let foo = Foo; foo.bar(); Foo::bar(foo); }
This is a method declaration (the type of
self
is lexically before the method name) but not a method call (the method is accessed as a static function).
-
I took a screenshot of a portion of a wallpaper I found on Google images, because reasons:
The image got automatically synced into Google photos. So I look at the it in the Google photos web interface and... holy shit!
It found the original location!
-
I was about to say EXIF, but
@anonymous234 said in The Official Status Thread:
screenshot of a portion of a wallpaper
Yikes.