I'm attempting to create a spreadsheet to calculate various things at my work, however I'm running into a problem that I can't solve. In one cell, I have a formula that returns an value to two significant figures. The number of decimal points that this value has is then used at various points throughout the sheet to truncate/round/etc. However, if the first value has a trailing zero as the second significant figure (ex. 0.20), that trailing zero is dropped. This creates problems since downstream calculations are then truncating to an incorrect number of decimal points. How to retain this trailing zero IF the formula results in one?
I have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).
example: Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.
The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.
Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?
Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself.
I cant seem to figure out how to concatenate data from two cells into one cell and retain trailing zeros. If Cell A1 has ".0000" and cell A2 has ".0005" then I want cell A3 to show: ".0000-.0005".
I get "0-.0005" on my attempts. Alos, how do I make the value an actual number and not a formula?
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases
The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"
I have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.
I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.
The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.
Just an example of some what some of the values look like on my spreadsheet:
.123456 | .123450 | .012345 | .012340
Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"
I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.
When i put a "0" in a currency formatted cell, the result is a blank cell. If i put any other number in there other than "0" it shows a dollar amount of that value (even .00001 works). I have also noticed that before i put a zero in that cell, i can go to the format of that cell, select currency, and i see a sample in the sample box ($0.00). But if i go to that cell, put a "0" in it and return to the format of the cell, the sample box is now blank and it doesn't even show a $ sign. I know the "0" is in the cell because i can see it in the formula bar. But on the sheet itself it does not display it.
Is there a fomula that can add trailing spaces. For example....I need a certain feild to be 11 Characters in length. So lets say in Column A, I have a bunch of different words:
House Bird Up Down Yes No
I would like to put a formula in column B that takes my original text and adds blank spaces to the end of it until it's 11 Characters. So "House" in Column A would be "House " in column B.
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I am linking multiple workbooks together - the problem I am facing is that the cells that do not have any data inside the source workbook result in a "0" on the destination workbook. Currently I am using the paste link option. Here is the purpose of my workbooks. the "core" document will have multiple worksheets each pulling its data from single worksheet workbooks. These workbooks will contain current project status and will fluxuate in length.
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
I have A1 that contain numbers, B1 that contain number, and i want to multiply those 2 and at the end of result i want to add zeros on front. Let me give you an example:
A1 field has data :00.375B2 has data: 6.49 C1 has the multiply result of A1+B1: 2.43 (But i want when the calculation is done and have the result 2.4 to add 2 zeros on front and become: 002.43 or if the result is for example: 65.20 to add 1 zero on front and become 065.20 , and if the result is 102.20 do not add anything on front.
So in total i need to have the 5 digits of number.
I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.
For Example,
if number is 1 then make it 0001 if number is 11 then make it 0011 if number is 111 then make it 0111 if number is 1111 then leave it 1111
I have a sheet with many different cells containing formulas. When the sheet has no data entered yet, every cell needs to be blank, including cells that have formulas for data.
I've realized that I can't use the "Show a zero in cells that have zero value" option because if a cell has data entered in it and the result is 0, I sometimes need to see that 0 in the cell. Some cells I need to see the 0 result and some cells I need to see a blank cell if the result is 0.
I just can't figure out how to edit the formulas so they do what I need.
cell P11:
=IF(J11="","",INT((J11-10)/2)) If no data is entered in cell J11, then cell P11 should be blank. But if data is entered in cell J11 and the result in cell P11 is 0, then cell P11 should be blank.
I am writing a macro that populates some columns from other worksheets, and populates other columns with formulas. One of these formulas is a text string that includes a three digit number, with leading zeros if needed. The following formula works perfectly when typed directly into the cells:
But this formula triggers a error message "Compile error: Expected: end of statement" when inserted in my sub. The "00" is highlited when the error message appears.
Names Quarters Nickels Dimes Pennies Record exc 0s
John 0 1 2 3
[Code] ........
In my table above, I would like to use the countif formula to count records based on the person, but I don't want the count to include values if the sum of quarters, nickels, dimes, AND pennies equal 0.
I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?
{=10*log(10^(A1:A16/10))}
It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
In column A I have the following formula =CONCATENATE((YEAR(B1)), " - ", (WEEKNUM(B1,1)))
In column B I have a date.
I need column a to display in YYYY-MM format.
This works great from about mid-March thru the end of the year when the week number is 10 or greater. As an example when the date is 1/31/2012, column A displays as 2012 - 5. For sorting purposes, I need it to display as 2012 - 05.
=REPLACE(Track!B58,5,11,"") the reason behind this is that the info originally is entered into one cell like 25.0 x 32.0 and I wanted to use a part of the cell for column A and the other for column B.
I use the following CSA formula to give me the total highest intervals of zeros in a range. I understand how most of it works apart from one thing and that's how the data array and bins array give me the result.
H2:Y2=0 - Returns TRUE/FALSE in the array based on range having 0 or a value other than that - ie{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
COLUMN(H2:Y2) - Returns the column number {8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}
IF(H2:Y2=0,COLUMN(H2:Y2)) -Returns the column number for every TRUE value
IF(H2:Y2>0,COLUMN(H2:Y2)) - This does the same as the above steps but returns column numbers for every cell that contains a value greater than 0
MAX - Returns the biggest number within the array ie =MAX({3;0;0;0;0;0;9}) would return 9
What I need to know is how the FREQUENCY formula gives {3;0;0;0;0;0;9}
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
I have a s/s which is built on IF functions and references other sheets and I have used the s/s in the past without issue. However now I want to ammend the formula a little and excel will only display the formula itself, not the result of the formula. I have tried CTRL ~ to turn on/off the show formula function but this makes no difference.
My reason for changeing the current formula is that I need to turn a 2 (numeric) to 02 (which can be either numeric or text). This is the new formula.
I have a workbook consisting of 4 worksheets. Cell B5 in each of three of these sheets has the exact same formula (copy/pasted) referring to data in the other worksheet. In one of the three similar sheets cell B5 shows up as a blank. In the other two, this cell shows up as a zero. Since the formula in each of these cells is identical, I figured the difference in the way the result showed up was a matter of cell formatting.
The cell format for each of the three is indicated as ‘general’. However, with one of these, the ‘sample’ shown when you view the cell format shows as a blank while the other two show the ‘sample’ as a zero (0). How can I go about making all three cells appear the same, namely a blank?
I have already an existing formula to get only the amount from the previous cell. From a6 to a30 it contains the word "11/05/09 press release $100", so in b6-b30 this is the formula. =IF(ISNUMBER(SEARCH("press",A6)),RIGHT(A6,LEN(A6)-FIND("$",A6)),"") until b30
the problem is in b6:b30 the 100's were not a number you have to copy and paste to other cells using paste special then values then click the smart tag and convert as a number to get all the total from b6 until b30.
