zeros

  1. A

    Button macro to dynamically fill in blank cells in Table with 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.
  2. B

    TRIMMEAN 5 percent and also exclude Zeros?

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

    Averaging the total in a range of columns but don't count zeros in leading columns only

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

    Serial numbers and ZEROS

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

    Check each delimited value to make sure it meets criteria

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

    Negative Number and Zero Formatting.

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

    Keep trailing any trailing zeros in formula in vba

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

    IFERROR in nested ifs?

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

    AVERAGEIF to remove zeros

    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...
  10. kelly mort

    formula to insert zeros in front of numbers

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

    Creating an FTP file. need cell to be 18 characters long with leading zeros.

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

    Zeros of a Graph

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

    How to differentiate zero from blank?

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

    converting rows of numbers to one column

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

    Index Function that Returns 0's AND Blanks

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

    Add Extra Zeros in a formula

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

    Add leading zeros

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

    Removing zero's from a formula array then using this array

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

    Countifs returning 0 for items being searched in where they exist

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

    How to Convert blanks to "-"

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

Some videos you may like

This Week's Hot Topics

  • Launch Script When Tab Is Opened
    Hello all! Is there a way to launch a script or message every time a particular tab (DATA1) is selected/opened? I know it can be done when...
  • VBA that can extract words that starts with # and ends with 2021.
    Hi Everyone, I am straggling to in searching for a vba code that will extract a string that starts with # and ends with 2021. The string has no...
  • Date update overflow error ‘6’
    Dear MrExcel community, I have a project search & update & add & delete can do. There are 32 column and 5 of them is date. When I search date...
  • VLOOKUP Formula ?
    Hi i am using this below but i need to return Column C on Detailed_view. You can see ,1 this returns D, but i need to return C before D. I tried...
  • conditional formatting the whole row
    Hello This is a question solved earlier by etaf. But I am getting some problem. I need help to correct the formula as it is not working in some...
  • Vlookup in vba
    Hi, I am new with the VBA, can someone help me, I have made this macro, but the loop across files in a directory runs only in cell G13, and I...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top