True that.Sometimes it takes me ages to figure out exactly what I was trying to do on some of the formulae in my excel worksheets when I've not used them for a while.
The Fighting Cock is a forum for fans of Tottenham Hotspur Football Club. Here you can discuss Spurs latest matches, our squad, tactics and any transfer news surrounding the club. Registration gives you access to all our forums (including 'Off Topic' discussion) and removes most of the adverts (you can remove them all via an account upgrade). You're here now, you might as well...
True that.Sometimes it takes me ages to figure out exactly what I was trying to do on some of the formulae in my excel worksheets when I've not used them for a while.
In Spain they use a full stop where we use a comma and a comma where we use full stop.
Decimal separator - Wikipedia
en.wikipedia.org
Here is a fun excel formula that I have developed which has contributed to my psychosis.
=IF(AND(H43<$H$37,I43<$I$37,J43<$J$37),$G$37,IF(AND(H43<$H$38,I43<$I$38,J43<$J$38),$G$38,IF(AND(H43<$H$39,I43<$I$39,J43<$J$39),$G$39,IF(AND(H43<$H$40,I43<$I$40,J43<$J$40),$G$40))))
That formula combines with other, similar formulas to feed variables into the formula below.
=IF($H$32="Single",$H$44,K43&"T "&IF($H$33="Clear",,IF(OR($H$33="Gray",$H$33="Bronze"),$H$33,IF($H$33="Clear",,"Q"&$H$33)))&" / "&L43&" FBD"&" / "&IF($H$34="Clear",,IF(OR($H$34="Gray",$H$34="Bronze"),$H$34,IF($H$34="Clear",,"Q"&$H$34)))&" ")&K43&"T"
That formula generates a complete order string for an insulated glass lite which contains tempering information, Low-E choices, tint options, glass thicknesses, and spacer bar information to submit to a glass manufacturer.
To think I was proud of designing my own income and expenditure spreadsheets with formulae and everything.
I even coloured in the column and row headings
=IF(AND($B$5="Monoslope",$B$6>0,$B$6<7.5),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y7:Y8,'Fig 30.8-1 & 30.8-2'!$P$7:$P$8),IF(AND($B$5="Monoslope",$B$6>7.5,$B$6<15),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y8:Y9,'Fig 30.8-1 & 30.8-2'!$P$8:$P$9),IF(AND($B$5="Monoslope",$B$6>15,$B$6<30),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y9:Y10,'Fig 30.8-1 & 30.8-2'!$P$9:$P$10),IF(AND($B$5="Monoslope",$B$6>30,$B$6<45),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y10:Y11,'Fig 30.8-1 & 30.8-2'!$P$10:$P$11),IF(AND($B$5="Pitched",$B$6>0,$B$6<7.5),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y30:Y31,'Fig 30.8-1 & 30.8-2'!$P$30:$P$31),IF(AND($B$5="Pitched",$B$6>7.5,$B$6<15),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y31:Y32,'Fig 30.8-1 & 30.8-2'!$P$31:$P$32),IF(AND($B$5="Pitched",$B$6>15,$B$6<30),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y32:Y33,'Fig 30.8-1 & 30.8-2'!$P$32:$P$33),IF(AND($B$5="Pitched",$B$6>30,$B$6<45),FORECAST.LINEAR($B$6,'Fig 30.8-1 & 30.8-2'!Y33:Y34,'Fig 30.8-1 & 30.8-2'!$P$33:$P$34),"EM"))))))))
=IF(AND(Sheathing_Matl="Structural I", Framing_width=2,Blocked_Dia="Yes"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$18:$K$20,IF(S_nail=6,3,IF(S_nail=4,4,IF(S_nail=2.5,5,IF(S_nail=2,6,NA)))),FALSE),IF(AND(Sheathing_Matl="APA Rated Sheathing", Framing_width=2,Blocked_Dia="Yes"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$21:$K$27,IF(S_nail=6,3,IF(S_nail=4,4,IF(S_nail=2.5,5,IF(S_nail=2,6,NA)))),FALSE),IF(AND(Sheathing_Matl="Structural I", Framing_width=3,Blocked_Dia="Yes"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$36:$K$38,IF(S_nail=6,3,IF(S_nail=4,4,IF(S_nail=2.5,5,IF(S_nail=2,6,NA)))),FALSE),IF(AND(Sheathing_Matl="APA Rated Sheathing", Framing_width=3,Blocked_Dia="Yes"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$39:$K$45,IF(S_nail=6,3,IF(S_nail=4,4,IF(S_nail=2.5,5,IF(S_nail=2,6,NA)))),FALSE),IF(AND(Sheathing_Matl="Structural I", Framing_width=2,Blocked_Dia="No"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$18:$K$20,IF(Sheathing_Layout="Case 1",7,8),FALSE),IF(AND(Sheathing_Matl="APA Rated Sheathing", Framing_width=2,Blocked_Dia="No"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$21:$K$27,IF(Sheathing_Layout="Case 1",7,8),FALSE),IF(AND(Sheathing_Matl="Structural I", Framing_width=3,Blocked_Dia="No"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$36:$K$38,IF(Sheathing_Layout="Case 1",7,8),FALSE),IF(AND(Sheathing_Matl="APA Rated Sheathing", Framing_width=3,Blocked_Dia="No"),VLOOKUP(Sheathing_Thk,'Lookup Tables'!$D$39:$K$45,IF(Sheathing_Layout="Case 1",7,8),FALSE),2))))))))
First calculates the Cn variable in the equation for C&C wind loads for open structures for a particular wind zone, wind flow type, and tributary area. Second calculates the shear capacity of a specific range of wood diaphragms based on geometric conditions, materials used, and connection patterns.
I'm working on a spreadsheet to design reinforce concrete water storage tanks in my down time. For fun and shit.
I've started doing a lot more sql in recent years but in both instances (excel) I have been choosing to break things up into chunks and not monster formula so others can follow.
After trying to decipher people's formula and fixing things that are broken. I now have a "motto". It's not if we can do it but whether we should.
I've done windload calculations a couple of times, and no more. I tell people now that as a manufacturer, it's a conflict of interest and a problem of liability for me to perform structural calculations of that nature.
I just lock my spreadsheets and tell people to fuck off.I've started doing a lot more sql in recent years but in both instances (excel) I have been choosing to break things up into chunks and not monster formula so others can follow.
After trying to decipher people's formula and fixing things that are broken. I now have a "motto". It's not if we can do it but whether we should.
I can't code for shit. I still have PTSD from a semester of MatLab. My brain just won't get it. I can work with spreadsheets and databases all day....but there's just some sort of blockage I just can't get through with coding.I feel for you bro. I code in several languages (and have written a fair few excel formulae long ago), but they are dogshit to read and write compared to even the worst languages I've used (PHP, VB). SQL isn't much better, but I'd much rather use that than Excel to extract data. You can use C# functions with MS SQL server, too, so it can be quite readable/maintainable. I have a feeling you can write functions for Excel the same way, but I'd guess they look like dogshit again once you start dealing with $h444 etc...
And it drives me insane.
You would think that a few hundred years of world domination first by Brits and then by Americans would have straightened everyone else out on this issue by now.
I think I know why.I can't code for shit. I still have PTSD from a semester of MatLab. My brain just won't get it. I can work with spreadsheets and databases all day....but there's just some sort of blockage I just can't get through with coding.
Small-to-big, obviously. Most people know what month it is and you don't need to start with the year.Brits and Americans can't even agree on today's date;
04/03/21 or 03/04/21?
... I wonder if the American way is a holdover of the frontier?Small-to-big, obviously. Most people know what month it is and you don't need to start with the year.
US format is basically saying "We mostly speak about events that are going to happen or happened some time ago. We'll let you know the month first, so we can zero in on the approximate area, then nail it down to the day before adding a year for the avoidance of doubt."
UK format is saying "Look, we both know what month it is. I'll start with the day and the other stuff is for clarity if required."
But, as a data guy, I love the yyyyMMddHHmmss format. Sort yourself out!