Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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


Post your answer or comment

comments powered by Disqus
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!!

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?

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

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

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

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
View this thread: http://www.excelforum.com/showthread...hreadid=470697

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

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?

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

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
View this thread: http://www.excelforum.com/showthread...hreadid=555103

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

Thanks!

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.

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.

Thanks in advance for your time.

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


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

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

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

Please help - andyfurber@rydermarsh.co.uk

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?
Thanks in advance.

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

Adapt to suit ..
--
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.
>
> Thank you in advance for your support. Please reply to Thank you very much!
>
> Regards,
> Gary Braida
>
>

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

Thank you for your time.

Is it possible to Protect a worksheet but allow "Merging" of cells?
In my work book there are three sheets, one sheet I need to be able to merge or unmerge cells in two columns (F and G). The columns will either appear as one or two depending on the data being entered (Example: Numeric data either as a single entity, .500, or as a range .500 and .505).
I have not found anything allowing to merge and or unmerge while sheet is protected!?


No luck finding an answer? You could always try Google.