I'm using Excel2010. I have a spreadsheet of loan information contained in rows. One of the columns lists the loan rate as a number (not a percent - so 5.25% is listed as 5.25, not 0.0525 and formated as percent). I need to pivot-table using the rate, rounded up to the nearest quarter of a percent (so 5.3875 rounds up to 5.50). Is there a formula to do this, or is it better to use a lookup table?
View 4 Replies
I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?
View 4 Replies View RelatedBelow is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well.
In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.
I am using the MROUND function and it will round up or down to the nearest whole 50. Is there a function to round only UP to the nearest whole 50?
ie: 1704 is being rounded to 1700 I need 1704 to rounded to 1750
I trying to make a formula that will multiply a cell by 1.43 and then round the total up to the nearest 45 or 95 with no decimal points.
View 9 Replies View RelatedI am looking for a formula which will allow a value to round up or down to the nearest x.99 if the value is greater than x.95 or less x.05 otherwise remain the same. i.e if the value is 0.96 it should read 0.99 and if the value is 1.04 it should read 0.99. however if the value 1.05 it should read 1.05.
View 9 Replies View RelatedI need to round numbers up in a spreadsheet to the nearest .05 . For example, I would need to change 1.502 to 1.55 or 1.556 to 1.60 .
View 3 Replies View RelatedI'm trying to round some figures UP to the nearest .5, However i only know this formula: =MROUND(P2,0.5) and that rounds to the nearest .5? Is there anything i can use instead to make sure it is always up not down. so 6.13 becomes 6.50 or 7.53 become 8.00? etc.
View 2 Replies View RelatedHow do I format the date to be to the nearest work day?
I want to establish a due date for an asset that is due 5 days before launch. If my launch date is in A1, and my due date is in A2, in A2 I put:
=A1-5
So how do I make sure that the resulting date is a workday?
How could round a time value like 08:23:51 to the nearest 15 minute like 08:30:00?
View 2 Replies View RelatedI want to use a single function to round to nearest 10 e.g. 1036 should round to 1040 and 1032 should round to 1030
View 3 Replies View RelatedI am trying to figure out a formula that will provide the following results for time worked. (This is contract work that is billed in 1/10 hr increments.)
1 min - 5 min = no charge (rounddown to 0 min)
6 min - 11 min - rounddown to 6 min
12 min - 17 min - rounddown to 12 min.
or
1 - 5 min are billed at 0 hours
6 - 11 min are billed at .1 hour
12 - 17 min are billed at .2 hours
B15 = start time, C15 = end time, G15 = billable time in 1/10 hrs
I have numbers ranging from 1 to 90 that need to be rounded the nearest whole number that is divisible by 5.
Expected outcome
1 should round up to five88 should round up to 9072 should round down to 70
Round - modified for multiples of five* - works perfectly well for number that are greater than or equal to 3. However, 1 and 2 round to zero.
I have tried using IF to have the formula ROUNDUP for 1 and 2 and ROUND for 3 and up with no luck.
Every result I have seen with the Google says the solution is "=ROUND(A1*0.05,0)". This does not work.
VBA is an option for this workbook.
*ROUND(A1/5,0)*5))
I need a formula to take an entered tim and rounded it to the nearest 1/4 hour
View 9 Replies View RelatedI guess I have an odd rounding up query. I want to know if its possible to make excel round up a number to the nearest number from within a list of numbers.
For instance my list could be
10
12
15
17
19
22
25
30
35
42
The numbers arent odds or evens or multiples of anything so I'm guessing there won't be an inbuilt function to do this but is there anyway I could get to the answer in a more round about way with helper calculations??
I guess I could use the following -
=IF(A1>42,"Error",IF(A110,A112,A1
I Need Formula To Number To The Nearest Number In Te Tabel
View 9 Replies View RelatedI need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour.
View 9 Replies View RelatedThis issue is due to the limitations of the nested if formula. I have range of cells that contains percentile rankings 1-100. I am trying to figure out how to convert the cells that contain values 1-10 to a 1, cell values 11-20 to a 2 and so on.
View 2 Replies View RelatedIn trying to use MROUND to round time to the nearest .25 I am getting an error " Reference names and arrays are not supported in Pivot Table Fromulas.
Here is my formula for the calculated field =MROUND( 'Numeric duration',.25) Whereas Numeric duration is the name of the Pivot table field.
I need to round up the sum of a column figures to the next nearest quarter. For example: $1.11 would be rounded to $1.25, or say $1.83 would be rounded to $2.00. What formula would I need to do this? I'm using Excel 2000
View 2 Replies View RelatedI have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.
View 5 Replies View RelatedIs it possible to 'round' a time to the next nearest 15min interval?
As an example in cell A1 I have a value that returns 2:07 PM (its formated
as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min
interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would
want to show 5:45 PM in B1 etc etc
I am doing a huge price increase for a price book and need to rasie each price by 3% and have it rounded to the nearest dime.
Is there a formula to do this in one go and not have one cell with the rounded price and another for the nearest dime price.
I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments.
Desired result:
1/1/07 12:00 in A1
1/1/07 14:10 in B1
1.25 in C1
0-7 Minutes - round down to 0.00
8-22 Minutes - round to 0.25
23-37 Minutes - round to 0.50
38-52 Minutes - round to 0.75
52-60 Minutes - round to 1.00
Because this is billable time, the number is never negative, so I don't need the formula to verify the first date/time cell is less than the second.
Is it possible to have this function work to round in the same cell the data is entered in? ...
View 9 Replies View RelatedWe are trying to find the correct formula to round number to the nearest even number. In the attached example, 0.105 should round to 0.10 since the zero to the left of the five is even. If the problem was 0.115 then the answer would be 0.12 since the one to the left of the zero is odd.
I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5
i need a formula that turns 3.5 to 4
when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5
i.e. a formula turns 3.2 to 3.5 and 3.5 to 4
I work at a facility where we care for adults with disabilities and we need to record times that clients arrive and leave. I created an attendance check-in sheet that needs the current time entered quickly. Is there code that will allow staff to double click on a cell and have the current time entered and rounded to the nearest quarter hour?
View 1 Replies View RelatedIs it possible to round up or down a 24HR time format.
ie. 18:06 would be 18:00
18:28 would be 18:30?
I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.
I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.
Attached is my form : Timesheet Form 2014.xlsx
