Forum Discussion
Solved
Is There An Easier Way?
I have cells that show a % (i.e., 40%, 45%, etc.) for each day of the week (Monday-Sunday). I'm trying to get the average for the entire week but some days are 0%. I tried using the following formu...
- Do you have the AverageIf function?
=AVERAGEIF(I17:O17,">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.
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.
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.
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.