Referencing the cell's answer in another formula

Hi there,
I'm new to this forum and am learning excel. I have a lot of programming experience, and this problem seems trivial, but I can't figure it out.

All cells in the following example use currency formatting. The quotes are there just for display, they are not entered in the real input box.

I have a cell, let's say A1. I've inputted a value of 200.
B1 has this inputted: '=A1*0.05'
C1 has '=A1+B1'

So far so good, C1 shows the correct calculation.

I have another cell D1 which I use to input an amount.

In E1, I have this: '=IF(C1=D1, D1*0.8, 0)'

After inputting the same value into D1 as seen in C1, this is where the problem lies. Even though both C1 and D1 cells show the same value, E1 returns 0. It seems it's comparing the formula of C1 as text, not the value the formula returned.

I've tried this:
'=IF(value(C1)=value(D1), D1*0.8, 0)'

and using the Cell function, but nothing works.

I would think that when you refer to a cell name, it returns the value calculated by the formula inside it, not the formula itself. That's how any programming language works. When you refer to a variable or function, it returns the value found in that variable or the value returned by the function.

Any help would be great. Thanks

Could you possibly post the workbook? I don't have that issue if I do the same steps as you did

Yeah you are right daffodil about that, but I doubt that would be the issue if he did input a value of 200, since is only generates integers in that case

Originally Posted by daffodil11

What you are expecting is how Excel should work.

If you copy C1 and Right-Click and Paste Values into D1 (the little 123 icon) does the formula work correctly? If so, then the value of C1 and D1 may not be exactly the same due to floating point precision and rounding rules when dealing with values around 15 digits long.


For example, 1.9999999999999999*.05 approx 2.1 according to Excel. If I put 2.1 in D1, the formula does not work.

Ahhh, thank you, it's a precision problem. It works if I copy the value like you said. But how do I make sure everything gets rounded correctly? Because everything I enter has only 2 decimals, and everything that is displayed has only 2 decimals. And all boxes use currency formatting.

You could use the Round function

Originally Posted by Jdevil

You could use the Round function

Great, using Round to 2 decimals worked like a charm. I keep forgetting about floating point problems like this. Thanks everyone