Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

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.
0
Jorge Ocampo
Asked:
Jorge Ocampo
  • 3
  • 3
1 Solution
 
Ryan ChongCommented:
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/
0
 
Rgonzo1971Commented:
Hi,

since the question does not exclude 0s

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

Open in new window

Regards
0
 
Jorge OcampoAuthor 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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
then try in sheet2

=AVERAGEIF(Sheet1!D2:D12,">=0",Sheet1!D2:D12)
0
 
Jorge OcampoAuthor Commented:
so it avoids any negative numbers?
0
 
Jorge OcampoAuthor Commented:
i had to do this ">0" since this ">=0 gave me wrong information
0
 
Rgonzo1971Commented:
Your choice
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now