Forum Discussion
Solved
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 ...
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-contiguous-ranges-in-excel/
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?
Try this:
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))
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))