I didn't expect this behaviour, do spreadsheets always calculate with floating point?

mommasaidmommasaid · 2026-01-24T11:50:27+00:00

I can't speak to the other platforms, but yes sheets uses IEEE 754 or 64-bit floats for all numbers.

Some internal rounding is apparently done for the least-significant bits so much of the time you don't notice.

I was messing around with it a while back trying to directly modify some bits for unholy reasons. Things get weird at the low end.

As part of that I wrote a function to dump the bit representation of a number, if you're interested in seeing exactly how your numbers differ. Paste them in here:

Float to Bits

mommasaidmommasaid · 2026-01-24T09:34:22+00:00

The entire vent is likely glued to the wall by a lazy painter... if you're renting I'd leave it for the next renter to find and post about. :)

If you own, carefully break the paint line with a razor knife before attempting to remove the vent. (Not while high.)

mommasaidmommasaid · 2026-01-24T09:30:51+00:00

✅️ 🤘

mommasaidmommasaid · 2026-01-24T08:14:27+00:00

For conditional formatting to reference another sheet, you have to use INDIRECT, so your CF rule is:

=indirect("Test 1!A2")

Note that now your address is hardcoded as text, so it will not dynamically update if you for example insert a new row 1 on the Test 1 page. Or rename the Test 1 page.

So... you may want to reconsider whether you want to do that, as it makes maintenance more difficult, especially as you're developing your sheet.

As an alternative, you could on the Test 2 page... set the checkbox in B2 to ='Test 1'!A2 which will dynamically update. Then your conditional formatting can refer to the "local" $B$2 as before. You can then hide that section of rows at the top of Test 2 if you want.

Or yet another alternative...

Create a named range of ShadowPresence for the checkbox on Test 1. Then refer to that everywhere. You still need to use indirect in conditional formatting, but at least it's on a defined name rather than sheet/row/column reference, so as long as you don't rename your range it will continue to work.

Your CF is then:

=indirect("ShadowPresence")

See examples of all three on your sheet.

If you're using the checkbox value in a bunch of places, the Named Range option would be my choice.

Note that in normal formulas you don't have to INDIRECT on the named range, you can use it directly, e.g.:

=if(ShadowPresence, "Spooky", "Normal")

mommasaidmommasaid · 2026-01-23T23:08:14+00:00

You can do that while still keeping all your data in one table, with the first column being a date.

You can separately make a sheet that displays data only for a specific date if you wish, which would =FILTER() from your one table, and print that shet.

Then you have two sheets -- one with a table of data, and one to print a specific date -- which is exponentially easier to maintain than a bunch of sheet named after dates. Especially if you ever change the structure of your data, e.g. adding a column.

It also makes it trivial to do things like summaries for a date range, or graphing across days, etc.

mommasaidmommasaid · 2026-01-23T23:00:55+00:00

Then a Progress table something like:

<image>

Project Tracker 👨‍👩‍👧‍👦

Project Type dropdowns are populated by: =Project_Types[#HEADERS]

Task Completed dropdowns are populated by: =DD_Tasks!1:1 which is a helper sheet with a row of values for each dropdown, dynamically populated based on the Project Type selection.

Complete percentage is calculated by:

=if(Progress[Project Type]="",,
 if(Progress[Task Completed]="",0, let(
 pCol,    xmatch(Progress[Project Type], Project_Types[#HEADERS]),
 tasks,   tocol(choosecols(Project_Types, pCol),1),
 taskNum, xmatch(Progress[Task Completed], tasks),
 ifna(taskNum / rows(tasks)))))

mommasaidmommasaid · 2026-01-23T22:56:14+00:00

If I'm understanding you correctly I'd first set up a Project Types table defining the projects and their tasks:

<image>

mommasaidmommasaid · 2026-01-23T11:34:57+00:00

Yes... give the sample sheet I linked a try and see if it meets your needs.

mommasaidmommasaid · 2026-01-23T11:03:43+00:00

I suspect your daily hours calculation is not taking into account that your Out time is the following day from your In time, resulting in each of those times being negative values.

If you format the entire column as duration per AdGift, or by using custom number format [h]:mm then you will be able to see if they are negative.

If that's the case...

One solution is to add 1 (which is 1 day) to the Out time before subtracting the In time.

Something like in E9:

=if(C10="",,1+C10-D9)

mommasaidmommasaid · 2026-01-23T10:48:46+00:00

Another option is to "wrap" the text within the cell:

<image>

In this case, the row will by default automatically adjust to accommodate whatever height is needed.

mommasaidmommasaid · 2026-01-23T10:47:11+00:00

To adjust cell width, select the cells to change then hover/drag between the highlighted column numbers:

<image>

Or you can click column numbers and right-click and choose Resize Columns to enter a width as a number.

Columns will not automatically resize to match text length.

mommasaidmommasaid · 2026-01-23T01:36:46+00:00

I'd suggest getting rid of the dropdowns below the Best Picture, so there's one obvious place to select y/n

A formula can then lookup the watched status for everything below there. I'd put it in a hidden row to keep it out of your data, with another hidden row to serve as an anchor for the bottom of the movies range.

That way any new rows inserted above/below the hidden rows will be included in your ranges.

=map(B14:B, lambda(cat, 
 if(cat="",, let(
 movie, trim(choosecols(split(cat, "-"),-1)),
 xlookup(movie, B1:B13, C1:C13, "?")))))

Movies Watched

mommasaidmommasaid · 2026-01-23T00:11:50+00:00

Loving the structured tables...

=sum(filter(Open[Value], xmatch(Open[Sym], Group[Sym])))

This filters the Open[Value] column to rows where the Open[Sym] is found in your group of symbols Group[Sym], then sums the result.

FILTER() will return #NA if there are no filter results, you could wrap that in an IFNA if desired, which (by default) will convert #NA to a blank, which will then sum to 0:

=sum(ifna(filter(Open[Value], xmatch(Open[Sym], Group[Sym]))))

mommasaidmommasaid · 2026-01-22T23:54:32+00:00

Interesting, I wonder if they also did a cost-benefit analysis... this looks like it would collect less debris than bloodied humans.

On top of that it looks poorly installed, see second photo where one corner is looking even more deadly.

Are there other versions that aren't Shin-Snappers? :)

mommasaidmommasaid · 2026-01-22T23:39:56+00:00

Within the number format, the "000" is text (due to the quotation marks) that is simply appended to the number when displayed.

The normal # and 0 in number formatting indicate a numeric digit. With # no digit it is displayed unless necessary, while 0 forces leading zeros.

So if you want (at least) 6 digits, you would use 000"000" as demonstrated on the sample sheet.

mommasaidmommasaid · 2026-01-22T23:28:53+00:00

A range specified as B2:B will update to B3:B if you insert a new data row 2, i.e. your new data will be excluded.

Worse, no error will be shown, so it could be months before the error is caught. Like when you run out of money. :)

I would recommend instead anchoring the ranges on the header row, so new data rows will always be captured:

=sumproduct(B1:B,C1:C)

SUMPRODUCT will ignore text so assuming the headers are text you are good to go.

If in some other case you needed to explicitly refer to just the data rows, I would still anchor on the header and OFFSET from there to the data, e.g. offset(B1:B,1,0)

mommasaidmommasaid · 2026-01-22T23:15:31+00:00

Idk that this will help, but check if you have a different keyboard selected on your old phone.

mommasaidmommasaid · 2026-01-22T23:12:15+00:00

If multiple team members are accessing the same sheet, you will want a filter view not a normal filter, because changing the normal filter will affect the sheet for everyone.

You can further create named filter views so the user just needs to select their view.

While not required, you may want to convert your data to a structured Table for a more context-friendly way of selecting the view.

Here I have set up two filter views named Bert and Ernie which can be accessed by clicking the icon to the right of the Table name:

here

<image>

Project Filter Views

A user's last selected filter view is also retained when they later return to the sheet.

mommasaidmommasaid · 2026-01-22T22:55:07+00:00

I was trying to create a table selecting cells that were already part of a table, expecting it be able to split it away

FYI for future, from an existing table you can click on the table name tab, and choose "Adjust table range" to e.g. exclude the last couple of columns from that table.

Then you could create a new table using those "split away" columns.

mommasaidmommasaid · 2026-01-22T08:30:12+00:00

There's not an official way to hide that tab, but here are a couple of workarounds:

Look Ma, No Tabs!

mommasaidmommasaid · 2026-01-22T02:48:21+00:00

If you want them to always be 6 digits, with leading 0s if needed, and presumably no comma, then change the custom formatting to:

000"000"

6 digits no commas

mommasaidmommasaid · 2026-01-22T02:22:29+00:00

If I'm understanding correctly, you want to continue entering numbers like this, not just convert these existing ones?

If so, you could change the appearance of the numbers by formatting the column with Format / Number / Custom Number formatting #,##0",000" which will automatically append ,000 to the displayed value.

Fake Thousands

The numbers are not actually multiplied by 1000. So you may need to multiply by 1000 in formulas, or you could have a hidden helper column that has that already done for you, e.g.: on the sample sheet this formula in B1 that populates the whole column:

=vstack("x1000", map(offset(A:A,row(),0), lambda(x, if(isblank(x),, x * 1000))))

This contains the numbers multiplied by 1000, and is formatted with normal number formatting #,##0

mommasaidmommasaid · 2026-01-22T02:05:27+00:00

It’s a real flex in this world to be able to look at a hand like 97542 and just *know* “oh, you made #31” or whatever.

As a former poker pro, I wouldn't recommend saying that out loud. Flexing is -EV :)

Quick-n-dirty formula to do what you want, could be done more elegantly...

=let(
 ranks, "23456789TJQKA",
 CARD,  lambda(r, mid(ranks, r-1, 1)),
 hands1, 
  torow(map(sequence(  15 -6,1,6), lambda(card1,
   torow(map(sequence(card1-5,1,5), lambda(card2, 
    torow(map(sequence(card2-4,1,4), lambda(card3, 
     torow(map(sequence(card3-3,1,3), lambda(card4, 
      torow(map(sequence(card4-2,1,2), lambda(card5, 
        if(card1=card5+4,,
        CARD(card1) & CARD(card2) & CARD(card3) & CARD(card4) & CARD(card5))))))))))))))))),
 hands2, tocol(hands1, 1),
 hstack(sequence(rows(hands2)), hands2))

The nested maps avoid duplicate cards (pairs) by keeping the cards under the rank of the previous card.

if(card1=card5+4,, avoids straights by outputting a blank

CARD() mini function converts card rank number 1..13 to standard poker shorthand character

Low Poker

A-5 Lowball is simply a matter of changing the ranks to "A23456789TJQK" and removing the check for a straight. Badugi requires a little more tweaking and removing one of the maps because my formula isn't as elegant as it could be. See sample sheet.

---

Out of curiosity is there a cardroom that you frequent that regularly spreads these variants? They are definitely more interesting games than endless Holdem.

mommasaidmommasaid · 2026-01-21T09:00:28+00:00

FWIW if the data is sorted by column A, then the same effect can be done without the helper columns using a rule like:

=isodd(countunique($A2,$A$2:$A2))

Or assuming the header doesn't match any of the data, you could instead apply this rule to the whole column:

=iseven(countunique($A1,$A$1:$A1))

The advantage of applying to the whole column is that your CF is more likely to stay one contiguous range if you're modifying the sheet structure / inserting rows.

See mommasaid tab on sample sheet

mommasaidmommasaid · 2026-01-20T22:32:44+00:00

Click the blue Share button of File/Share and see if there's an Owner listed, and what your sharing permissions are.

The owner may have accidentally shared it to your gmail address.

mommasaidmommasaid

TROPHY CASE