Round Number Down by Decimal Places Using Format

Hi, If you enter a number in Excel which is halfway like 8.5 and then select Format - Number - 0 Decimal Places, the number becomes 9. My question is about if there is a way to round this number down. For example, 8.

I know the Rounddown and Roundup functions exist and how to use Rounddown to make this happen. Is there an option in Excel where you can just format the cells instead? Perhaps using Custom instead of Number might do something? Is there something in Tools then Options which will do this?

If there isn't, is there a way you can make this happen in one cell instead of needing Rounddown to reference another cell? For instance, at the moment I have 8.5 in one cell and then 8 in another cell using Rounddown whereas with Format - Number, I type in 8.5 and that cells automatically becomes 9 in the one cell. Any help would be great. Thanks.

Hi jaclrsen

How about Formatting cells, Custom: #

Hi Kevin UK, Thanks for the response. If you put 8.5 in a cell and use # in Format Cells > Custom, the cell will return a 9. The number goes up to 9 but I want it to go down to 8. I think you're correct about doing it through Custom instead of Number but I'm not sure what syntax to use. Any further help would be great. Thanks.

Hi jaclrsen

No I don't think you can. You will have to use the ROUNDDOWN.

Kevin

Hi Kevin UK and Armando, Thank you both for your responses with the macro and ROUNDDOWN suggestion. I have one question regarding the ROUNDDOWN suggestion. Is it possible to put a ROUNDDOWN formula in cell A1 and then type a number in cell A1 which is rounded down?

For example,

A1 may have =ROUNDDOWN formula but if you type 8.5 into A1, it is rounded down to 8 doing it all in the same cell of A1.

I'm guessing this will create a circular reference but is this possible to have a formula working in the background? Alternatively, would you have to go with a macro like what was suggested. Thanks for the terrific help and any further help would be great.

Hi jaclrsen

No you will over write the formula. What about if you have a helper column say column A and you put your formula in column B then hide column A!

Might be able to do it with code, have you done a search.

Kevin

Hi Kevin UK and Armando, Thank you both for your responses. Kevin UK, thank you so much for your extensive help and taking on my query. Armando, thank you so much for a great solution.

Considering Format Cells won't work, I think ROUNDDOWN or Armando's excellent Macro which does work are the best solutions. Thanks so much for your time and efforts.

Hi jaclrsen

Thank you for the feed back.