My longest ever Excel formula

Separate forum to permit easy exclusion when searching for serious information !
Manc33
Posts: 2217
Joined: 25 Apr 2015, 9:37pm

My longest ever Excel formula

Post by Manc33 »

I had to put this in a cell today :lol:

Code: Select all

=IF(X3=B2,K2,IF(X3=B3,K3,IF(X3=B4,K4,IF(X3=B5,K5,IF(X3=B6,K6,IF(X3=B7,K7,IF(X3=B8,K8,IF(X3=B9,K9,IF(X3=B10,K10,IF(X3=B11,K11,IF(X3=B12,K12,IF(X3=B13,K13,IF(X3=B14,K14,IF(X3=B15,K15,IF(X3=B16,K16,IF(X3=B17,K17,IF(X3=B18,K18,IF(X3=B19,K19,IF(X3=B20,K20,IF(X3=B21,K21))))))))))))))))))))
If anyone knows of a longer one (or can help me shorten this one haha) post it. :P
We'll always be together, together on electric bikes.
Jdsk
Posts: 24487
Joined: 5 Mar 2019, 5:42pm

Re: My longest ever Excel formula

Post by Jdsk »

Manc33 wrote: 4 Dec 2021, 9:00am I had to put this in a cell today
I hope that it serves an appropriately long sentence.

Jonathan
Manc33
Posts: 2217
Joined: 25 Apr 2015, 9:37pm

Re: My longest ever Excel formula

Post by Manc33 »

Hopefully it will lose a bit of weight but it's not easy.
We'll always be together, together on electric bikes.
rjb
Posts: 7183
Joined: 11 Jan 2007, 10:25am
Location: Somerset (originally 60/70's Plymouth)

Re: My longest ever Excel formula

Post by rjb »

Are you sure it's correct. It looks a bit iffy to me. :wink:
At the last count:- Peugeot 531 pro, Dawes Discovery Tandem, Dawes Kingpin X3, Raleigh 20 stowaway, 1965 Moulton deluxe, Falcon K2 MTB dropped bar tourer, Rudge Bi frame folder, Longstaff trike conversion on a Giant XTC 840 :D
User avatar
simonineaston
Posts: 7993
Joined: 9 May 2007, 1:06pm
Location: ...at a cricket ground

Re: My longest ever Excel formula

Post by simonineaston »

That's cheating - its just one great big ite statement... !! :wink:
but seriously: here
S
(on the look out for Armageddon, on board a Brompton nano & ever-changing Moultons)
Pebble
Posts: 1930
Joined: 7 Jun 2020, 11:59pm

Re: My longest ever Excel formula

Post by Pebble »

would of thought it would have been simpler wrote in VBA
User avatar
simonineaston
Posts: 7993
Joined: 9 May 2007, 1:06pm
Location: ...at a cricket ground

Re: My longest ever Excel formula

Post by simonineaston »

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)
axel_knutt
Posts: 2870
Joined: 11 Jan 2007, 12:20pm

Re: My longest ever Excel formula

Post by axel_knutt »

=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).
“I'm not upset that you lied to me, I'm upset that from now on I can't believe you.”
― Friedrich Nietzsche
User avatar
Audax67
Posts: 5999
Joined: 25 Aug 2011, 9:02am
Location: Alsace, France
Contact:

Re: My longest ever Excel formula

Post by Audax67 »

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.
Have we got time for another cuppa?
Pebble
Posts: 1930
Joined: 7 Jun 2020, 11:59pm

Re: My longest ever Excel formula

Post by Pebble »

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.
User avatar
Cowsham
Posts: 4951
Joined: 4 Nov 2019, 1:33pm

Re: My longest ever Excel formula

Post by Cowsham »

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).
Do you work in a hundreds an thousands factory?
I am here. Where are you?
Stradageek
Posts: 1652
Joined: 17 Jan 2011, 1:07pm

Re: My longest ever Excel formula

Post by Stradageek »

Aaaaaargh.... I retired to escape all of this :(

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
sjs
Posts: 1305
Joined: 24 Jan 2010, 10:08pm
Location: Hitchin

Re: My longest ever Excel formula

Post by sjs »

Manc33 wrote: 4 Dec 2021, 9:00am I had to put this in a cell today :lol:

Code: Select all

=IF(X3=B2,K2,IF(X3=B3,K3,IF(X3=B4,K4,IF(X3=B5,K5,IF(X3=B6,K6,IF(X3=B7,K7,IF(X3=B8,K8,IF(X3=B9,K9,IF(X3=B10,K10,IF(X3=B11,K11,IF(X3=B12,K12,IF(X3=B13,K13,IF(X3=B14,K14,IF(X3=B15,K15,IF(X3=B16,K16,IF(X3=B17,K17,IF(X3=B18,K18,IF(X3=B19,K19,IF(X3=B20,K20,IF(X3=B21,K21))))))))))))))))))))
If anyone knows of a longer one (or can help me shorten this one haha) post it. :P
Take a look at the VLOOKUP function. I think it might do what you want.
User avatar
simonineaston
Posts: 7993
Joined: 9 May 2007, 1:06pm
Location: ...at a cricket ground

Re: My longest ever Excel formula

Post by simonineaston »

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)
Manc33
Posts: 2217
Joined: 25 Apr 2015, 9:37pm

Re: My longest ever Excel formula

Post by Manc33 »

axel_knutt wrote: 4 Dec 2021, 11:21amWhat's your biggest spreadsheet...
Not as big as yours :lol: 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 :P

This does the exact same thing as the thing in the first post:

Code: Select all

=VLOOKUP($X$3,$B$2:$K$21,10,FALSE)
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.
Post Reply