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

Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

This Week's Hot Topics

  • link pictures from Sharepoint to Excel macro
    Hi, I need your help to fix this issue. I have a code and this is work on my computer. But now I try to link the picture from the SharePoint to...
  • lookup match return closest to x date
    Hi all, Happy New Year, this is my first post in 2022. I have two worksheets I'm working with. worksheet one is titled KPI Data - MY22 ONLY It...
  • Average hours per week
    I'm trying to find a simpler/quicker way to obtain the average number of hours per week for employees. My spreadsheet contains columns for Date...
  • Google Sheets IMAGE Function
    Is there any way to replicate the IMAGE function from Google Sheets in Excel? Maybe a VBA code? From what I can tell, the function in Google...
  • Identify equipment used -No of consecutive days
    Hello, I appreciate your time and effort. I would like to have formula for column O and P. Column A : lots of Asset Column C: date If one Asset...
  • Filling in data from source
    Hi All, I'm not quite sure what to call the following type of data manipulation so I don't quite know how to phrase my question. Below is an...

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