Solved

Excel Formula

Posted on 2016-07-25
7
51 Views
Last Modified: 2016-07-29
trying to get the average number ignoring all negative value, data and is in one workbook and the info should be in another.
0
Comment
Question by:Jorge Ocampo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41728694
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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41728820
Hi,

since the question does not exclude 0s

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

Open in new window

Regards
0
 
LVL 2

Author Comment

by:Jorge Ocampo
ID: 41729443
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41729554
then try in sheet2

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

Author Comment

by:Jorge Ocampo
ID: 41729614
so it avoids any negative numbers?
0
 
LVL 2

Author Comment

by:Jorge Ocampo
ID: 41729626
i had to do this ">0" since this ">=0 gave me wrong information
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41729630
Your choice
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question