🧵 View Thread
🧵 Thread (20 tweets)
I feel like many more people could benefit from developing awesome spreadsheet skills, but most people don't know what they're good for so I'm going to document some of the nice things I do in Google Sheets in case anyone wants to pick up some tips
Demo 1 I'm organising an event; I need a count of beds required per night guests will have stay for different number of nights & only some of them need a bed (the others bring their own tent) here's what I made: https://t.co/CBR1lqh1JV https://t.co/BZ8ImqzuM0
first: style choices make it nice & relaxing to look at: switch off gridlines [View > Show > Gridlines] and switch on zebra stripes [Format > Alternating Colours] use bold text & border lines to highlight the important bits
Column D has a nice friendly dropdown menu, where you can select between the two options "House" or "Tent" you can create dropdowns like this... open the data Validation menu [Data > Data Validation] and type in the options separated by commas https://t.co/hmctjQbGVw
the checkboxes in Columns F:K are another kind of data validation, with different settings https://t.co/akZy3CSdBb
you could manually check & uncheck those boxes but it is better to make an automated system that easily scale to handle many guests without a lot of manual data entry (error prone & boring) I do this with conditional logic...
here's the formula in G5, lemme explain: if G1 ("Aug 2") is greater than or equal to the arrival date B5, AND less than or equal to the departure date, the box should be checked https://t.co/hxcEKCjWvo
sorry long delay I met a nice cat it was important https://t.co/ITGgB8n8Uo
ok next, how to count the number of guests per night? in cell G7 I have the formula "=countif(G2:G6, true)" which means, check all the cells between G2 and G4 and count all the ones marked "true" (ie. count the ones that are checked) https://t.co/6vUAMHzz84
maybe you noticed in the first formula I used dollar signs, like "if(G$1 >= $B5)" $ means "don't increment this part of the cell reference when I copy the formula to the next cells" so a formula with $B5 will *not* become C5 when I drag to the right
next, how to count just the guests who need beds? I have another grid of true/false checkboxes here's the formula in cell N4. translation: if the guest is in the house (D4="House"), and they're staying tonight (G4 is true), this cell should be true https://t.co/cbPhK1Q6B6
@RichDecibels This is how I did it for a previous event! I like how you did a dropdown for tent/bed because it prevents typos. I'm not sure how to avoid a gantt chart approach if people are going to be changing rooms in between like we had https://t.co/e03M88nFNz
@RichDecibels I made a thing that allows you to compile spreadsheets into hardware so you can drive low latency or industrial or embedded processes with it. Feels like there should be some business in that but I've not found it.