Excel gurus....

February 08, 2023, 03:09 PM

Lord Vaalic

Excel gurus....

Is there a way to have a cell round another cell to the nearest whole number?

For example 46.6 would be 47, 46.3 would be 43.

Not just for the display, I know you can select how many decimals you want to see and it will round that, but I want it to round and use that rounded number for a calculation in another cell.

Example... when do I need more widgets?

I have 1.9 days of inventory, its 2/8 today so that should last until 2/10

But If had 1.2 days, I would need more 2/9

February 08, 2023, 03:27 PM

SigM4

Is it always going to be a number with a single digit after the decimal?

If so, if A1 is the cell you want to round the number of:

=ROUND(A1,0)

February 08, 2023, 03:30 PM

SPWAMike0317

You can use the round function.

Lets say the number you want rounded is in cell A1

In cell A2 you would type =round(A1,0)

Any number you type in A1 will be rounded up or down to the appropriate whole number (e.g. 412.4 will round to 412, 412.6 will round to 413).

If you want to round the result of any equation, just wrap parenthesis around equation and drop it in the round function (e.g. =round((equation),0)

February 08, 2023, 03:36 PM

flashguy

I had the same response, but others beat me to it. (I'm not a guru, but I've played with Excel a lot.)

flashguy

February 08, 2023, 04:43 PM

jprebb

You could also just format the cells to be a numbers and set the number of decimal places you want to see. It will then round the last decimal.

JP

February 08, 2023, 06:36 PM

TRIO

If you want to create a new value that always goes up to next whole number, you could try this:

Cell A2 has original value, such as =355/113 (a very accurate ffraction for PI)

In cell B2, place this formula
=IF(A2-TRUNC(A2)>0,TRUNC(A2)+1,A2)

Edited to add...this only works with positive values.

February 08, 2023, 07:08 PM

TRIO

OK, cues from previous posts leads us to this...

cell A2 as -20.00000000001
cell B2 as =roundUP(A2)

Makes next higher whole number regardless if positive or negative

February 08, 2023, 07:10 PM

TRIO

quote:
Originally posted by TRIO:
OK, cues from previous posts leads us to this...

cell A2 as -20.00000000001
cell B2 as =roundUP(A2,0)

Makes next higher whole number regardless if positive or negative

--Tom
The right of self preservation, in turn, was understood as the right to defend oneself against attacks by lawless individuals, or, if absolutely necessary, to resist and throw off a tyrannical government.

February 08, 2023, 07:11 PM

Lord Vaalic

Thank you everyone! Round function was the trick. My sheet is working awesome now.

February 08, 2023, 07:21 PM

straightshooter01

quote:
Originally posted by Lord Vaalic:
Is there a way to have a cell round another cell to the nearest whole number?

For example 46.6 would be 47, 46.3 would be 43.

Not just for the display, I know you can select how many decimals you want to see and it will round that, but I want it to round and use that rounded number for a calculation in another cell.

Example... when do I need more widgets?

I have 1.9 days of inventory, its 2/8 today so that should last until 2/10

But If had 1.2 days, I would need more 2/9

With that logic and 1.9 days of inventory, you are gonna have to go home early on 2/09