[Solved] Help understanding a formula

[Hagar Delest]

I am using a template that came with a formula that I do not understand. I know WHAT it is doing but don't understand HOW it does what it does. I want to tweak it and possibly create similar type formulas in other spreadsheets but am unable to do that since it is so foreign to me... at least parts of it are.

=IF(F8="";"";CHOOSE(LEFT(F8;1);"asset";"payable";"equity";"income";"expense"))

I understand that it is taking the account in F8 and is assigning it as an asset, payable, equity, income, or expense. How does it know? I don't understand the use of "",""; or the CHOOSE portion of the formula.

Would someone care to break it down and explain it to me? I would be very grateful.

Thank you
- Mark -

[Lupp]

Did you read the help about the CHOOSE() function?
Obviously cell F8 is expexted to either be (blank or containing the empty text) OR (to start with one of the digits or respective number) 1; 2; 3; 4 or 5. Stressing the CHOOSE() a bit more you could use

Code: Select all   Expand viewCollapse view

=CHOOSE(1+IF(F8="";0;LEFT(F8;1));"";"asset";"payable";"equity";"income";"expense")

The selecting expression on the first parameter position of CHOOSE() in the original formula needs anyway to be autoconverted to a number.

[BTFT66]

Thank you for your quick response. It is appreciated. In the CHOOSE portion of the formula LEFT would refer to the left most or first digit of the entry in F8? Supposing I wanted to change it so that it would choose based upon the first TWO digits (ie 2104 and 2504 could be two different responses) All of the 2100 numbers would be one result and all of the 2500 numbers would be a different result. Is that possible?

[Lupp]

... In the CHOOSE portion of the formula LEFT(F8;1) would refer to the left most or first digit of the entry in F8?

Yes. (Quotation slightly disambiguated.)

Supposing I wanted to change it so that it would choose based upon the first TWO digits (ie 2104 and 2504 could be two different responses) All of the 2100 numbers would be one result and all of the 2500 numbers would be a different result. Is that possible?

Basically yes - if 2100 is meant as 21xy and 2500 respectively. However, you need to exctract the first two digits by LEFT(F8;2) then, and to avoid errors you need to make sure that there actually are TWO digits, not just one. In addition CHOSE() is under the limitation to accept 30 choices at most.

[BTFT66]

Great. Thank you very much. You have been very helpful.