zeros

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

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

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.

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

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

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?

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!

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

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

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

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

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?

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

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

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?

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

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

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

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