Perplexing Question...........

[bfraser]

I have a cell P47 in a worksheet that has a formula in it as follows
p44-p45-p46

I also have an IF statement with a warning message in another cell L49 as follows
=IF(P47<0,"You have worked more hours than required", "")
I also have a second IF statement with a message in another cell L50 as follows
=IF(P47>0,"You have worked less hours than required", "")


Sometimes the top formula comes back with a negative zero (0.00)
which then brings up my warning message

How can I rewrite this top formula so it does not come up with a negative zero value?

 

[shg]

Try

=IF(P47 < -0.005, "You have worked more hours than required", "")

 

[bfraser]

That only works for small neagative values, I need the same kind of thing for small positive values so that whether the number is -.000001 or 000001 it always truncates to be 0.00
Any other thoughts???

 

[shift-del]

Hi

I suppose some ROUND()ing would do the trick.
<table valign="middle" colspan="3" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="227,25pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="3" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">-0,00</td><td align="left">You have worked more hours than required</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">0,00</td><td align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>A1</td><td>=107.33-107-0.33</td></tr><tr><td>B1</td><td>=IF(A1<0,"You have worked more hours than required","")</td></tr><tr><td>A2</td><td>=ROUND(107.33-107-0.33,2)</td></tr><tr><td>B2</td><td>=IF(A2<0,"You have worked more hours than required","")</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[bfraser]

Thanks very much

Thanks very much