Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Unwanted rounding of whole numbers

IF(L4≥I4,I4×H4)+IF(L4≤G4,−G4×F4)


I'm trying to create an IF/Then formula whereby the number in L4 triggers a calculation if L4 is greater than I4 or less than G4.

The formula works well except that the calculation is rounded to the nearest hundred. As I have it now I4xH4= $99.00 but it is displayed as $100.00. G4xF4=$-198.00 but it is displayed as $-200.00.

It seems as though it has to do with the fact that I4 and G4 are numbers derived from a formula. When I use a different cell other than I4 or G4 that is not derived from a formula the result is not rounded.


I have made sure I have enough space in the cell to display the number and have the column set to Currency with 2 decimal spaces.


Any suggestions?

Posted on Mar 12, 2018 9:38 AM

Reply
4 replies

Mar 12, 2018 1:04 PM in response to wookiepants

HI w'


What are the current values, expressed to at least 6 places after the decimal, in each of the cells included in the formula, and in the cell containing the formula?


Regarding the formula itself:

IF(L4≥I4,I4×H4)+IF(L4≤G4,−G4×F4)


IF(L4≥I4,I4×H4)

What is to happen in this part if L4 is less than 14?


IF(L4≤G4,−G4×F4)

What is to happen in this part if L4 is greater than G4?


Here are some examples of what does happen with the formula as written:

User uploaded file

Columns C and D show the results of the parts after and before the + sign. The blue 'warning' triangles in column A (which contains the full formula) say "The formula uses a Boolean in place of a number (but 'I' managed to work out an answer,)".


Assuming the answers above are correct for the 'data' shown, you can remove the warnings by making the edits below, telling each IF to return a zero is the condition is not met.


original: IF(L4≥I4,I4×H4)+IF(L4≤G4,−G4×F4)

revised: IF(L4≥I4,I4×H4,0)+IF(L4≤G4,−G4×F4,0)


Details requested in the first question in this message may help solve the issue you asked about.


Regards,

Barry

Mar 12, 2018 2:13 PM in response to wookiepants

"Does this do the same function as your formula?"


Wouldn't expect it to, as my changes are two additions to the formula presented in your initial post, and your edits include both additions to that formula, a change to the relationship between the original two IF statements, and changes to some cell references in those IFs.


Here's the original, with my revision above it and yours below. Changes are in bold.


revision B: IF(L4≥I4,I4×H4,0)+IF(L4≤G4,−G4×F4,0)

original: IF(L4≥I4,I4×H4) +IF(L4≤G4,−G4×F4)

revision W: IF(L4=0,0+0,IF(L4≥J4,I4×J4 , IF(L4≤H4,−H4×G4,0+0)))


Why "0+0"? "0" gives the same result and skips the calculation of 0+0.


The initial formula, and revision B, have two independent IF statements. Both are evaluated each time the formula is run, and the results are added. Both these formulas give the same numerical results for the data provided in my example. The original presents a warning when either IF presents a FALSE result.


revision W adds an IF to test for 0 (or 'no entry') in L4, and returns zero if the test result i TRUE.

The original omits this test. 0 in L4 results in the formula returning the product of the contents of G4 and F4.

It appears the table has been edited by moving column K to a new location between E and F (now G).


revision W also changes the relationship between the IF statements.

In the original, each is independent, and each calculates every time the formula is run.

In the revision, the three are nested; the second is called only if the first's condition returns FALSE, the third is called only if the second's condition (also) returns FALSE, The formula will do only one of the root calculations: 0+0, I4*J4, H4*G4, or 0+0, exiting after the first TRUE encountered, or after the final FALSE if there are no TRUEs.


Regards,

Barry

Unwanted rounding of whole numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.