[Solved] Can an IF statement return a value based on......?

Discuss the spreadsheet application

[Solved] Can an IF statement return a value based on......?

Postby smartcooky » Wed Apr 07, 2021 9:28 am

I am currently using a very old program (MS Works) to calculate tax rates for employees. MS Works has become increasingly unreliable on Windows 10 - It runs with a fair amount of trickery and workarounds, and I would like to move on to Office Calc. However, I am having difficulty understanding wow "IF" statements function in Office Calc.. so I will explain what I am doing...

This is the IF statement I use in works operates as IF, THEN, ELSE (cell M2 contains the gross weekly wage, cell M3 (Taxrate) contains this IF statement

=IF((INT(M2)*52<14000),0.1189,(((INT(M2)*52)-14001)*0.1889+(14000*0.1189))/(INT(M2)*52))

In Works, the statement operates as follows

a. If the integer value in M2 multiplied by 52 is less than 14,000 then the value in M3 will be 0.1189
b. If a. is false, subtract 14001 from that value, then multiply the result by 0.1889 then add the result of 14,000 * 0.1189 and divide everything by the value in M2 multiplied by 52


Here are some results

If M2 = 150 then M3 = 0.1189
If M2 = 200 then M3 = 0.1189
If M2 = 250 then M3 = 0.1189
If M2 = 300 then M3 = 0.1261
If M2 = 350 then M3 = 0.1350
If M2 = 400 then M3 = 0.1418

As you can see, the tax rate does not climb above 0.1189 until the value of M2 x 52 exceed 14,000 (which is 269.99)


It operates perfectly in MS Works, but I cannot figure out how to get this to function in Open Office Calc. Any help would be appreciated.
Last edited by smartcooky on Thu Apr 08, 2021 6:36 am, edited 1 time in total.
Open Office 4.1.7 on Windows 10
smartcooky
 
Posts: 3
Joined: Wed Apr 07, 2021 8:46 am

Re: Can an IF statement return a value based on value in a c

Postby robleyd » Wed Apr 07, 2021 11:25 am

The formula as written above will return Error 508 in AOO, as the argument separator should be a semi-colon (;). You may have forgotten to mention that you get this error?

If the integer value in M2 multiplied by 52 is less than 14,000 then the value in M3 will be 0.1189

the tax rate does not climb above 0.1189 until the value of M2 x 52 exceed 14,000 (which is 269.99)


This is exactly the behavior I see using your formula, with commas replaced by semi-colons.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3831
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Can an IF statement return a value based on value in a c

Postby MrProgrammer » Wed Apr 07, 2021 7:25 pm

Hi, and welcome to the forum.

smartcooky wrote:I am having difficulty understanding how "IF" statements function in Office Calc.
There are no IF statements in Calc. There are no statements at all in Calc. IF is a function. It has three operands: =IF(condition;second;third). When the condition in the first operand is true, the function result is the value of the second operand. Otherwise the function result is the value of the third operand.

smartcooky wrote:=IF((INT(M2)*52<14000),0.1189,(((INT(M2)*52)-14001)*0.1889+(14000*0.1189))/(INT(M2)*52))
=0.1189+MAX(0;0.07-18.85/M2) gives the same result with a simpler formula. Use a semicolon, not a comma, for MAX().

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3940
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Can an IF statement return a value based on value in a c

Postby smartcooky » Thu Apr 08, 2021 6:37 am

robleyd wrote:The formula as written above will return Error 508 in AOO, as the argument separator should be a semi-colon (;). You may have forgotten to mention that you get this error?

If the integer value in M2 multiplied by 52 is less than 14,000 then the value in M3 will be 0.1189

the tax rate does not climb above 0.1189 until the value of M2 x 52 exceed 14,000 (which is 269.99)


This is exactly the behavior I see using your formula, with commas replaced by semi-colons.


That was quick, and indeed, was the problem. Thank you
Open Office 4.1.7 on Windows 10
smartcooky
 
Posts: 3
Joined: Wed Apr 07, 2021 8:46 am

Re: Can an IF statement return a value based on value in a c

Postby smartcooky » Thu Apr 08, 2021 6:40 am

MrProgrammer wrote:Hi, and welcome to the forum.

There are no IF statements in Calc. There are no statements at all in Calc. IF is a function. It has three operands: =IF(condition;second;third).


Sorry... IF statement, a hangover from my Apple][+ BASIC programming days :roll:

And thank you for your help as well!
Open Office 4.1.7 on Windows 10
smartcooky
 
Posts: 3
Joined: Wed Apr 07, 2021 8:46 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests