If Statement Help

[Ben]

I have the following if statement in one of the cells:

IF(H465=$B$6,(((($B$4)/(($D$2^$D$2)*($F$2^$F$2)))^(1/($D$2+$F$2)))),(($B$3)*((H465-$B$6)/(H465+$B$1)))))

Now it is not giving me the answer that I calculate so to troubleshoot I
started by inserting the value that is in cell H465 and it did give me the
right answer. So my question is why if I change the value of H465 to a
constant in the comparison operator does the formula work as planned but if I
leave the cell reference (H465) then it does not calculate the right value.

One more piece of information is that there is a formula in the H465 to
calculate its value. Maybe that has something to do with it? Please help!

Thanks,
Ben

 

[Ron Rosenfeld]

I have the following if statement in one of the cells:

IF(H465=$B$6,(((($B$4)/(($D$2^$D$2)*($F$2^$F$2)))^(1/($D$2+$F$2)))),(($B$3)*((H465-$B$6)/(H465+$B$1)))))

Now it is not giving me the answer that I calculate so to troubleshoot I
started by inserting the value that is in cell H465 and it did give me the
right answer. So my question is why if I change the value of H465 to a
constant in the comparison operator does the formula work as planned but if I
leave the cell reference (H465) then it does not calculate the right value.

One more piece of information is that there is a formula in the H465 to
calculate its value. Maybe that has something to do with it? Please help!

Thanks,
Ben

What is clear is that what you type into the formula to replace H465 is NOT the
same as the actual contents of H465 (or the result of that formula).

Probably either a TEXT versus NUMBER issue; or possibly a rounding versus
formatting issue.

What is the formula in H465?
What are the precedents of that formula?
What is the contents of B6?




--ron

 

[driller]

Hi BEn,

Sir Ron, Ben can also try to use a very helpful tool in excel to derive
where the error maybe coming from the content of his formula...

tools>formula auditing>EVALUATE FORMULA...

regards...

 

[Ben]

When I used the evaluation tool I noticed something was going wrong with the
way the H* values are incrementing. I do not know how to fix it either. The
H* function is the value of the previous H row + 0.1 (Hn=H(n-1)+.1 or ex.
H50=H49+0.1) But one thing I noticed is that every 300 or so repeats of that
(H300) it adds an additional 0.0000000000001 to the previous number (if
H299=20.3 then H300=20.3000000000001) I do not know why it is doing this.

 

[driller]

Ben,
maybe u can try some extra function to round these decimals, if it is not so
relevant in the result you need....

 

[Ron Rosenfeld]

When I used the evaluation tool I noticed something was going wrong with the
way the H* values are incrementing. I do not know how to fix it either. The
H* function is the value of the previous H row + 0.1 (Hn=H(n-1)+.1 or ex.
H50=H49+0.1) But one thing I noticed is that every 300 or so repeats of that
(H300) it adds an additional 0.0000000000001 to the previous number (if
H299=20.3 then H300=20.3000000000001) I do not know why it is doing this.

If all you need to do is increment by tenths, change your formula to something
like:

Hn: =ROUND(H(n-1)+0.1,1)

For an explanation, checkout Rounding Errors in Excel at support.microsoft.com
--ron

 

[Ben]

Thank you both for your help. That worked.

Ben

If all you need to do is increment by tenths, change your formula to something
like:

Hn: =ROUND(H(n-1)+0.1,1)

For an explanation, checkout Rounding Errors in Excel at support.microsoft.com
--ron