Free Microsoft Excel 2013 Quick Reference

Decimal places and rounding up (or down)

Need a bit of help please.

Have a spreadsheet showing rents charged out. For example in column 1 annual
rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and
in column 3 the monthly rent is added to the VAT to give a total.

Example

£6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66

But in my total column I get £626.67 and can't seem to get rid of that extra
penny.

Is there any way to force the calculations to work to the nearest penny as
£6,400.00 divided by 12 is actually £533.33333. What I want is it to
disregard anything after the two decimal places.

Hope that all makes sense.

Thanks for any help in advance.

Ash


Need a bit of help please.

Have a spreadsheet showing rents charged out. For example in column 1 annual
rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and
in column 3 the monthly rent is added to the VAT to give a total.

Example

£6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66

But in my total column I get £626.67 and can't seem to get rid of that extra
penny.

Is there any way to force the calculations to work to the nearest penny as
£6,400.00 divided by 12 is actually £533.33333. What I want is it to
disregard anything after the two decimal places.

Hope that all makes sense.

Thanks for any help in advance.

Ash

How do I round up or down based on whether a cells decimal is greater than .77, regardless of what the integer is? For example, 2.75 & 3.76 would rounddown and 2.77 & 3.78 would roundup to the nearest integer.

How do I remove rounding up or down errors?

I haven't got an example to hand, but when excel works out an average forumla which to use the full answer would be to say 5 or 6 decimal places, but I only want to show it to 2 how do I remove the 'aparent' visual rounding up or down error?

Hope the above makes sense.

Thanks!

I am needing a formula that would cause a currency amount from a calculation to round up or down to the nearest 100.00 mark. The current formula is ie: =E10*F10 (e10 being an amount of money and f10 being a percentage fo it.) I am needing the resulting answer to round up or down to the nearest 100. So if the answer is below the 50 mark it would round down and above it would round up. If that is not possible then just rounding down would be acceptable.

How do i do this??

Thanks

Hello,

I have the following situation:

2 workbooks, named "01" and "02"

In "01" i have a time formated cell "F5" witch contains a substraction of time and outputs something like this: "1:25"

In "02" i also have time formated cells "L1" and "D10":
L1 = 1:00
D10 =IF('01'!$F$5>$L$1; '01'!$F$5; "-")

D10 will either output "-" if 01!F5 is under one hour (F5<L1) or 01!F5 if the time is above 1 hour (example "1:25")

How should the formula:
=IF('01'!$F$5>$L$1; '01'!$F$5; "-")
be modified in order for the time to be rounded up or down to the hour (up 1:29 will be floored to 1:00, above 1:30 will ceiled to 2:00)?

example:
1:20 -> 1:00
1:29 -> 1:00
1:30 -> 2:00
1:59 -> 2:00

2:10 -> 2:00
2:29 -> 2:00
2:30 -> 3:00
...

Can anyone help me change this formula =IF('01'!$F$5>$L$1; '01'!$F$5; "-") in order to make the round up or down to the hour?

Thank you.

p.s: I use excel 2003

Hello, I'm trying to figure out the formula for the following example

=400*.98 I want the answer to then round up or down to the nearest zero or 5 Thank you I'm sure this is not to hard but I could not figure it out,,,, Newbee Thank you very much

How do I create a single formula to either round up or down the result of my
formula to the nearest "9"?
i.e.
843 = 839
845 = 849
etc., etc.
Thanks,
Jack

Hi,

I am new to Excel.

I have a spread sheet with hundreds of retail prices.

I need to round up or down.
Example: $2.58, $2.42

< .5 would be rounded up... ex: $2.58 would round up to $3
> .5 would be rounded down...ex $2.42 would round down to $2

What is the formula to accomplish this without doing each one separately?

Thanks a lot for your help.

75

I am trying to round up or down in excel to the nearest 5 mins , but with no joy

ie 05:24 = 05:25
06:21 = 06:20

Can anyone help !!!

Hi People,
Is there a way to round up or down in 1/2 units.
e.g. 2.3 up to 2.5, 2.1 down to 2.
Any help appreciated.
Thanks,
Mal

I'd like to be able to enter a dollar amount (say A1 = $22.05) and then have
8 cells display dollar amounts that add up to A1. Is there a function that
would round up or down the correct number of cells (to two decimal places) to
equal $22.05?

There are discrepencies in my spreadsheet because i believe excel is taking into account more than two decimal places when calculating formulae. How do I make it work with two decimal places rounded up? They already appear as two decimal places but the sums don't work out right. For example £1.06 times by 5.2 on my spreadsheet comes out as 5.50 when it should be 5.51. When I look at the £1.06 figure and add a few decimal places it comes out as 1.05781.

What can I do?

Ok making progress but need a little guidance.
Have vba that copies a certain criteria from Gate Log rounds the total hours worked and then paste the data into the Timesheet
see code below:


	VB:
	
 Test() 
    Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2") 
    Worksheets("Gate_Log").Range("D2:D300").Copy Worksheets("Timesheet").Range("D2") 
    Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2") 
     
    Dim OneCell As Range 
    For Each OneCell In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp)) 
        OneCell.Formula = "=Round(" & OneCell.Value & ",0)" 
    Next OneCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

would like to have the code automatically deduct 30 minutes from the total hours on the gate log and then round it to the nearest 1/2 hour based on 15 minute scale and then paste that value in column L of Timesheet. Current code works great but is rounding up or down based on .50 scale wrather than .15 ,ie; 15 minutes.

Example:
Gate Log = 7.45 - 30 minutes = 7.15 then rounded = 7.00
Gate Log = 7.46 - 30 minutes = 7.16 then rounded = 7.30

I think I posted the code correctly, don't want to offend anyone here, due to my own short comings.

any guidance would be greatly appreciated.

Hi

I have a column of approximately 8,000 cells (in B4) containing numbers in the format of 8.1
8.5
9.6
etc

I deed to round the number up or down (in B5). I need any figure over .2 to be rounded up, and any figure under .1 to be rounded down. For example 8.2 would become 9 but 8.1 would become 8.

Is this possible?

I need assistance in identifying a macro or macros that will help with our payroll data processing. I need a macro or macros to perform the following:

Round up or down to 15 minute increments (i.e., 6:45 am should round up to 7:00 am; 7:05 am should round down to 7:00 am

Also, we have 24 hour shifts at our company. Excel converts 24 hours to 0:00, which doesn't allow us to include that 24 hour period in our Sum value. Can a macro fix this issue?

Many thanks.

Is there a way to program a cell to automatically round a number up or down if the number being rounded is a sum of other numbers? I'd like to be able to round this $1.22 either up or down in $0.25 cent increments.

Example:
=sum($0.15+$1.07)=$1.22

RThis is for a price sheet for a new business

I have cells with formulated numbers that I want to round up or down
depending to X,X99.95 or X,X49.95.

I am working a retail price formula rounding up or down to either

$X,X49.95
$X,X99.95

the answers in 6 cells need to be *0.387 these round up or down (this is ok)
but when i need to add these up 6 cells up its still taking the 3rd decimal
place in to account eg 15*0.387= £5.81, 10*0.387=£3.87. 30*0.387=£11.61 these
3 are duplicated once more, when i add them up the answer is coming out as
£42.57 and shoud be £42.58 plase can you help me

I am trying to round up or down to the nearest whole number, based on a particular decimal value. e.g. if the decimal is equal to or greater than .1 I would like to round up to the nearest whole, if the decimal is less than .1 I would like it to round down.

Any ideas ?

Hi there.

I'm trying to format numbers to display a limited number of decimal places without rounding the displayed result or altering the actual value of the cell.

For example, the value in the cell is: 78.6361538461539

I need it to be displayed as: 78.63, but still keep the underlying value for the purposes of calculation.

I've searched everywhere and cannot find a solution. Changing the decimal places in the number format will round the displayed result and the ROUNDDOWN or TRUNC functions alter the underlying value.

Is this possible using number formatting or formulas? In the worksheet I'm working on, a VBA solution is not feasible.

Thanks in advance for any help you can provide.

I've written a purchase order spreadsheet and I need to round the retail price to the nearest .n9 number.
Example: $4.00 would be $3.99
All the prices in the store end in 9. Do you know of a way to write this formula or macro?
The prices that end in .n0 to .n4 would be rounded down to .n9
The prices that end in .n5 to .n8 would be rounded up to .n9
Thanks for any assist.

Hey folks. I am a newbie here and a bit of a biffer when it comes to Excel so I hope someone here can help me out.

I have a spreadsheet that carries out some mathematical functions and the final output is a number between 0 and 25. What I would like to do is round the numbers up or down so they only output:

0
5
10
25

i.e. if the raw output is equal to or greater than 24, output 25
if it is less than 24 but greater than 9.5, outputs 10
less than 9.5 but greater than 4.8, output 5
less than 4.5, output 0

I have tried using nested IF functions but got myself horribly tangled up.

Can anyone please point me in the right direction with this?

Many thanks

I have created a price list in excel but the amounts are coming out at say
£25.68 and I would like to tell it to round up or down to the nearest 25p can
I do this