I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
I have a problem with the currency format with 4 decimal places.
Sub go() Range("C5") = Range("C5") + Range("C4") End Sub as you can see it adds the conecnt of "R4" to what is in "R5"
If works fine when the calls are formatted as number with 4 decimal places. example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333
But if the cells are formatted as currency and 4 decimal places the then excel returns $0.3300
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.
I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)
I use datediff to calculate the difference between 2 dates in Months.
Result = Datediff("M", Date1,Date2)
I need the result to be formatted to 2 decimal places ie 23.52 (months)
Not neccessary to use datediff if this doesnt work.
Please note I have cross posted this HERE because I couldnt get connected to ozgrid for some reason, I will montior both sites for a solution and post back results. Apologies for the inconvenience.
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:...
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.
I am doing some calculations via Excel, and I have found that I get different results using a calculator. I know that by formatting the number, I can show as many or as little decimal places as I like in any cell, but can anyone tell me how many decimal places Excel actually uses when it's carrying out calculations? I have a feeling that the Excel results I'm getting might be more accurate than my calculator ones.
if I change the formatting of a cell to 2 decimal places, it appears as two decimal places (as it should) for example $88.88888 will show as $88.88
However, when I use this data in another application that displays this data it will display as 88.88888 still. I need to actually take the value and truncate it to 88.88 eg 8.8888888 will become 8.88
I have been using trunc by hand and wanted to try and find out if there is a way that I could write some sort of macro to do this for me each time.
How do I stop a user from entering more than 2 decimal places?
If I format the cell to "0.00" I can still type in 1234.1234 and it will display 1234.12. If I select that cell, the value in the formula bar is still 1234.1234.
Is there a function in VBA that lets me increment or decrement the number of decimals of a particular cell? let's say i got a cell value of 7.123456 but excel only displays 7 on the cell. What if i want excel to display only 7.123? How can I specify the number of decimals i want on a particular cell (in this case 3)
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.
I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?
I am taking a decimal degree (longitude) and converting it to binary then to hexadecimal. The value I am using is a Binary Angle 32 bit structure that will be translated into an 4-byte hexadecimal (with two characters per byte i.e. "20" is one byte and results in spaces).
This is what I have:
Input of 359.999999916 (in cell A2) Cells B2 through AG2 I have converted A2 into binary (using formulas like "=IF(A2-180>0,1,0)" for cell B2, "=IF(A2-180*B2-90>0,1,0)" for cell C2, "=IF(A2-180*B2-90*C2-45>0,1,0)" for cell D2, and so on)
I then concatenated all of the results into one cell using the following formula: =CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2,AF2,AG2)
To get a result of (located in cell A4): 11111111111111111111111111111110
However, when I attempted to use the BIN2HEX conversion on A4, I get a "NUM!" error. Here is the formula that I was using in cell A5: =BIN2HEX(A4,8)
I was going to use the following to test that a number has no more than two decimal places,
Code:
If Int(ActiveCell.Offset(0, 8) * 100) ActiveCell.Offset(0, 8) * 100 Then However it is rather mysteringly failing when activecell = 16.99 or 17.99; on testing it appears that vba is evaluating Int(ActiveCell.Offset(0, 8) * 100) to equal 1698 or 1798
I am facing a problem with Excel's Concatenate function. I am trying to make a text string with numbers from a cell. =CONCATENATE( "price paid= ", D23). D23 is a numreric cell, formatted for one decimal place. However, the text produced by above function is showing me two decimal places of the number in D23.
I am creating a Daily Cash Count worksheet for a business to do their daily closeout paperwork. What I am trying to do is automatically apply decimal formatting to a cell. Examples, if they count 60 cents in nickles and enter 60 in the cell, it automatically converts it to .60. And if they count 7.50 cents in quarters and enter 750 in cell it automatically would convert it to 7.50.
I'm trying to work out a formula in excel which requires me to use Pi to over 30 decimal places. Excel will only let me have 30 decimal places of Pi whether I copy and paste it as a number or use its Pi function and even then seems to round up. Is there any way I can get more than 30 decimal places for this calculation? If not in excel then can anyone suggest another programme that may be capable of this? You can post here or contact me at [email=" Deleted by Jack in the UK[/EMAIL]
Is it possible to apply some kind of formatting to a range, that will force all numbers entered into that range to have the same number of decimal places as the cell with the maximum number of decimal places?
Everytime I enter a number, excel automatically converts it to a decimal number.
I type 1 in a cell (or formula box) Excel returns the value as 0.1
I think it may have to do with the FIX box highlighted to the bottom of the screen. (To the right side of the NUM lock). (I can't paste a screen dump to show the FIX box position). how to remove the FIX box, and/or change excel so that I can enter in numbers normally.