I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.
I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros
I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.
Column A of the file shows an amount, when summed, give a total of -3.5725E-09.
Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.
Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.
I have a few cells that contain decimals that do not appear as decimals. If the number is 50.1, it only shows up as 50 in the cell. I've tried formatting it to a number with 2 decimal places and to scientific and when I do that the number displays as ##. When I do general formatting it only appears as 50.
I used a formula to pull the number into a different cell, however, and in that cell it shows up as 50.1.
Is there anything I can do to make it show up as 50.1 in the original cell?
I am in need of a macro which would be capable of doing the following;
Replacing a sequence of number's & decimals such as 58.6073.1 with 58607310000
Essentially, the code will just need to remove the two decimal points & to suffix those 4 zeros onto the end of the number sequence.
Is there some kind of function that will remove decimal points from a number? What I'm looking for is:
1231.5 = 12315
123.15 = 12315
12.315 = 12315
1.2315 = 12315
I've read various "solutions" to my request but it's Greek to me. Simply stated I occasionally have to create worksheets that involve dollars and cents. My question is how can I get Excel to stop dropping the zero if its the last digit to the right of the decimal point? $42.30 becomes $42.3, etc.
I've looked through the preferences/options and can find nothing to rectify this. Any way to do this so that keeping the zero becomes the default for any tables, lists, etc that I create in the future?
I have tried, through control panel - region settings etc, to change the default number of decimals to 0. I often use the Number format using the shortcut CTRL + SHIFT + 1 but then have to reduce the 2 decimals down.
I have recorded a macro to apply this kind of formatting but the disadvantage of this is that I cannot undo any actions before that point - which has gotten me into some trouble on a few occasions .
Is there a macro that I can use that adjusts the "Normal" cell style number format from General to Number (with thousand separator but 0 decimals) each time I open a new book? As merging cell styles is also quite a mundane task..
I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?
View 4 Replies View RelatedI've got a wierd simple problem in Excel 2003. I have a laptop and a desktop machine, and I'm an accountant who uses the fixed decimal feature as a default, with it set to 2 decimal places.
On the desktop machine, if I input "23." into a cell and then hit enter, the value left in the cell is "23.00" However, on the laptop, when I do the same thing, I get "0.23" In essence, it ignores the fact that I entered a decimal point.
If I enter "23.0" in a sheet on the laptop, it puts "23.00" in the cell.
I thought maybe it was some sort of hardware thing, like the KB was messed up, but then I used remote desktop to try to see what would happen if I enter numbers into the other computer using the laptop, and into the laptop using the desktop. The laptop when connected to the desktop performed normally, just like the desktop machine. The desktop, when connected remotely to the laptop behved like the laptop machine. In other words, I believe this test takes Hardware out of the picture.
Which leaves some strange obsure setting in excel someplace tht is causing this behavior, and I can't seem to find it.
Does anyone here know why these two installs treat the data entry so differently? It's driving me nuts.
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
If you run it type in 3.5. What gets returned from the procedure is the number 4 NOT 3.5. I think its related to the data type declaration but i thought a data type of long would include the number 3.5.
View 5 Replies View RelatedHow can I add rounded corners and a drop shadow to the code below for a graph.
Code:
Sub AddAICharts()
Dim co As ChartObject, endf%, i%, r$, sname$, suffix
'~~~ Suffix allows the code to be manipulated more easily with changing the sheet name but keeping the Suffix the same
suffix = Array("A", "B", "C", "D", "E", "F", "G", "H")
For i = LBound(suffix) To UBound(suffix)
'~~~ name of sheet is currently set for Objective
sname = "Data" & suffix(i)
[code].....
I want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example:
Column I=7.99
Column M=7.19
Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
I am a beginner to Excel macros. I am writing a macro to find the value based on the search criteria am giving
E.g. ( Record in table)
Column A
24/07/2013 15:04:05
Input am giving
24/07/2013 15:04 ( since am not sure about the milliseconds)
I need to find and fetch the Datas based on the input am giving
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
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 RelatedBasically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies View RelatedI need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
View 4 Replies View RelatedI have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
I 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 am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99.
What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter.
I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.
I have a spreadsheet with rows of entries that are listed by date. I would like to create anther worksheet where i can refer back to the other sheet and calculate the total prices per work week. Is there a way to do this using a function(s)?
View 14 Replies View Relatedhow do i download historical stock prices for approx a hundred stocks i.e what code would suffice?
View 9 Replies View RelatedI have to update our ticker(US and Can stock) prices manually in our portfolio system.
I use www.bigcharts.com as our source . I can import the file in Excel. So what I would like to do is update the prices in Excel from the bigcharts.com. Not just today's prices but prices from previous day or two as well. Once I go to the website, I click historical quotes, then type in the stock symbol, and the date ,then write down the price.
Would Excel web query be the best way to approach this? If so, how would I import the the price for that particualr ticker for that date from the website into my excel?
Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)
Cell F19 should contain =D19*B19
Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2
Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed
Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.
If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.
I believe a Most Even distribution of units will work fine.
After the distribution/population of fields in column2;
D29 must equal F3
F29 must equal F7
I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.
The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
I am trying to have a Vlookup Macro to add all the prices for a certain product in different tabs.
VB:
Private Sub CommandButton1_Click() 'Generate Info Button
Dim ws As Worksheet
Dim ISSUEPRICE As Integer
Dim RESULT As Integer
Dim sheet As Worksheet
For Each ws In ThisWorkbook.Worksheets
[Code] ......
I have a sheet with a price list of papers and sizes. I then use this list to calculate the price of each order. It's a basic VLOOKUP and it returns the price. (this is the section outlined in blue on the demo).
However, i now want to add on another section so that if the order has Lamination (Gloss or Matt) this automatically adds on 20p or 25p respectively onto the sheet price. (table outlined in red but doesn't yet work).
I have attached a sample spreadsheet for reference.
What I am trying to do is eliminate column 'J' on the Distribution tab. I want to use column 'C' on the Totals tab to calculate the subtotal based on matching the products on the Distribution tab in column 'E' with the Products list on the Totals tab in column 'A'. Once the match is made, I need to multiply the associated price in column 'C' on the Totals tab and the associated quantity in column 'F' on the Distribution tab with the result going in column 'K' on the Distribution tab.
I tried
=VLOOKUP(E:E,Totals!$A:$A,3,TRUE)*F:F
and
=SUMPRODUCT(SUMIF(E3,Totals!A3,F3)*Totals!C3)
but neither works and I do not know what else to try.
I have a security order list. This consist of several columns most important are
Column A Column B
Sec a 15,00
Sec b 14,50
Sec c 16,00
Sec a 15,10
Sec a 15,70
Sec b 16,70
Sec a 14,80
The first column gives the name of the security, the second column gives the bid price.
What I want is that I want to sort out all bid prices for a specific security. Vlookup works fine but again it just gives the first record. How can I get all bid prices for security a on a new worksheet without blank cells. If possible I would like to have it sorted as well.