zeros

Greetings, I've got a defined table in excel (when you convert a range into an actual Table). I need a button macro to dynamically fill in all blank cells of the Table with zeros even if we add more rows and columns later- then rerun the macro.

Hello, What would the formula be if I wanted to find an average but exclude all zeros and exclude the top and bottom 5 percent? I know how to exclude the top and bottom 5 percent but I cant figure out how to ignore zeros.

This is a stumper - hoping someone has an idea. I need to calculate average hours worked by an employee in a quarter so I have all of the hours separated by each week in columns. I don't want to penalize new hires for the zero hours in the columns prior to when they were hired. But I do want...

Hello, I have a problem with disapearing zeros.. my formula so far to copy proper numbers to other sheet is: Dim cellR As RangeDim lastRowR As Long, r As Long lastRowR = Range("J" & Rows.Count).End(xlUp).row r = 2 For Each cellR In Sheets(1).Range("J1:J" & lastRowR) If Len(cellR)...

Hello, I have a workbook that has several sheets that have data on them in the following format: 107.75|106.68|96.98 The data can be a single value, or pipe delimited and contain up to 99 instances of values. Is there an easy way to check that each value has the trailing 2 digits after the...

Hi All. I want to format negative numbers in brackets and format zeros as "-" not "0". Currently I display negative numbers in brackets using the below custom number format: #,##0;(#,##0) However using this format zeros display as "0" and I want zeros to display as "-". Appreciate any...

Hello, I was wondering how I can keep my trailing zeros when I reference a cell from one sheet to another sheet in a = text & value & value statement And the numbers of zeros is going to be different so setting it with Fixed isn't going to work, I don't think. I have 2 sheets Sheet1 Sheet2 in...

Hello All, I'm using the following formula but want zeros when 0/0, instead getting div/0# IF(G200<1,(G200-F200)/F200,IF(F200=0,"100%",(G200-F200)/(ABS(F200)))) How can I format this? This is for y/y growth just for context. Jenny

Good Morning, I am trying to use the following formula and I keep getting a #VALUE ! error: AVERAGEIF(Firm1:Firm70!E46, "<>0") I am trying to get an overall average for a particular survey question, but I want to exclude zeros. I have each firm's submission in a separate tab. Any clue what...

Hello all, I have in cells G9 the formula G9 = "0208888"&H9 So in H9 is a lookup formula. Then this is what I wanna do: When the number in H9 is one digit number , then place two zeros in front of it in the formula above. If two digits then place only one zeros in front of it. Thanks in...

Hi Folks, I am creating a file for import to a client's system. One of the values is an amount. The cell has to be 18 characters long with no decimal points (the pence or cents will be the last two characters). If the amount in Sheet1 cell A1 was £19.99 then the cell B2 in sheet 2 would look...

Hey, guys, I have the graph below and would like to find an easy way to get the zeros (interceptions of the X axis). Is there any way to know it? I have the data but there's no specific value for 0. How can I extract it? Thanks, ORoxo

I have two cells, let’s say, A1 and B1. They may hold blanks or zeros, I need a way to identify when both are blanks or both are zeros. So, I did this: D10=(1-(A1=””)*(B1=””))*(A1=0)*(B1=0) I test it this way: (A1=”” and B1=””) results in D10 =0, and (A1=0 and B1=0) results in D10 =1, both...

Hi everyone, I would like to take strings of numbers from one sheet arranged in Rows and put them all into one column on another sheet. Eliminating any duplicates, zeros and no numbers above 60. So I have numbers arranged as follows: D12:R12, D14:R14 D16:R16 D18:R18 D20:R20 D32:M32 D33:K33...

Hi Everyone, I am in need of an INDEX function that is capable of returning both blanks and zeros from a column of data. Normally an when and INDEX function comes across a blank it returns a "0". A lot of people on the forums seem to devise solutions to similar problems using IF statements...

How can I force a formula to pull in extra zeros pass the decimal point? I have a fixed formula that drops off all the ending zeros and adds a zero to the left of the decimal. The ending zeros show up in the cell that the data is pulling from, but only displays the zero left of the decimal...

I am creating a file that will be used to upload data to a website. the information is very specific in format. Specifically the text string has to be a specific length. This is a problem when I have an amount, because the amount is variable and therefore the leading zeros change with each...

HI All, I had a look around and couldn't figure out how to do this. I'm working on some legacy spreadsheets and updating the formula's to be more dynamic rather than fixed to accommodate when new data is added. I'm having a bit of trouble showing what my dummy data table looks looks like but...

Hi there, Instead of removing duplicates, I tried to count number of duplicated items using countifs. In column A, I have IDs of transactions (they are not unique), in B date, C description, D amount. In Column E, I wrote the following formula: =countifs(A:A, A1, B:B, B1, C:C, C1, D:D, D1) and...

Hello Experts! Help pls. I have below code in my workbook which formats the numbers in my pivot table to 2 decimal places (and no decimal for UK region) and turns zeros into "-". How can i add a line item to change also blanks (not zeros) with "-"? Can i incorporate it below or requires another...