Excel Formula

trying to get the average number ignoring all negative value, data and is in one workbook and the info should be in another.

Jorge Ocampo

Asked: 2016-07-25

Advertise Here

Microsoft Excel

1 Solution

LVL 56

Ryan ChongCommented: 2016-07-25

try use AVERAGEIF or AVERAGEIFS functions:

example:

=AVERAGEIF(D2:D12,">0",D2:D12)

Open in new window

try change the range accordingly.

more info:

How to calculate average in Excel - formula examples of AVERAGE, AVERAGEIF, AVERAGEIFS
https://www.ablebits.com/office-addins-blog/2015/08/28/excel-average-averageif/

LVL 55

Rgonzo1971Commented: 2016-07-25

Hi,

since the question does not exclude 0s

then try

=AVERAGEIF(D2:D12,">=0",D2:D12)

Open in new window

Regards

LVL 2

Jorge OcampoAuthor Commented: 2016-07-26

that part i got but what about getting the info from a different sheet for example data is in sheet1 but i am trying to display data in sheet2?

LVL 55

Rgonzo1971Commented: 2016-07-26

then try in sheet2

=AVERAGEIF(Sheet1!D2:D12,">=0",Sheet1!D2:D12)

LVL 2

Jorge OcampoAuthor Commented: 2016-07-26

so it avoids any negative numbers?

LVL 2

Jorge OcampoAuthor Commented: 2016-07-26

i had to do this ">0" since this ">=0 gave me wrong information

LVL 55

Rgonzo1971Commented: 2016-07-26

Your choice

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Excel Formula

Join & Write a Comment Already a member? Login.

Featured Post