PayDeg
Page 5
 
 
Sharing Brings Variations
 
Linkedin Company ProfileFacebookTwitter
 
 

Journal of PayDeg

 
 

Hands on Office

Excel- Rounding
Sometimes we need to round numbers or time. For us is is always easy to round numbers using some builtin functions in Excel. But how about rounding time? Sometimes hard to figure it out how it may work. I do have three examples for you;

Rounding time in Excel

  • Mround(); this function rounds the time for 3/20 down to a specific interval.
  • Ceiling(); this function always rounds up to a specific interval.
  • Floor(); this function always rounds down to a specific interval.

Lets talk about the Mround first;

TIME(HOUR(E2);MROUND(MINUTE(E2);$F$2);0)

Time() function converts hours, minutes and seconds as numbers to an Excel serial number, formated with a time format. Since there is no need to round hour, we leave hour without rounding. We need to round minutes, so we will going to use all three function here.

Hour() function turns the value of hour as numbers.

Minutes() function converts minutes in to numbers

You may see the information about Mround(), Ceiling() and Floor() functions definition and example above.

Spread Sheet- Rounding

Gdrive Spreadsheet rounding tip

You can insert functions to a spreadsheet at Gdrive. Go to cell which you want to use function, click on insert at menu, and highlight function and choose more. When you do that a new tab at your browser with /support.google.com will open. Click on the small triangle button next to "select an option" and chhose math in the opening list. In the list of functions you may see all math functions with Ceiling(), Floor() and Mround().

You can write our time function in to related cells. To ease the process you can write the function at first row and then copy down.

Calc- Rounding

OpenOffice Calc Rounding Function

You can use same functions here at Calc, click on Fx button next to input box;

Functions at OppenOfficeCalc

Function wizard will open, choose Date&Time at category, under function list you may easily see TIME, click on it twice.

OpenOffice Function Wizard

You can fill up hour click on fx button again and after choosing Date&Time at category click on HOUR twice after adding HOUR() with select button select the suitable cell (E2), for minute again click on fx button and after selecting Mathematical at category select MROUND and click on twice. Next to number click on fx button again and find MINUTE and add minute too. It is time to choose the suitable cell now which was E2 for the first row.

Function Wizard OpenOffice CalcAnd for multiple choose the interval F2, and then put $ sign around F to fix the column, since we are going to use the same cell in every row.

Then write 0 for second..

So for Mround() we prepared the solution. Same way you can do it for Ceiling and Floor. After managing first row (do not forget to fix interval in the formula) copy first row and paste to creat your table.

 

Life long continous learning is important to compete in today’s business conditions. To be powerful and to be equipped training is a must.

We as PayDeg designed our Office traning to support you and to let you use the software much more efficiently and purposive for your own business needs.   

As PayDeg we can train students for basic business solutions to let them a step ahead of their competitiors in the business life after graduation. We have especially designed Office course just for this porpose.

If you consider our Office training, please check it out our web page PayDeg training.

PayDeg’s training can be customized like a puzzle according to your needs. Please do not forget to fill up our Necessity Analysis to let us manage your traning.

For tips, we are preparing some short videos too. You may go to our channel and chect them out. Here is the link Geek's Channel You may see all of our traning videos over there clik on the ones you are interested on.

Here you may also want to check journal's previous issues' tips;

Rounding

Rounding a numerical value means replacing it by another value that is approximately equal but has a shorter, simpler, or more explicit representation.

We can talk about two types of rounding functions at spreadsheets era. Round, Roundup and Rounddown as first type and Ceiling, Floor and MRound as second type. First type uses numbers, number of decimal places. Second type uses numbers and multiple or significance or interval.

Round(), RoundDown() and RoundUp() functions use numbers and number of decimal places to round. Round() rounds it to the nearrest, RoundDown rounds the number to the nearest, RoundUp rounds the number up to nearest.

Round Functions for Spreadsheets

Lets say our number is 20,135 which is at cell A1 and we would like to round it with 0 number of decimal digits, then our fonction is ; Round(20,135;0) since number of decimal digits is 0 then result is 20. If we would like to round it with 1 number of decimal digits then result will be 20,10 and so on.

If we would like to use Roundup function then for 0 number of decimal digits the result will be 21,00. For 1 number of decimal digit the result will be 20,20 since roundup rounds it up.

For Rounddown function with 0 number of decimal digits the result will be 20,00 and with 1 number of decimal digit the result will be 20,10 and so on.

All rounding functions are working the same way in Excel, OpenOffice Calc and SpreadSheet at Google.

You may get information about other rounding functions from videos too.


Time and Rounding functions - Excel


Time and Rounding functions - OpenOffice


Time and Rounding functions - SpreadSheet Google

 

Second Tip

Excel -List of values

Suppose that we are keeping the test drive and purchasing day records for a car seller.

Excel how to create a list

First prepare the list as in column A. Then under "car" heading at column F after selecting few cells on the menu bar choose Data and in Data Tools section click on Data validation. Doing that Data validation box opens like in the picture. At settings tab under Allow choose List. And click on the button at next to Source to fix the list. Click on Kia and move your mouse down to the Saab. Click on Source button again and return back. At Input Message tab you can you can write the Title of the list and an input message.

Excel Data Validation

And if you like to you can change the Error Alert tab too.

then click Ok and your list is ready to be use in this cell. You need to define list for next cell at column F. Or at the begining you can select a few cells at column F and then open Data Validation window and define it for more than one cell.

GDrive Spreadsheet -List of values

We can use same example as Excel.

Gdrive Data Validation

First prepare the list at column A. Then under car at column F click on the cell and at menu select Data then Validation. Data Validation window will open. Click on the Number and choose Items from a list.

Gdrive Data Validation Box

Click on Cell Range and choose the car data at column A. At the help text box you can write an help like "Choose a car" then Click on Save.

Gdrive Spreadsheet Data Validation

Yes, you can select from your list now. You need to define list for next cell at column F. Or at the begining you can select a few cells at column F and then open Data Validation window and define it for more than one cell.

 
  << --
-->>