Can any one tell me if the choose function is a fat or light formula?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
(if I'm saying function but should say formula please understand i mean the "choose"eg. "=Choose(A1,1,2,3,4,5,6) )
I was recently helped with a problem and the solution used the choose function.
on investigating it seams this is a very useful function and one i could find many many uses for.
My worry is i don't know how much resources the function uses so i don't know if I'm better off continuing to use my current style most of the time or move over to the choose function that would be very helpful and easier to set up.

So my question is does anyone know if Choose is classed as a light function or heavy function?

If that terminology is not a familiar one let me try explain

as I understand it formulas like "Sum", "If", "Sumifs" etc are quite light, you can use then hundreds of times even thousands of time in a document with little effect on the speed of the document.
I know this to be true as i use them all day every day,

however I have also found some formulas just clog up the document if you use them too often, like Array formulas, Indirect, etc. so whilst they are useful, i tend to try avoid then in areas where im repeating the formula down a column for example.

so my question is does anyone know if choose is relatively light or heavy? should i avoid it if the formula is for every cell in a column of 1000rows for example?

I know this might not be an exact answer, i understand excel and formulas quite well but before i go build a new document for myself and end up having to redo it because the choose function is clogging everything up i just thought id ask if anyone knows?

Thanks

Tony
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I didn't read all above (too much text) but, imho, CHOOSE() is like multi IF()
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks sandy, but not reading the request before answering is a bit like answering the question you think someone is going to ask "No Help Whatsoever!"

Right now i feel like I just asked my nan if she saw the news on BBC1 and got "yes BBC1 has news!"

Perhaps someone who can be bothered to read my post would also like to comment with an answer that has something to do with the question as I could really do with some advice on this?:mad:

Tony
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
did you read this before post?
or
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Every function has it's use, but I believe there are more efficient formulas instead of CHOOSE
I wouldn't avoid its use, but Possibly Index/Match or VLOOKUP or LOOKUP might be better
Have a read here
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Sandy,
again, thank you for trying to help but you don't understand what my question is about!
I'm not asking about how to use the choose function, or how it works, I have already looked at this and have a good understanding of when i can use it,
if you bother reading my post you will better understand what I'm asking and might be able to answer my question not some other one.
again thank you for spending the time but maybe try taking a minute or two to read what I'm asking in full before trying to help as you are just wasting your time.
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you Michael, so i will only use it when its in a one off situation for now as that sound like the best idea, thanks very much for your help.
Thanks
Tony
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Further to this...here is a performance test between VLOOKUP(CHOOSE and InDEX/MATCH with the VLOOKUP component not being the casue of the slow speed.Read about halfway down the page in RED text
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,197
Messages
5,623,308
Members
415,965
Latest member
Sixaside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top