i want to do something that should be relatively simple, and yet it is not... has me stumped. what i'm doing: taking a range of numbers... from 1-19, 20-39, and so on, and using a vlookup function to pin a rating for said value off a reference table. heres a example of what i'm doing:
A | B
1 1
2 1
3 1
4 1
5 1
... ...
19 1
20 2
... ...
40 3...................
I receive a list of numbers that are rounded to three decimal places. I need to round them to two decimals - here's the catch - if the third decimal is a 9, it needs to go up, otherwise, 1-8 need to round down.
Example
3.678 goes to 3.67
3.679 goes to 3.68
If the third decimal is <=8, it needs to go down.
I need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.
For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00
Lets say the number is in cell A1. What formula would do this?
For this example, A1 is the given at 19.875 I am trying to get A2 to net a result of 19.5. A2 is to net that result by searching through cells B1:B14 and finding the nearest equal or lower number. If A1 is less than 13.5 a result of "error" should be generated in A2.
B1=13.5
B2=15.5
B3=17.5
B4=19.5
B6=21.5
[Code]...
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
How can I drop the whole number part of a number and leaving only the decimal part of the number. Then multiply the decimal part of the number with a number. Then repeat this in a sequence. The object is to convert Lat and Long decimals to Hr. Min. Sec.
eg. 53.535663 .535663*60=32.13978 .13978*60=8 53 32 8
eg. 113.352640 .352640*60=21.1584 .1584*60=9 113 21 9
eg. 113.306579 .306579*60=18.39474 .39474*60=23 113 18 23
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies View RelatedI need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies View RelatedI know the basics of excell, but haven't ever written any formulas or macros (if that's what I need?) that would perform the following opperations. Bare with me, it's a three step process.
1. I need any zero's to the right of the last number (1-9)
to be removed. (I think this can be done with the click of a button, somewhere?)
examples:
a. 173.17300 = 173.173
b. 231.11000 = 231.11
c. 53.00000 = 53.
2. I also need for the decimal to be moved to the right of the last number after the zeroes have been removed from the previous step. (This seems to be the most deficult step, but most important for me.)
examples:
a. 173.173 = 173173
b. 231.11 = 23111
c. 53. = 53
3. I then need to be able to cross reference those remaining numbers with a set of numbers I have.
examples:
Remaining Numbers / My List
a. 173173 / Not on list
b. 23111 / MATCH
c. 53 / Not on list
rounding the numbers. I am working on a quote in which quantity is arrived by dividing the sell price by Total sell price. The condition is the result (quantity) should always be a whole number, I can achieve that by cell formatting but when the calculation is done using handheld calculator the results are different.
I need the result to be same if using excel or handheld device i.e quantity in whole number.
I want to use a formula that will round the number UP to the next whole if the the tenths is .3 or higher and round DOWN if the tenths is .2 or lower.
ex. 79.1 --> 79
79.2 --> 79
79.3---> 80
79.4---> 80
79.5---> 80
79.6---> 80
79.7---> 80
79.8---> 80
79.9---> 80
I am thinking this is either not possible or would be an If Then type formula but way beyond my knowledge.
The reason I want to do this is becaue if my student has a 79.5 avg for instance I would normally round to 80. However if they are off by just .1 (ex 79.4) i still want to give them the 80 and then I decided to make it within .2 (ex. 79.3.)
I have budgets that I want to round to certain numbers
For example:
55% rounds to 75%
45% rounds to 50%
33% rounds to 35%
And so on. I have about 7 of them to do, I'm hoping that someone can help with this. I have been struggling all morning on it.
Is there a code that turns 4.123 to 4.5, so it rounds .123 to .5
And same for 4.8 a code turns it to 5, so it rounds .5 to 1 and add it to 4
I tried with round, but……………..
I want round off number to be with the decimals (usually decimals r discarded for round off feature). I ll be able to explain better with an example.
10.36+10.36= 20.72. If i round it up, it vl be 21.
what i am tryin is to get 21.00 after round off.
I prepare my bills in excel and after total of all products, if i get a decimal number, I would like it be rounded off to whole number with decimal and 00 at the end (eg. 21.00 instead of 21.27 or 22.00 instead of 21.77). I tried to change decimals but when I select 2 decimal places, it gives me the actual fig(eg. 21.27 instead of 21.00)
I am already using =sum for the total so cannot use another formula (as far as i know, 1 cell can have only 1 formula. Dunno if multiple can be used in a single cell).
I 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 Relatedthis is a formular that i used to divide
=IF(Z6=TRUE,K6/MID(D6, FIND("1=",D6)+2,LEN(D6)-FIND("1=",D6)-1),K6)
the problem i have is that if i have 14 in cell k6 and 1=10 in d6 the answer i get is 1.40 what i need the formular to do is always round it up to the highest number
ie 2 not 1.4
example 1=10 20 answer 2
1=10 25 answer 3
1=4 11 answer 3
1=4 20 answer 5
I have a number in a cell (12.34) I wish to copy this number into a user form textbox. I can copy the number, however it is about 6 places behind the decimal point. How can I round the number going into a textbox to 2 places?
View 7 Replies View RelatedI work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.
View 9 Replies View RelatedI am trying to round a number to next multiple of 10 with condition i.e. for example if number is 1230.56 then it should round off to 1230 but if the unit number is more than zero i.e. 1231.56 then it should round off to 1240.
That means the main number should round off to next multiple of 10 only if unit number is equal to or more than 1.
Example 120.11 should round off to 120
121 to 130
120.99 to 120
119 to 120 and so on
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 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 work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.
View 9 Replies View RelatedIt's been awhile since I've done this...and I tried searching for the solution but to no avail.
I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:
Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
I'm trying to do a simple calculation of the # of hours between 2 times, rounded up to the nearest hours. Somehow along the way I've ended up with this massive formula below, which is doing what it's supposed to do except for certain cases (like the one in the attached file...where it says the difference between 2am and 3am is 2 hours) =ROUNDUP(IF(B2="",0,IF(A2="",0,(IF(B2<A2,((((B2+1)-A2)*1440)/60),(((B2-A2)*1440)/60))))),0)
View 4 Replies View Relatedthe way my spreadsheet's set up so far is that each employee should have a goal of, say, 100 for how many people they need to sign up. But they each have specific geographic breakdowns in their turf, some of which might be bigger or smaller than others, but at the end should all add up to 100 for each employee.
It's not super hard to do since I just take a goal of 100, in this example, and multiply it by the percent of population of the area in their overall assignment and give them a goal for that area based on that. So if Philadelphia has 70% of their total population, Upper Darby has 20% of their total population, and Phoenixville has 10% of their total population -- the goals would come out like this:
Philadelphia, Goal: 70
Upper Darby, Goal: 20
Phoenixville, Goal: 10
Ryan, Total Goal: 100
For some of the employees, that works out perfectly -- but based on the population sizes, the rounding sometimes gets a little off and it could end up like this:
Philadelphia, Goal: 71
Upper Darby, Goal: 19
Phoenixville, Goal: 11
Ryan, Total Goal: 101
For most employees, it ends up being dead-on 100 -- but there are some whose totals are at 98 or 99 or 101, and I was wondering if there was a way to force it to shave a point off or add a point on somewhere, pending on how close the decimal was when it rounded to make sure that it always ends up exactly on 100. (I'm using 'Data >> Subtotals' to get what's being represented above as "Ryan, Total Goal.")
I 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 RelatedI 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.
We 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 want to multiply a number say, 1551 by 5%, 6%, 7%, 8% and 9% and round each result to 0 decimal places. Then the sum of these results (544)should be equal to 35% of 1551 rounded to 0 decimal places (543). How do I create an if function for rounding so that the sum equals 543.
View 6 Replies View Related