Is There An Easier Way?

Nickerz_2020 

Sorry, have no idea on which post you give reference as "my post @ 3:30pm above,".  Site shows my local time which is most probably not the same as for your timezone.

As for the formula you may find it in the file attached to my previous post.

Hi Sergei,

Looking back at my post @ 3:30pm above, I tried using several variations that didn't work but not sure if I was using them correctly. Can you reply with the exact formula you are referring to, then I can copy and paste it to know for sure?

The formula that is now in this cell now (I think this is the one as there were a few cells I was getting help with) is the following formula...

=IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/INDEX(FREQUENCY((S33,AC33,AM33,AW33,BG33,BQ33,CA33),0),2),0)

Thank you.

Nickerz_2020 

Not sure why do you have an error, I attached sample file to check the formula. In my case it works. Appreciate if you show what's wrong in it, will be useful. 

Yep...that worked too! Thank you again JMB! I tried this but saw in the one tried, I didn't close the parentheses at the very end. Very nice and have a good weekend everyone.

Try:
=Iferror((P34+Z34+AJ34+AT34+BD34+BN34+BX34)/J14,0)

JMB...you did it again! Yes, I copied and pasted your first formula and it worked! Thank you, thank you, thank you! I think I'm done with my report. It's a crazy spreadsheet that I've added values to over the years and it's become a beast. So again thank you to all for your help!

I'm sure I'll be back asking more questions so thanks also for your patience from a noob. 😃

If it is certain there will be no negative numbers, you could also try:
=IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/INDEX(FREQUENCY((S33,AC33,AM33,AW33,BG33,BQ33,CA33),0),2),0)

But, I assume this is part of a table? Is there a specific table header for these values that could be used to identify these values? Say the headers are in Row 1 and the specific header for these values is "x", then maybe you could something similar to this instead of dealing with a non-contiguous range (and, assuming all the values in the range are numeric):

=SUM((S1:CA1="x")*S33:CA33)/SUM((S1:CA1="x")*(S33:CA33<>0))

I tried copying and pasting your formula above and inserted my cells into the areas that said "values" but get errors. For example, I tried typing the following combinations...

IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/SUM((S33,AC33,AM33,AW33,BG33,BQ33,CA33<>0)),0))

=LET(S33,AC33,AM33,AW33,BG33,BQ33,CA33,INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/SUM(--(S33,AC33,AM33,AW33,BG33,BQ33,CA33<>0)),0))

last, I tried copying and pasting exactly what you typed...
=LET(values, INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),
IFERROR(SUM(values)/SUM(--(values<>0)),0))


I know it's hard to explain since my spreadsheet can be unique in so many ways but I'd like to calculate the different PPH (Presentations Per Hour) by adding them up (i.e., 1.09, 0.40, 0.22, 0.68, 2.03, 2.33, 0.00) and then give me the average of them all combined but without the average being skewed by the 0.00 as this decreases the real average.

So when I click on each of the cells with a PPH average, the correct average should be 1.12PPH but because it is counting the 0.00, it's bringing down the average to .96PPH - which I don't want. The sum should be 1.12PPH. Sorry if I'm confusing this even more.

Nickerz_2020 

Depends on your version of Excel. As variant

=LET(values, INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),
     IFERROR(SUM(values)/SUM(--(values<>0)),0))