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

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

- Decimal places and rounding up (or down)
- Rounding up or down based on different decimal criteria
- Rounding up or down "errors"
- How to make currency round up or down automatically
- Round up or down to hour
- Multiplication calculation to make the answer round up or down
- Rounding up or down in the same formula
- How To Round Up Or Down
- Round up or down time by 5 mins in excel
- Rounding Up or down to 1/2 Units
- Rounding up for down based on cell value
- Decimal places and rounding up
- Vba subtract 30 minutes and round up and down
- Round up or down the number in a cell
- Macro for Rounding Time Up or Down
- Round up or down
- Rounding up numbers up or down.
- Need to round a formula up or down to a specific number
- Stop rounding up in a colum that contains decimals in excel
- Rounding Up and Down
- Format Decimal Places Without Rounding or Changing Cell Value
- Rounding up or down to the nearest .n9
- Perform:Rounding numbers up or down
- How do I tell a price list to round up and down to the nearest 25

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

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!

How do i do this??

Thanks

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

=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

formula to the nearest "9"?

i.e.

843 = 839

845 = 849

etc., etc.

Thanks,

Jack

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

ie 05:24 = 05:25

06:21 = 06:20

Can anyone help !!!

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

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?

What can I do?

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 SubIf 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.

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?

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.

Example:

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

I have cells with formulated numbers that I want to round up or down

depending to X,X99.95 or X,X49.95.

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

Any ideas ?

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.

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.

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

Â£25.68 and I would like to tell it to round up or down to the nearest 25p can

I do this