IF Function is not returning correct number for all cells

[Zimmy242]

If you look at the data under "Chart 1.3" the formula says that if the number on page "20 Ga." multiplied by 1.5 is less than the number on Chart 1.2 to use the number on chart 1.2. It doesn't seem to be working for all of the cells. Did I do something wrong in the formula? I know this sounds very confusing so I included the workbook so you could see the IF function. It works for most of the cells, but not all of them.

[MrShorty]

I don't see anything syntactically wrong with the formula. Can you identify a few cells where you think the formula is returning an incorrect result?

If I knew which cells to debug, I would probably start debugging by bringing up the Evaluate formula tool (https://support.microsoft.com/en-us/...rs=en-us&ad=us ) and step through the function, checking each step to make sure it is doing exactly what I expect it to do.

Gut instinct is that the problem is going to turn out to be some kind of rounding or floating point error, where a calculation that you think should return TRUE is actually returning FALSE (or vice-versa). If it does end up being some kind of rounding or floating point error, the solution will depend on exactly what you are expecting.

[Zimmy242]

Cells B67, B68, B69, C67, C68, C69 (basically all of $118.38), E66

[HansDouwe]

The value of Cell B67 is correct: It is '20 Ga.'!B18*1.5 = $78.92 * 1.5 = $118.38

[Zimmy242]

But that is lower than B18, so it should show $118.40.

[MrShorty]

First, I note the external references that make it impossible to test the calculations. I assumed that the external references should be to this workbook, and delete the external references from the formulas. I did not do anything else to try to verify if this resulted in the correct references. With that edit, follow along in the Evaluate formula tool, if you like.

B67:
1) Retrieve value from 20 Ga.!B21 -- 79.89
2) multiply that by 1.5 -- 119.835
3) retrieve value from 18 Ga.!B18 -- 118.4
4) compare step 2 to step 3. step 2 is larger than step 3, so proceed to value_if_false argument
5) retrieve value from 20 Ga.!B18 -- 78.92
6) multiply by 1.5 and return final result -- 118.38

C68:
1) Retrieve value from 20 Ga.!C22 -- 83.15
2) multiply by 1.5 -- 124.725
3) retrieve value from 18 Ga.!C19 -- 118.4
4) compare step 2 to step 3. step 2 is larger than step 3, so proceed to value_if_false argument
5) retrieve value from 20 Ga.!C19 -- 78.92
6) multiply by 1.5 and return final result -- 118.38

That's how 2 of those cells are calculating. Which step is doing something you are not expecting?

[HansDouwe]

The formula of cell B67 is

=IF('20 Ga.'!B21*1.5<'18 Ga.'!B18,'18 Ga.'!B18,'20 Ga.'!B18*1.5)

I think that should be

=IF('20 Ga.'!B18*1.5<'18 Ga.'!B18,'18 Ga.'!B18,'20 Ga.'!B18*1.5)

[Zimmy242]

OMG!! You're right. I feel really dumb now, but I didn't notice that the first part of the function was attaching to the wrong cell.

[HansDouwe]

You are Welcome.

It doesn't matter.
This happens to everyone who works with Excel or something similar.

Glad to have helped.

TIP: If a formula returns an unexpected result you can evaluate the formula:
Go to the formula and click Formulas --> Evaluate Formula --> Evaluate --> Evaluate ....