Excel Basics #14: ROUND function & Number Formatting

[jeffnationaltrade]

How would I know when to round or not round number?

[ExcelIsFun]

@jeffnationaltrade , It is only when:

1) You are required to round (like with money or your bosses tell you to)

2) You are multiplying or dividing decimals

3) you will use the formula result in a subsequent formula.

[samthesung]

Thank you for this tutorial.  I like the way that you teach it is very easy to understand and you are very knowledgable.

[ExcelIsFun]

@samthesung , you are welcome!

[aadmi67]

You are simply amazing!

[ExcelIsFun]

@aadmi67 , Thanks! I am glad the vids help!

[workwithme2011]

aaaaaaaaaaaaa hi there, how can I round up $1.80 = $2.00, and $1,75= $ 1.00, please help, thanks..........

[ExcelIsFun]

I have many videos on rounding:

Excel Magic Trick #237: ROUND to Digit or Multiple

Excel Magic Trick 775: Round to 5 Minutes for Integer Values or Time Values ...

Excel Magic Trick 379: Round to Nearest 1/10th Hour for Payroll

Excel Busn Math 07: When ROUND function Is Required

But none of these cover what you are asking. I do not see a consistent pattern to exploit in your example.

[warlock4001]

I typed the Round formula as specified but it is returning 143.9600 :S Any idea why this might be?

[ExcelIsFun]

Try formatting to show 2 decimals only.

[pathinterpreter]

Thanks! Question: Is it possible to use the display number for a formula? I'm trying to get the year of birth from the age at a specific date(dd/mm/yyyy) and I figured formating "yyyy"in another cell and use minus later, but didn't work. Any suggestions?

Thanks!!

[ExcelIsFun]

Formulas do not see formatting. Dates are serial numbers. For example 8/2/2010 is the serial number 40392, which is the number of days since Dec 31, 1899. If you were to format 8/2/2010 with "yyyy", you would see 2010, but any formula pointing at that cell will not see the 2010 but instead it will make calculations off of the 40392. If you want the year of 8/2/2010 and that date was in cell A1, the formula:

=YEAR(A1)

would give you 2010, and formulas could calculate off of that.

[DaN0T0RI0US1]

Thank you very much! you just helped me with my hw :D

[ExcelIsFun]

You are welcome!

[aman1245singh]

Thanks again sir. God bless you :)

[ssalazarjr]

Hi Mike,

Is there a way to FLOOR "ranges" in functions? I'm using SUMIFS to add my credit cards processed for the month per day. The "criteria_range" i'm using is from a spreadsheat that puts the time and day together. I need to add the criteria done in a certain day. But the time is keeping it from adding. Any thoughts?

I tried: =SUMIFS(A:A, FLOOR(B:B, 1), D1)

Column A=Credit Card Amounts

Column B=Date and Time Processed

Cell D1=Date

But it didn't work.

[ExcelIsFun]

I do not know an answer to that. The best place to ask Excel questions is:

Mr Excel Message Board

mrexcel[dot]com/forum

[sofiathewise09]

Hi excelisfun!

I'd like to ask about a command which results into .0 for decimals less than .5 ... and decimals equal or more than .5 will still be .5. Hope you could get back to me asap. :)

[ExcelIsFun]

Sure! Try MROUND function. Here are two videos with various methods:

Excel Magic Trick #237: ROUND to Digit or Multiple

or

Excel Magic Trick #238: Round Times To 5 Minutes

[sofiathewise09]

@ExcelIsFun Thank you so much sir! You're such a big help! Will check out your other videos. Thanks again. :)

[ExcelIsFun]

You are welcome so much!!

[gastonytHQ]

B7 or $B$7 wouldve been just fine

[ExcelIsFun]

Not if you are going to copy the formula. B7 is relative and will not work. $B$7 or B$7 have a row reference locked and will work if the formula is copied.

[danturners]

Wow, thanks much for your prompt reply!

That helps. So the next questions are: 1) what if the range does not have all the same formulas?, and 2) what if the range doesn't have any formulas but only raw numbers?

I know this isn't specific to rounding. If you can just point me to where I can learn about how to deal with these cases, that would be great.

Thanks again.

DT

[ExcelIsFun]

Dear danturners,

1st one, I dont know.

If the range doesn't have any formulas but only raw numbers, highlight a similarly sized range of empty cells, hit F2, type equal sign, type ROUND(, click on the corresponding cell in the range of raw numbers, type ,2) or whatever number you want for the rounding position, Ctrl + Enter, Ctrl + C, Highlight raw numbers, Paste Special Values.

--excelisfun

[ExcelIsFun]

Dear danturners,

1st one, I dont know.

If the range doesn't have any formulas but only raw numbers, highlight a similarly sized range of empty cells, hit F2, type equal sign, type ROUND(, click on the corresponding cell in the range of raw numbers, type ,2) or whatever number you want for the rounding position, Ctrl + Enter, Ctrl + C, Highlight raw numbers, Paste Special Values.

--excelisfun

[danturners]

Thanks much for these lessons! How do I most easily apply ROUND to a collection of numbers?

Thanks in advance.

[danturners]

In other words, I have a collection of numbers in, say, an array. I want to replace those numbers with their rounded values.

[ExcelIsFun]

Dear danturners,

If the range has all the same formulas, highlight range, hit the F2 key, edit formula (add round), hit Ctrl + Enter to put formula in all cells.

--excelisfun

[bugajoskaa]

hey,

it was pretty interesting and helpful, but i have a question.

what if I need the number of something.

for example i have 101 dollar and i'd like to buy beer which is 3 dollar. so 101 divided by 3 equals 33.6666, and if I do the formula round it'd be 34, but I can buy only 33 cans and i'll have 2 extra dollar.

is there any formula or trick for this case?

thanks for helping and thanks for these amazing lessons!!!

[ExcelIsFun]

Dear bugajoskaa,

Here are some formulas and there results:

ROUND(101/3,0) = 34

ROUND(101/3,2) = 33.67

INT(101/3) = 33

QUOTIENT(101,3) = 33

MOD(101,3) = 2 (this is the remainder)

The QUOTIENT function is in Excel 2007, but must be added in 2003 by going to Tools, Addins, and then checking the Analysis Toolpak.

--excelisfun

[bugajoskaa]

thanks a lot =)

[ExcelIsFun]

Dear bugajoskaa ,

You are welcome!!!

--excelisfun