Free Microsoft Excel 2013 Quick Reference

# How to average a column, but exclude zero AND negative values?

I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero
values, but what about zero AND Negative?

Thanks!!

## Related Results

### How to average a column, but exclude zero AND negative values?

I used the formula of =AVG(IF(A1:A10)0,A1:A10)) to exclude just the zero
values, but what about zero AND Negative?

Thanks!!

### How to average a column of numbers that are greater than 0?

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?

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

### How do I average a column without including zeros

I want to average a column that is all percentages, but I want to excluding
any cells with a 0%, what formula would I use? Thanks!!!

### How to hide a column

Hello,

I want to send out an excel file to some people, but I dont want them to see
the column c. I know how to hide a column, That is not what I need, I need
to have this column not accessible at all by the people who will receive this
file.

Is this possible?

Thank you Pascale

### How to set a column to accept only a specified number of digits

Hi,

Can u tell me how to set a column in excel to accept only a specify
number of digits? for example ... i want column A to accept only
numbers in the range 1-100. If 101 is type an error must be displayed

Thanks

--
ashvik
------------------------------------------------------------------------
ashvik's Profile: http://www.excelforum.com/member.php...o&userid=27543

### How to set a column to accept only a specified number of digits

Hi,

Can u tell me how to set a column in excel to accept only a specify number of digits? for example ... i want column A to accept only numbers in the range 1-100. If 101 is type an error must be displayed

Thanks

### Anyone know how to detect a column or row insert?

Does anyone know how to detect a column or row insert event other than
doing some complicated processing on every Worksheet_Change event? I'm
looking for something a little cleaner than that. I realize that this
might be a stretch. Thanks in advance.

Roy Berger

### How to rename a column title in excel?

How to rename a column title in excel?

### How to read the column which include text and number in the excel via ODBC?

Hi everyone,

Who can tell me how to read the column which include text and number in the excel via ODBC?
like one columnA include 'abcd'(Text) and 1234(number)
I want to read all the data in columnA, but seems that I can only read the data which the datatype is consistent with the begining of the data in the column.
For example. (please correct me if anything is wrong)
If the begining of the data are 'adcd', 'dfd-yrtd','yreh4645', the data type in this column was defined as text by default, and then I can't read the numeric data in this column if there is numeric (like 1123) data in the column.

I use the sql like: select * from ...
So I think I should read all the data in this column.

Thanks
Winnie

### How to put a formula in one cell and the result in a another cell

how to put a formula in one cell and the result in a another cell

### Calculating Averages but excluding zero's

Hi,

i want to calculate the average of a column but exclude anyzeros in
that column.
For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average
would be 2. (8/4) NOT (8/6)

Thanks.

--
fodman
------------------------------------------------------------------------
fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941

### How do I average a formula without calculating zero values?

I want to average a column, except for the cells that have zero as a value.
How do I do this?

Thanks!

### Calculating Averages but excluding zero's

Hi,

i want to calculate the average of a column but exclude anyzeros in that column.
For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6)

Thanks.

### Average the numbers in a column but exclude some values based on other column data

I'm having trouble getting a formula to work that will average numbers in a column while excluding some data based on the data in another column.

I think I'm having trouble because I'm seeing some formulas startout with =AVERAGEIF and others starting out with =AVERAGE(IF ...

[Well, to be honest, since I can't get either approach to work, maybe that isn't the problem.]

Anyway, here's the situation:
I want to average some of the numbers that appear in column AC (data in cells AC2:AC148) I want to exclude any numbers that correspond to rows that have an "y" in F2:F148.

### Average Dynamic Data But Exclude Zeros

Greetings

I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem. I would be grateful for any assistance and thankyou in advance.

```
VB:
SumBlock()
Dim First_Row As Long
Dim Last_Row As Long
Dim iTotalRows As Long
Dim iCount As Long
iTotalRows = Range("A65536").End(xlUp).Row
First_Row = 2
Do While Last_Row < iTotalRows - 1
Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row
iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")
ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"
First_Row = Last_Row + 2
Loop
End Sub

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

```

### Using Excel to average a data set with exceptions

Hey Everybody! I am stuck on a spreadsheet that I am writing and I would be forever greatful if someone could help me out. I have a set of data that I would like to average, but this data set contains zeros. I would like to have one cell average the data without including the zeros because it is throwing my number off. I will give an example data set below:

357
0
359
421
285
0
0
697
- I want to average the column but only the numbers that are not 0

Thanks very much,
Joshua

### Trying to add a column of numbers, but ignoring negatives

Hi,

I'm trying to SUM a column, but it has a negative number in it. I want to sum the column but ignore the negative number.

I.E.
5
46
12
-15
3

so the total would be 66.

How is this possible or would i have to use another formula instead of SUM.

Cheers,

John

### How to average a varying amount ou numbers

I want to include some code in a module to average a range of numbers, the problem is you dont know before hand how many numbers you will have.
When someone runs the spreadsheet they enter a number which will be the amount of figures to be averaged out, but I don't know how to use the active.formula with a Range(Cells(3, 4), Cells(11, 5)).Select type of command.
Can anyone help?
I know there isn't much to go on but please ask me what else you might need to know.

Thanks

Warren

can anyone tell me how to sum a column but if one of the cells is empty! the cell which should have sumed the numbers will display #N/A
A A
1 7 1 7
2 2 2
3 4 3 4
4 13 4 #N/A
i.e because A2 cell data is missing cell A4 does not sum the column it displays #N/A

### How to make a column chart with both axle X,Y in value

There are two series of data, which need to put in X & Y axle respectively to make a column chart. What I want is drawing the columns with X1-X2 wide in value Y1, for example:

XY05512013605100

first column will be 5 in width and 5 in hight, second column will be 15 in width but only 1 in hight, etc

Can anyone help me to do that, or is there any other program can do that?

### Question regarding how to search a column and print a row(s)

One non-array formulas play ..

Assume source data is in Sheet1, cols A to O, data from row2 down
Assume the key col is col B, with the criteria: >150

In an empty col to the right, say col Q
Put in Q2: =IF(B2="","",IF(AND(ISNUMBER(B2),B2>150),ROW(),"") )
Copy Q2 down to say Q100 to cover the max extent of data expected in the
source
(Leave Q1 empty)

Col Q is the criteria col which will simply assign arbitrary row numbers to
lines which satisfy the criteria. Col Q's returns will be read by the
formulas in Sheet2.

In a new Sheet2
-------
Copy > paste the same headers from Sheet1 into A1:O1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!\$Q:\$Q,ROWS(\$A\$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!\$Q:\$Q,ROWS(\$A\$1:A1)),Sheet1!\$Q:\$Q,0)))

Copy A2 across to O2, fill down to O100
(cover the same range as done in col Q in Sheet1)

Sheet2 will return only the rows from Sheet1 which satisfy the criteria, i.e
those rows with values in col B > 150. Result rows will be bunched neatly at
the top
---
If the criteria to be set in col Q is say:
where the text string "OK" appears in col B

Then we could put instead in Q2:
=IF(B2="","",IF(ISNUMBER(SEARCH("OK",B2)),ROW(),"" ))
and just copy down as before

(Change SEARCH to FIND if the case is important. FIND is case sensitive)

Sheet2 will then return the desired results ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik yahoocom
----
"Gary Braida" > wrote in message
news Hello,
>
> I have a spreadsheet consisting of 15 columns and 100 rows. I would like
to
> search a column for a specific string or based on some other criteria
(e.g.,
> > 150) and for columns meeting the desired criteria, I would like to
print
> the entire row (all columns) within the same worksheet or on a new
> worksheet. Can this be done using one or more formulas but without
getting
> into VB or fancy macros? Can this be done period? I'm looking for
> something that searches the column using the same or similar approach as
the
> "sumif" function but instead of summing the specificed range, I want to
> print the rows.
>
>
> Regards,
> Gary Braida
>
>

### How to turn a column of numbers into interval range?

I have a column of ages and need to create a column of interval age range.
I need to create interval of 5 for ages 0 up to 105 (about 22)

ie (How can I generate the second column?):
*note: I have many many rows of records.

Age
0
0
0
1
2
3
4
5
6
6
6

Age interval
0-4
0-4
0-4
0-4
0-4
0-4
0-4
5-9
5-9
5-9
5-9