[Solved] Help understanding a formula

Discuss the spreadsheet application

[Solved] Help understanding a formula

Postby BTFT66 » Fri Jul 17, 2020 12:15 am

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 -
Last edited by Hagar Delest on Fri Jul 17, 2020 7:48 am, edited 1 time in total.
Reason: tagged solved.
OpenOffice Version 4.1.7 (Mac) IOS Catalina 10.15.1
BTFT66
 
Posts: 9
Joined: Tue Dec 17, 2019 3:58 pm

Re: Help understanding a formula

Postby Lupp » Fri Jul 17, 2020 12:25 am

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.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2999
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Help understanding a formula

Postby BTFT66 » Fri Jul 17, 2020 12:36 am

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
BTFT66
 
Posts: 9
Joined: Tue Dec 17, 2019 3:58 pm

Re: Help understanding a formula

Postby Lupp » Fri Jul 17, 2020 12:41 am

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?
Yes. (Quotation slightly disambiguated.)
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?
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.
Last edited by Lupp on Fri Jul 17, 2020 12:44 am, edited 1 time in total.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2999
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Help understanding a formula

Postby BTFT66 » Fri Jul 17, 2020 12:43 am

Great. Thank you very much. You have been very helpful.
OpenOffice Version 4.1.7 (Mac) IOS Catalina 10.15.1
BTFT66
 
Posts: 9
Joined: Tue Dec 17, 2019 3:58 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot], mike.stirton and 8 guests