Free Microsoft Excel 2013 Quick Reference

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

values, but what about zero AND Negative?

Thanks!!

- How to average a column, but exclude zero AND negative values?
- How to average a column of numbers that are greater than 0?
- How to average a set of values but ignore zero and error values (#N/A)?
- How do I average a column without including zeros
- How to hide a column
- How to set a column to accept only a specified number of digits
- How to set a column to accept only a specified number of digits
- Anyone know how to detect a column or row insert?
- How to rename a column title in excel?
- How to read the column which include text and number in the excel via ODBC?
- How to put a formula in one cell and the result in a another cell
- Calculating Averages but excluding zero's
- How do I average a formula without calculating zero values?
- Calculating Averages but excluding zero's
- Average the numbers in a column but exclude some values based on other column data
- Average Dynamic Data But Exclude Zeros
- Using Excel to average a data set with exceptions
- Trying to add a column of numbers, but ignoring negatives
- How to average a varying amount ou numbers
- Formulas: (PLEASE HELP) how can i sum a column and get a N/A
- How to make a column chart with both axle X,Y in value
- Question regarding how to search a column and print a row(s)
- How to turn a column of numbers into interval range?
- How to "Protect" a worksheet but allow "Merging" of cells?

values, but what about zero AND Negative?

Thanks!!

that contain a number greater than 0. How do I do that?

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

any cells with a 0%, what formula would I use? Thanks!!!

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

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

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

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

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

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

How do I do this?

Thanks!

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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

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

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.

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

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

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.