1. D

    Eliminate #DIV/0! with IFERROR

    Hi there I'm having a brain freeze with an IFERROR to eliminate the #DIV/0! after a simple calculation and it's driving me insane... the formula is =(E108-C108)/C108 the result of course where there are zeros is #DIV/0!. I have tried a few variations unsuccessfully any help will be greatly...
  2. M

    Formula to Sort a Subset of Conesecutive Positioned Values of an Array with Constant Values?

    I am looking to figure out in Excel 2016 how to write a formula that will sort a subset of values in consecutive position within a larger array with constant values (zeros), but keep those zeros in their same positions thus keeping the number of values in the array the same? Moreover, an...
  3. R

    Zeros in a cell

    Hello, How can I keep different numbers of zeros in a cell, for example, 000 or 000000 or 00000000. When I use custom format, I am limited to the number of zeros that is customized but I need to have different number of zeros in a column cells. Thank you.
  4. 1

    Count streak of alternate 1 and 0

    Hi, I have ones & zeros in column C. Is there a formula that will show me a streak of alternate ones & zeros like at the bottom where there's six in a row? Thank you! <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> C 1 0 2 0 3 0 4 1 5 0 6 0...
  5. R

    Leading zero lost in Combobox

    I can't seem to find a way to save leading zeros in my Combobox list. The zeros are necessary since they correspond to unique IDs and are formated as text in the worksheet. These unique numbers may appear more than once so I'm using this portion of the form to narrow down my selection. The...
  6. Y

    Format number digit

    I Use zeros to format the number of digits i want when generating numbers, i.e 000 will display 054, 001, etc. how do i code it on a userform to just type the number of digits i want directly instead of using zeros, i.e typing 5 in a textbox and it will generate numbers like 00045,00004,00569, etc?
  7. E

    How to calculate the median of sums ignoring zeros?

    Hello I have the formula =MEDIAN(SUM(A1,A2),SUM(A3,A4)) and I want to ignore the zeros if any of the SUMs is zero. How can I do this please? I tried =MEDIAN(IF(SUM(A1,A2)<>0,SUM(A1,A2)),IF(SUM(A3,A4)<>0,SUM(A3,A4))) entered as ARRAY but it did not work. Thanks!
  8. D

    Rank Function without Zeros

    I am needing help on a formula that ranks a set of cells. some will have data and some will not depending on the time the report is ran. I am attempting to exclude the data with zeros and no data from the rankings. Current formula used : =IF(Q6=0,"",RANK(Q6,$Q$6:$Q$20,1)) <colgroup><col...
  9. K

    Autofilter out Zeros

    Im trying to auto filter out zeros on a sheet when a cell is updated. Is there a simple VBA that can do this? Also If a cell is blank, I want to hide certain rows on a separate sheet. is there another for this? Thank you
  10. V

    SUMIFS less than date AND amount is more than zero

    I am trying to do a sum based on two separate criteria: =SUMIFS(A3:A163,"<=30/06/2006",C3:C136,">=0",c3:c136) Col A = dates Col C = Amounts (there are negative and positive amounts) So basically, I am grouping financial years (Aus) that are not negative numbers (invoiced amounts) and a...
  11. C

    Moving Average

    Hi, Hoping someone could help me with the following please, I am looking to find the average of a range of numbers (determined by the variable in the 'weeks average' cell) So in the below examples, I want the average of the last 5 weeks for each set of numbers. I want to ignore any zeros...
  12. S

    Power Query is ADDING in leading zeros when convert to text

    I have a column with both numbers 1-99 and a few alphanumerics like U08. Import throws an error because "U" isn't a number. However, all my numbers now have leading zeros, 3 is now 003. Can I convert to text while maintaining the original numbers?
  13. K

    VBA: moving an array but keeping the format unchanged

    I have a list of suppliers codes, most of them are in text format like ABC, DEF, XYZ however I have a few that are just numbers, 12456 6789 00056 This last one is causing me problems as the leading zeros are lost I am moving the data, trying to avoid a copy paste...
  14. Z

    How to work with leading zeros and vlookup

    Help needed...again... I have a sheet that has 5-digit number codes in column B. I am trying to use a vlookup in column A to pull in names from a named table "CP_NAMES" in another workbook "Freeze Code Template". Column B sometimes has leading zeros (1 or 2), which makes the vlookup give me...
  15. E

    Right/Left Formulas and dropping leading zeros from result

    I am using the =RIGHT('Q-Tools Data'!A2,4) function on this data: 4004050.0001 and the result is 0001 How do I get the result to be 1 instead and drop leading zeros?
  16. T

    How do i cocantenate with leading zeros and a hyphen

    I have a large spreadsheet where I have one column with a 6 digit number and the next column with a single digit number. I wish to cocantenate them but need 2 zeros to the left of the 6 digit number and 1 zero to the left of the single digit number and a hyphen between the two like so. Cell in...
  17. L

    The Evaluate function drops a leading zero

    Hi is there a way to use the VBA Evaluate function and have it retain a leading zero, if the zero is there? I don't want to add leading zeros I just want it to be kept if it already exists. Thanks L
  18. A

    Suppressing Zeros Macro

    I need help suppressing zeros on my worksheet. I created a formula =IF(ISBLANK(B8),"x",IF(OR(D8<>0,E8<>0,F8<>0,G8<>0,H8<>0,I8<>0,J8<>0,K8<>0,L8<>0,M8<>0,N8<>0,O8<>0,P8<>0,Q8<>0),"x","")) , then a macro that would apply the filter. Sheets("Forecast Detail").Select...
  19. T

    Macro to add if statment to every formula in a range

    Hi Everyone, Ok I hope this is posible, I inherated a doc from an idiot who doesn't care about looks and I have lots of ranges that have simple link formulas in. e.g.s "='EitWT'!D11" a lot of these have blanks and I have a sheet full of zeros, I know I can hide the zeros but for calculation...
  20. T

    Excel remove Leading zeros in Excel

Some videos you may like

This Week's Hot Topics

  • Problem in vba vlookup
    Dear All Master, The problem I mean is as follows : 1. I want to modify the vba code because it takes too long/very slow to vlookup in VBA code...
  • VBA Vlookup
    Please for help. Why it`s not works? [CODE=vba]Sub Check_equpment1_click() Worksheets("Production_program").Activate Dim ans1 As Integer ans1 =...
  • Nesting INDEX MATCH into SUMIFS
    I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!$N:$N, 'datasheet..... but as I am...
  • Extracting number from a range randomly
    Hello Expert, please kindly advise what is the best method to extract numbers from a range randomly I have a series of numbers in the range of A1...
  • Problem with If Formula
    I am using the following formula in cell A5. I would like if N5 is blank then A5 also return blank. If N5 is 0 or is greater than zero then equals...
  • Is there a way to make the day's name show up automatically in a header?
    I have a form I print out every day..and in the top right custom header I have the &[DATE], which automatically gives the current date in top...