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

[smartcooky]

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.

[robleyd]

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.

[MrProgrammer]

Hi, and welcome to the forum.

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.

=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

[smartcooky]

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

[smartcooky]

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

And thank you for your help as well!