Free Microsoft Excel 2013 Quick Reference

# calculation to ignore a 0 (zero) value

I have a series of calculations that I need to use the MIN formula. the
problem is that many of the cells can have a 0(zero) value and not just
blank. Is there a way to find the minimum value but ignore the 0's? Otherwise
the minimum is always 0.

Thanks

## Related Results

### How to average a set of values but ignore zero and error values (#N/A)?

Hi,
How to average a set of values but ignore zero (0) and error values (#N/A, #value!)? Been searching for a way to do this. Lots of forums on how to ignore zero value OR ignore error values but not ignore both. Appreciate the assistance.

Regards,
klchoy

### 0 (zero) values shown in line graph for empty values - (EDIT

I use a bar graph to display actual data and a line graph to display Budget data. Now, for obvious reasons, I only want to show data until the current month. The problem is that the line graph shows 0 (zero) values (which are the result of a formula like IF[ (A1="FEB-02";B1;"") ]and the bar graph does not show 0 (zero) values.
I get the following situation in a graph:
------ Jan Feb Mar Apr May
Actual 5 6 8
Budget 6 5 5 0 0
Why does the line not show the same behaviour as the bar ? I do not want the line to show 0 values or "" as 0 (zero).
BTW. Options-Tools-Graph shows: "Plot empty cells as: Not Plotted (leave gaps)"'
Apparently this only works for the Bar-Graph.
What can I do or is this just a peculiar habit of Excel2000 ?

### Formula to count the number of times a non-zero value appears three consecutive times

Hi All,

I am trying to create a formula that will count how many times a non-zero value appears three consecutive times. For example, the formula should return "4" in the following example:

Cells B1:P1 contain the following values: 111011111100111

The first string of "111" values count as 1 instance + the next string of six "111" values count as 2 instances because we have back to back "111"s + the last set of "111" values count as 1 instance = 4

Handling the string of six consecutive "1" values seems to be the biggest challenge.

Would appreciate any guidance! Thanks in advance.

### Function to find first non-zero value in a range

Hi!

I have sort of a complicated question (for me) and I was hoping that one of you could help me out.

Here's my example.

I have two columns of data (I'll call them A & B):

A | B
Phase 1 | Blank
Phase 2 | Blank
Phase 3 | Blank
Phase 4 | XXX
Phase 5 | Blank
Total | YYY

What I'm trying to do, is have the total (YYY) return the value 4 (as in 4 phases). I've tried using the counta() function, but it only works if I enter values for the other phases (which isn't always the case). And the match() function only works if there's an actual lookup value (which in this case it changes all the time). I'm looking for some sort of lookup function (or something like that) which will return the line number a non-zero value entered in column B. That non-zero value can be anything however.

Any help that you guys could provide would be greatly appreciated!

Thanks,

Paula

### Show zero value instead of a minus number

Hi all....very firsy post, bit of a noob..
I need help with a formula that will give me a 0 (zero) value
cell A1+A2 sum in A3....no problem here
Then in A4 I need a sum of another cell total lets say B1 taken away from sum in A3
e.g A1=10
A2=10
sumA3=20 if cell B1 is more than 20 it will give a negative number
I need A4 to show 0 or the positive number
tried for hours any help please

### Need V-Lookup to return a 0 not # N/A when data not present

Is it possible for V-lookup to return a 0 when no data matches? I can't use a closest match (or true). I need the Zero for formulas. If V-lookup won't return a 0 can I use a IF statment to say if data not present return a zero?

[ This Message was edited by: tigger1 on 2002-11-08 15:34 ]

### 0 VS #n/a

is it possible to alter this formula to return a 0 (zero) value instead of #n/a?
=vlookup(\$d\$3,'sales by month'!\$k\$4:\$q\$90,2,'sales by month'!\$k\$4:\$k\$90)

### Using sumif to sum a range of values

Is there a way to use sumif to sum a range of values based on two conditions.
In other words I would like to sum a range of values in col c if values in
col b are equal to "2006" and values f are equal to "proposed".
Thanks

--
hwy

### How to enter a null cell value in a formula

I am trying to effect a null cell value based on a formula. For example, for
B2, I might want to have B2 be NULL if A2 is NOT NULL, otherwise use the
value from B1). This might be written something like "=if(A2>"",NULL,B1)".

The intent is to allow the text value of A2 to extend into B2 (i.e. not
wrap). Entering a "" for the TRUE action does not allow A2 to extend into B2.

### Macro to increase a cell’s value based on a reference

I am making an Excel-based set of flash cards (attached). I need to make a macro to increase a cell’s value by 1 based on a reference, and a second macro to decrease it by 1.

The first sheet (List) has a list of words in German (column B) and the English translation (column C). Column A assigns a number value to each word, which is referenced by the sheet “Flashcards”. Column D is the “Score” for each word.

On the second sheet (Flashcards) cell C7 takes a random number from List.A:A which cell E7 uses to pull that number’s word in English or German.

On that same sheet, I have two command buttons. I would like a macro to add 1 to that word’s score (first sheet, column D) if you click “Correct”. The second macro would deduct 1 upon clicking “Incorrect”

(Note: this is a growing list so it needs to be able reference the entire column)

I’m a noobie to both VBA and this site so any help is much appreciated!

### Look for min value in a Column,but ignore #VALUE! / 0 (zero)

Hi all,

I have a formula "=MIN(C:C)" to look for the min value in Column C. is there a way I can tell the formula ignore any cell value in Column C that has either #VALUE! OR 0 (zero) value.

### Ignore ZERO values in PIE Chart

Hi, everyone,

I am trying to build a PIE chart from the hereunder values:
1, 3, 0, 0, 7, 0, 11

I need a way to force the chrat to ignore all the Zeros.

In a normal BAR or LINE chart I would have used the =NA() or #N/A instead teh ZEROs but in a PIE chart it seems to have no influence on the ZERO Lables.

Bottom line:
I am looking for an automatic way to eliminate displaying only the ZERO Values (Lables) and to present a PIE chart made of 3 pieces and 3 Value Lables.

Thanks, Michael

### From #N/A to "0" Value

I need the following formula to return a 0 (zero) when the value returns #N/A. I am fairly new at this...

=INDEX(\$A\$6:\$H\$95,MATCH("FPL",\$A\$6:\$A\$95,),MATCH(4,\$A\$6:\$H\$6,))

Thank you,

Jena

### Ignoring a blank cell (not interpreting it as a zero)

I am using Excel 2002 and am having an issue with a spreadsheet. Hopefully some of you can assist me.

I have generated a spreadsheet which allows a user to enter particular values along a row (total of 12 values in 12 cells). 5 days a week, only 6 of these cells are filled out, and 2 days a week, all 12 values are filled out. The cell values are automatically charted on a different page of the spreadsheet which allows me to track certain events over time. This is where I have a problem.

Everything is fine during the two days of the week that the entire 12 cells are filled out. However, when only 6 cells are filled out, it skews the data interpreting no entry as a 0 (zero). Is there anyway that I can enter in an equation or macro which would cause the charted data to ignore data that is not entered? Something like ignore data that is =0 or blank?

Thanks!

### How to hide rows in a Workbook with multiple sheets with zero valu

Hi,

I am trying to create a code to run on a workbook with several work sheets
to hide rows with zero values.

I have used the following code:

Sub HideRowsIfColumnDisEmpty()
Dim X As Long
Dim LastRowOfData As Long
With Worksheets("Functional SummaryTotal Risk")
LastRowOfData = .Cells(.Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If .Cells(X, "N").Value = 0 Then
.Cells(X, "N").EntireRow.Hidden = True
End If
Next
End With
End Sub

This works for one worksheet in a workbook, however I need this to work on
multiple sheets.

run successfully on multipule worksheets in a workbook.

Thank you.

Khurram

### CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES

Hi

I am completely stumped at the moment. I am trying to use an IF formula or
a COUNTIF formula to capture a specific cells value.

What I need is to determine whether or not a certain value in a cell is
equal to or lower than 0 (zero). If so, I want to use the value in this
cell. Alternatively, if it is equal to or higher than 0 (zero) I want this
value to be used in another cell.

Can anyone help me to figure the formula out for this? It would be
extremely helpful as I know it can be done. Its a matter of determining the
correct way to write the forumla.

Cheers

Paul

### How to omit zero values when calculating averages?

Hi,

I'd like to find a method that allows me to calculate an ongoing YTD average that omits any zero values present in the source data range.

Any zero values in the source data are not valid, so the purpose of omitting them serves to avoid my YTD average becoming skewed due to inclusion of the zero values.

Below is an example of the formula I'm using (pls see attached screenshot for further reference).

G23: =AVERAGE(L23)
G24: =AVERAGE(\$L\$23:L24)
G25: Etc.

screenshot - ss.jpg

Thanks for any assistance that can be provided,
Jason

### How to add a default value (if less than function) to a calculation

Might be the eastest thing in the world but I'm missing it

I can't seem to figure out how to do this in excel? FoxPro no problems, Excel and I have problems? Go figure.

Currently I'm dealing with this

=IF(C4="",0,IF(C4>=1,(B4+D4+E4+F4+H4)))

My problem is I want to add a minimum default value to this calculation.

If my return from this string is less than \$25 I want to return the value of \$25 otherwise if it is greater than \$25 I want to return the calculation of this string?

Any help would be greatly appreciated. Thanks!

### Is there a way to use a calculation as column identifier number??

Anyone know if there is a way to use a calculation to return a value into a column identifier number?? I am trying to to tell a column of cells to look at another worksheet, but return values based on a 4:1 block of cells. example =A ((14 -2) / 3) to call for =A4

So... worksheet1 has main set of information in single row format, and worksheet2 relates back to worksheet 1, but is set in rows of 4 cell blocks for every 1 row in worksheet1
thus... worksheet2 B2:B5 have to return worksheet1 B1 B6:B9 have to return worksheet1 B2 and so on down the sheet. table example below.

The formula below will return the row number i need to identify for each cell block- but i cant seem to figure out way to make the formula replace the digit after the column identifier

(ROUNDUP((ROW()+3)/4,0)

this is what will not work :-)

=Worksheet1!A(ROUNDUP((ROW()+3)/4,0)

I would love to get this working - help would be so much appreciated!!

Example

Worksheet 1
A B C D E 1 Fruit
Color
2 apple red 3 orange orange 4 cherry red 5 pear green 6

Worksheet 2
A B C D E 1 ID
Fruit
COLOR
ROW # NEEDED
2 yes apple red 1 3 apple red 1 4 apple red 1 5 apple red 1 6 yes orange orange 2 7 orange orange 2 8 orange orange 2 9 orange orange 2 10 yes cherry red 3

### Delete Sheet If Cell Value Equals 0

Hi all,

I have a macro that (1)copies a sheet, (2)renames them based on a list and (3)inserts them to the very end of the workbook. The copied sheets have a formula that draw data from other sheets. Sometimes the formula will calculate a 0 (zero) value in cell G62. For each copied sheet that has a 0 value, I want a macro that will go through and delete them.

All of the copied sheets are inserted after a sheet called "TOI." I don't know how to make a macro to delete a sheet based on a calculated cell value let alone tell it to stop at a certain referenced sheet (the "TOI" sheet).

Can you anyone help?

### Need to add a 0 (zero) to an incorrectly formatted time

Hi - I've been sent time in military format, but for times before 10:00 they have sent it as 9:00 instead of 09:30 which I need to sort it correctly in a Pivot table. What is the best way to add the zero? I was thinking something like
```
VB:
(A1="?:??",0&A1,A1)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
to look for the 4 characters and then add the zero, but it doesn't look like I can use wildcards or maybe my function is wrong. Any suggestions?

### How to hide zero values rows in a range of sheets

Hi,

I have a large Excel file which containts multiple sheets. Excluding a few summary sheets, all the remaining sheets are identical in terms of formula and layout and are for different customers. I need to find a macro which allows me to hide a row if value in cell e.g., B10 is zero. I need to do so for a range of rows on each of these identical sheets (e.g, rows 10 to 50 and hide zero value rows if the correspondending values in column B is zero. E.g., hide row 10 if cell B10 is zero, hide row 11 if cell B11 is zero, etc. until hide row 50 if cell B50 is zero).

I will then need the macro to be applied to a range of sheets in the notebook from sheets e.g. from RE0001 to RE0100.

Your help will be much appreciated.

### Sum To Ignore Negative Values

I am trying to figure out how to ignore a #VALUE! error in my OR formula. I am using this formula for conditional formatting and I don't want to correct the error because I am using the errored out cells as a fourth conditional format since you can only use three in excel. This is my formlua:

=OR(N4>0,O4>0,P4>0,Q4>0,R4>0,L4>0,Q4>0,K4>0)

How can I modify this formula so that the whole formula doesn't error out if one of the cells has #VALUE! in it?

### Min Function Excluding Zero Values & More

Disclaimer: My experience with and knowledge of Excel is very limited.
I'm not sure if what I'd like to do can be done with a simple function
or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect
them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only
A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There
are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution
in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift
+ enter. This works fine. This solution may not be needed if it can be
incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list
until the corresponding rows are populated a row at a time descending.
So, with A1:A6 containing non-zero values, I'd like to exclude from the
MIN function not only the zero values A7:A10, but A6 also (the last
non-zero cell.) Is there a way to simply find the first zero value
cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from
the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully
populated and row 7 gets its first piece of data. When A7 has a
non-zero value, this means that row 6 is now fully populated and should
be considered in the MIN(A1:10) function. At this point A7 should be
excluded even though it is no longer a non-zero value because row 7 in
not fully populated yet.

Any help would be appreciated.

Thanks,
--Robert-->

--
WeatherGuy
------------------------------------------------------------------------
WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254