May 24 2019 05:24 AM
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
May 24 2019 07:48 AM
May 24 2019 07:58 AM
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?
May 24 2019 08:25 AM
Use 'AverageIF', not just 'Average'
May 24 2019 08:54 AM
May 24 2019 09:30 AM
Solution@Alison Flynn , that could be
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)
Source is here https://www.exceldashboardtemplates.com/how-to-averageif-excluding-zeros-and-blanks-for-non-contiguo...
May 24 2019 09:38 AM
May 24 2019 09:48 AM
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)
Really appreciate your time.
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))
May 24 2019 09:50 AM
Hi Sergei,
You have done it again! That works perfectly. Thank you so much, much appreciated.
May 24 2019 10:04 AM
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)+...
May 24 2019 09:30 AM
Solution@Alison Flynn , that could be
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)
Source is here https://www.exceldashboardtemplates.com/how-to-averageif-excluding-zeros-and-blanks-for-non-contiguo...