well, it might be, if the op is happy to write code. One of the advantages of Excel - a revelation when it first emerged! was that you didn't have to use code. You could simply build straightforward formulae. Indeed, each formula came bundled up with its own wizard. Similarly, Access had a macro builder, specifically so that you could get results without having to know how to use code. Thank-goodness! It meant ordinary office workers could create an app that worked for them, hence the title, Microsoft Office. Years ago, I was an advocate of the even more plain-vanilla Microsoft Works, simply because it did 95% of what most people needed, at waaaaay less cost & complexity than Office.
S
(on the look out for Armageddon, on board a Brompton nano & ever-changing Moultons)
What's your biggest spreadsheet, this comes from my diet spreadsheet which is 151 columns by 254 rows (38,354 cells), which in turn sources reference data from the USDA database, which is 43 columns by 8692 rows (373,756 cells).
“I'm not upset that you lied to me, I'm upset that from now on I can't believe you.” ― Friedrich Nietzsche
simonineaston wrote: ↑4 Dec 2021, 10:56am
well, it might be, if the op is happy to write code. One of the advantages of Excel - a revelation when it first emerged! was that you didn't have to use code. You could simply build straightforward formulae. Indeed, each formula came bundled up with its own wizard...
Aye, which is why 100% of the spreadsheets produced by one of my clients in the 90s had errors in them before I wrote some decent software for him. The subject matter was, amongst other things, radiation sampling at Chernobyl.
simonineaston wrote: ↑4 Dec 2021, 10:56am
well, it might be, if the op is happy to write code. One of the advantages of Excel - a revelation when it first emerged! was that you didn't have to use code. You could simply build straightforward formulae. Indeed, each formula came bundled up with its own wizard. Similarly, Access had a macro builder, specifically so that you could get results without having to know how to use code. Thank-goodness! It meant ordinary office workers could create an app that worked for them, hence the title, Microsoft Office. Years ago, I was an advocate of the even more plain-vanilla Microsoft Works, simply because it did 95% of what most people needed, at waaaaay less cost & complexity than Office.
the code you are using in the cell is far more confusing and liable to mistakes than the code you would use in a macro.
axel_knutt wrote: ↑4 Dec 2021, 11:21am
=IF(AND(VLOOKUP(Unit_energy/Dilute,tb_cal,5,1)+VLOOKUP(Unit_saturates/Dilute,tb_fat,4,1)+VLOOKUP(Unit_sugar/Dilute,tb_sgr,3,1)+VLOOKUP(Unit_salt/Dilute,tb_slt,2,1)>=11,VLOOKUP((Fruit_content+Veg_content+Nut_content)/Dilute,tb_fvn,2,1)<5),VLOOKUP(Unit_energy/Dilute,tb_cal,5,1)+VLOOKUP(Unit_saturates/Dilute,tb_fat,4,1)+VLOOKUP(Unit_sugar/Dilute,tb_sgr,3,1)+VLOOKUP(Unit_salt/Dilute,tb_slt,2,1)-VLOOKUP((Fruit_content+Veg_content+Nut_content)/Dilute,tb_fvn,2,1)-VLOOKUP(Unit_fibre/Dilute,tb_fib,3,1),VLOOKUP(Unit_energy/Dilute,tb_cal,5,1)+VLOOKUP(Unit_saturates/Dilute,tb_fat,4,1)+VLOOKUP(Unit_sugar/Dilute,tb_sgr,3,1)+VLOOKUP(Unit_salt/Dilute,tb_slt,2,1)-VLOOKUP((Fruit_content+Veg_content+Nut_content)/Dilute,tb_fvn,2,1)-VLOOKUP(Unit_fibre/Dilute,tb_fib,3,1)-VLOOKUP(Unit_protein/Dilute,tb_prt,2,1))
What's your biggest spreadsheet, this comes from my diet spreadsheet which is 151 columns by 254 rows (38,354 cells), which in turn sources reference data from the USDA database, which is 43 columns by 8692 rows (373,756 cells).
My biggest issue was struggling to eliminate all the errors in a formula then having to watch a bright young graduate, with all the mathematical aptitude that I lack, do the same thing in seconds.. Ahh memories
The genius of Microsoft's Office was that it made reasonable computing software, in the form of word-processing, spreadsheet and database, available to folks who weren't IT professionals. The measure of Microsoft's success is of course, well known, in that the company has been colossolly successful and rich, with Office as one of their core products.
So, the key difference between the old and the new, was the escape of IT from its internment in the dusty fusty specialist 'EDP department' into the brand new freedom of the pc perched on everyone's desk - once that genie was out of the bottle anybody could knock up app.s - with all the varying degress of success that comes as part of that sort of freedom !!
S
(on the look out for Armageddon, on board a Brompton nano & ever-changing Moultons)
axel_knutt wrote: ↑4 Dec 2021, 11:21amWhat's your biggest spreadsheet...
Not as big as yours My biggest was probably the ones I was trying to do for cryptocurrencies but I can't even remember what I was trying to work out and I do remember it not making one iota of difference (past results had no bearing or "edge" on future outcomes).
My long one got fixed by the way
This does the exact same thing as the thing in the first post:
It wasn't me that fixed it and I'm still not sure what that "10" is for, probably the amount of columns. Who knows what happens if a vaule in a random cell in that range like F12 is equal to something in the K column... luckily the way I have it all setup, the K column figures are in the hundreds or thousands whereas the main body of the table (C to J) is single and double digits.
Now I'm trying to do "if this is greater than that and the other is greater than another, display this text" but that's just a case of nesting about 4 ifs, not 20.
We'll always be together, together on electric bikes.