• 580K Members
• 4,160 Online
• 701K Conversations
SOLVED

Highlighted
Contributor

# Formula with % and averages???

Hey guys,

I'm hoping for a solution to a wee problem...

I'm looking for the average of a number of cells.  There is a formula in each of these cells, some cells are populated with data and some are not.  The ones that are not, have the value of 0% displayed and will have until information is entered. Data won't be entered in for some time.

The problem I'm having is that the the average % being returned is including the 0%'s and thereby reducing the average.  Is there any way around this, do you think?

The story is related to percentage attendance at events.  7 in total throughout the year, so I'm looking to return the average attendance for the periods that have passed only, but I do what to have the formulas in the other cells in advance.

Your help would be greatly appreciated!

Cheers

9 Replies
Highlighted

# Re: Formula with % and averages???

Try this formula, =AVERAGEIF(a1:a100,"<>0")

Highlighted

# Re: Formula with % and averages???

Hi Rusty,

Thanks for this.  Not sure how it will fit in to the formula I have - here's what I've got:

=AVERAGE(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)  - yes, a big database!

I tried =AVERAGE(FE6,HJ6,JO6,LT6,NY6,QD6,SI6,"<>0") but it's returning a #value.

Any thoughts?

Highlighted

# Re: Formula with % and averages???

Use 'AverageIF', not just 'Average'

Highlighted

# Re: Formula with % and averages???

Afraid that's not working - 'too much arguments for the function'

???

Highlighted
Solution

# Re: Formula with % and averages???

@Alison Flynn , that could be

`=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)`

Highlighted

# Re: Formula with % and averages???

Try this:
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))
Highlighted

# Re: Formula with % and averages???

Hi,

Thanks a million for sending this formula on and for taking the time to do so.

I'm afraid it's not actually returning the correct answer.  I've been given a different formula that seems to be doing so:

`=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)`

Cheers

@Twifoo wrote:
Try this:
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))
Highlighted

# Re: Formula with % and averages???

Hi Sergei,

You have done it again!  That works perfectly.  Thank you so much, much appreciated.

Highlighted

# Re: Formula with % and averages???

Hi Alison,

You are welcome. Formula which @Twifoo  suggested also works if only you have blank or zero cells. If you have empty strings in your cells, e.g. returned by something like =IF(<A>,"",number), you may use it changing 0 on "". If you need to check both the variant could be .../((LEN(FE6)>0)+...

Related Conversations
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Leap year formula
Hattsoff in Excel on
4 Replies
FORMULAS
aayushman_mishra in Excel on
4 Replies