Excel Formula

Jorge Ocampo
Jorge Ocampo used Ask the Experts™
on
trying to get the average number ignoring all negative value, data and is in one workbook and the info should be in another.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Software Team Lead
Commented:
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/
Rgonzo1971
Top Expert 2016

Commented:
Hi,

since the question does not exclude 0s

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

Open in new window

Regards
Jorge Ocampo

Author

Commented:
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?
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Learn More!
Rgonzo1971
Top Expert 2016

Commented:
then try in sheet2

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

Author

Commented:
so it avoids any negative numbers?
Jorge Ocampo

Author

Commented:
i had to do this ">0" since this ">=0 gave me wrong information
Rgonzo1971
Top Expert 2016

Commented:
Your choice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial