Rounding down in Excel 6

[Juice05]

I need to round down within a cell. Not only round down, but round down to the nearest quarter. Any advice would be greatly appreciated.

 

[StuckInTheMiddle]

=IF(A1-TRUNC(A1)>0.74,TRUNC(A1)+0.75,IF(A1-TRUNC(A1)>0.49,TRUNC(A1)+0.5,IF(A1-TRUNC(A1)>0.24,TRUNC(A1)+0.25,ROUNDDOWN(A1,0))))

This formula should work for you, it will round down to the nearest quarter the value in A1.

A.

 

[xlbo]

ouch - no need for all that:

=FLOOR(A1,0.25)

will do nicely

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[StuckInTheMiddle]

hehe, Wow don't I feel silly! Learn something new every day.

 

[KenWright]

And just in case it is of any use =CEILING is the complementary function to that.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

StuckInTheMiddle - don't worry about it. Do you know how I got the answer ?? by searching the archives (as I had seen this asked before) and finding a post in which I suggested using code to accomplish it - it was a round UP question which needed CEILING but other than that, virtually identical - someone else came in and suggested using CEILING - totally made my post redundant.

Moral of the story - as long as you learn, it doesn't matter how silly you feel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Juice05]

Thanks for the assistance. When I add it to the A1 cell I get an error saying that Excel can't calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference.

If I put it in A2 it works when referencing A1 in my function. How do I apply it to the cell that I want rounded?

 

[AnotherHiggins]

You can't put the FORMULA that references A1 in A1!

Once you place that formula there, the original value is overwritten - only the formula remains. So you are entering a formula that references the result of itself - that's a critical error in logic.

Place the formula somewhere else on the sheet and refer to A1 (or whatever cell contains the value you want to use).

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[StuckInTheMiddle]

Hi Juice05,

Thanks for the star! anotherhiggins is right you need to copy the formula into a adjacent cell for instance.

See the image below which shows the values you want to Rounddown in column A and xlbo's formula in column B.

A.

 

[StuckInTheMiddle]

P.S. If you don't wish to show the un-rounded figures you can hide column A by right-clicking the 'A' in column A and choosing 'Hide'.

 

[wec43wec]

How would you round to the nearest 0.25 increment vs roundDOWN to the nearest 0.25 increment?

 

[TonyJollans]

Hi wec43wec,

You will need to have the Analysis Toolpak AddIn for this, but MROUND is the function you need ...

[blue][tt] =MROUND(A1,0.25)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[wec43wec]

TonyJollans

This gave me an error message of

#NAME

 

[TonyJollans]

Select Tools > Add-Ins... from the menu
Check Analysis ToolPak
Click OK

Try again.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[