Prices Not Rounded To 2 Decimal Points

Mar 5, 2009

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

View 5 Replies


ADVERTISEMENT

Phantom Decimal Points

Sep 29, 2009

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.

View 10 Replies View Related

Number Won't Display With Decimal Points?

Jul 3, 2013

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?

View 2 Replies View Related

Macro -Delete Decimal Points & Add 0's

Nov 18, 2008

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.

View 3 Replies View Related

Removing Decimal Points From A Number

Jul 17, 2007

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

View 6 Replies View Related

Stopping Zeroes From Being Dropped After Decimal Points?

Feb 10, 2014

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?

View 4 Replies View Related

Changing Default Number Of Decimal Points?

Oct 24, 2012

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

View 5 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

Jul 25, 2006

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 Related

Change Default Decimal Points For Numbers

Feb 14, 2008

I'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.

View 9 Replies View Related

Combining Simple Formula With Text Concatenation Produces Too Many Decimal Points

Feb 18, 2014

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.

View 9 Replies View Related

Number 3.5 Is Getting Rounded To 4 In Procedure

Apr 17, 2009

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 Related

VBA Add Rounded Corners To Chart?

Dec 6, 2011

How 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].....

View 2 Replies View Related

Discount Applied To A Cell And Rounded To Nearest Nine

Jul 10, 2014

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)

View 3 Replies View Related

Search Timestamp Value Giving Input With Date And Time Rounded Off Value Alone

Jul 29, 2013

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

View 2 Replies View Related

Points Property Select Multiple Points

May 2, 2007

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

View 2 Replies View Related

Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing

Aug 13, 2009

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 Related

Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value

Aug 3, 2006

Basically, 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 Related

Removing Decimal Point While Maintaining # Of Decimal Places

Jun 10, 2009

I 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 Related

Conversions Of Decimal Feet To Decimal Inches - Formula?

Apr 15, 2012

I 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

View 7 Replies View Related

Convert Or Format Decimal To X Digits Without Decimal Point

Aug 8, 2008

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.

View 2 Replies View Related

Calculate Decimal Portion Of Decimal Number`

Jun 10, 2007

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 Related

Rounding The Prices

Dec 15, 2008

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

View 9 Replies View Related

Total Weekly Prices

Sep 13, 2009

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 Related

Historical Stock Prices

Jul 24, 2008

how do i download historical stock prices for approx a hundred stocks i.e what code would suffice?

View 9 Replies View Related

Update Ticker Prices

Feb 9, 2006

I 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?

View 9 Replies View Related

Even Distribution Of Units Across All Prices

Sep 15, 2007

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.

View 9 Replies View Related

VBA VLookup - Add All Prices For Certain Product In Different Tabs

Oct 16, 2012

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

View 3 Replies View Related

Adding Prices On Top Of VLOOKUP Formula

Nov 21, 2007

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

View 10 Replies View Related

Comparing Items And Calculating Prices

May 5, 2009

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.

View 12 Replies View Related

Sort Out All Bid Prices For A Specific Security

Jan 4, 2006

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.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved