adding a ROUND into an IF Statement

[Robbo1971]

I am using an IF Statement to work out the cost of something... by doing this, it will work it out to umpteen decimal places (this messes up the spreadsheet to no end) and not the two decimal places required!

Is there a way of adding a ROUND into the equation to get the two decimal places required?

 

[jghrist]

I don't see the problem, maybe an example would help.

 

[IRstuff]

Why don't you just format the cell to be currency? Excel carries all the precision throughout its calculations. The ROUND and formatting functions are purely for display only.

TTFN

FAQ731-376

 

[HgTX]

Formatting is for display only. ROUND really truncates the number.

But yes, why don't you just format the cell to have two decimal points?

On the other hand, if you need intermediate values also rounded (as if someone were writing them all down with two decimal points and then plugging them into a calculator), and can't afford the possibility that your result might be off from that result by a penny, then stick in a ROUND anywhere you need it. I don't see the problem either.

Hg

Eng-Tips policies: faq731-376

 

[Robbo1971]

the formula I am using is as follows:

=IF(29.99>=G21,G21*(5.25/100),IF(599.99>=G21,(((G21-29.99)*(3.25/100))+1.57),(((G21-99.99)*(1.75/100))+(1.57+18.53))))

I have formated the cell to 'currency', but another formuala in another cell uses the answer... the answer is not to two decimal places, only two decimal places are shown.

Hope this expands on the issue I have.

 

[handleman]

Perhaps I'm just dense and totally misunderstanding the problem, but why doesn't

=ROUND(IF(29.99>=G21,G21*(5.25/100),IF(599.99>=G21,(((G21-29.99)*(3.25/100))+1.57),(((G21-99.99)*(1.75/100))+(1.57+18.53)))),2)

do what you want?

-handleman, CSWP (The new, easy test)

 

[Robbo1971]

Perfect... the answer was staring me in the face!!!

Thanks

 

[IRstuff]

If you do this a lot, you might contemplate using the "Precision as Displayed" option. Once you format the desired precision, Excel will drop the precision it usually carries.

TTFN

FAQ731-376

 

[Robbo1971]

thanks for that...

another hidden command deep within Excel.

 

[IRstuff]

Well, that particular one is pretty old, dates to at least Excel 4

TTFN

FAQ731-376