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 -
[Solved] Help understanding a formula
[Solved] Help understanding a formula
Last edited by Hagar Delest on Fri Jul 17, 2020 7:48 am, edited 1 time in total.
Reason: tagged solved.
Reason: tagged solved.
OpenOffice Version 4.1.7 (Mac) IOS Catalina 10.15.1
Re: Help understanding a formula
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 useThe selecting expression on the first parameter position of CHOOSE() in the original formula needs anyway to be autoconverted to a number.
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
=CHOOSE(1+IF(F8="";0;LEFT(F8;1));"";"asset";"payable";"equity";"income";"expense")
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Help understanding a formula
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?
OpenOffice Version 4.1.7 (Mac) IOS Catalina 10.15.1
Re: Help understanding a formula
Yes. (Quotation slightly disambiguated.)BTFT66 wrote:... In the CHOOSE portion of the formula LEFT(F8;1) would refer to the left most or first digit of the entry in F8?
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 wrote: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?
Last edited by Lupp on Fri Jul 17, 2020 12:44 am, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Help understanding a formula
Great. Thank you very much. You have been very helpful.
OpenOffice Version 4.1.7 (Mac) IOS Catalina 10.15.1