nerd stickers

my biggest boi to date

=IFS(AND(ISBLANK($B$3),ISBLANK($B$6)),“enter balance, date”,ISBLANK($B$3),“enter balance”,ISBLANK($B$6),“enter req date”,
COUNTA($B$3:$B$6)=4,(((FLOOR((($B$6-44926)-($B$5-44926))-4,14)/7)*B$9)+$B$3+(((($B$5-44898)-FLOOR($B$5-44898,14))-1)/7)*A$9))

Hey my guys imma let you finish but convert those fucked up hardcoded cell refs to tables with names so you can change your data shape without having to fuck with the whole goddamn thing.

2 Likes

fwiw the shape of this would be extremely unlikely to change. it’s a pto calculator that would be embedded on adp since our pto accrues differently now that we have 4 day work week

edit- admittedly my table game is p weak

1 Like

So is mine because my data management folks mostly did that so I never had much of a chance, but my Matlab days I worked with some professional programmers who did their best to inculcate good habits.

It pains me to say this, but I liked learning R. I don’t want a job where I have to use it, but it was fun while it lasted

1 Like

That sounds cool but not needed for my formula — it’s a tool with a handful of entry cells that category managers hand key values into to get a calculated result. If I were better it would use SQL commands to retrieve data but I don’t know how to do that

1 Like

My status as Lord of Boring Tarck Conversations™️ is reaffirmed

2 Likes

And this thread is your royal hall.

2 Likes

all about INDIRECT() here

1 Like

Love indirect.

Also, if you must do it in the (spread)sheets, convert to a table and use structured references

1 Like

if you’re using INDIRECT you’ve done fucked up something else real good in your spreadsheets

I had a bunch of spreadsheets at Central that had catapult sql queries built into them that would pull sales data and then run it through a bizarre series of overly complicated excel formulas. You would love them. You should hit them up for copies.

using it to take a propellor spec file in from the aero engineer and generate a ~3000 line CSV file that I can import into CAD as a point cloud

It’s great for pointing at the data in this tab or that tab based on the text I type into a tab.

It’s also useful in conditional formatting/validation by formula to get dynamic ranges

1 Like

They’re here. Tell me where to send them.


4 Likes