Apr 22 2021 10:18 PM
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 formula...
=(I17+J17+K17+L17+M17+N17+O17)/7
but this formula calculates the 0% for some days, bringing down the average for the week which I don't want. If a cell is 0%, can I have the average not include this so it doesn't skew my average?
Thanks in advance!
Apr 22 2021 10:29 PM
Apr 23 2021 09:54 AM
Apr 23 2021 11:09 AM
SolutionApr 23 2021 11:47 AM
Apr 23 2021 11:57 AM
Apr 23 2021 12:17 PM
Apr 23 2021 12:28 PM
Apr 23 2021 02:05 PM
Apr 23 2021 03:04 PM
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))
Apr 23 2021 03:20 PM
Apr 23 2021 05:21 PM
Apr 23 2021 05:34 PM
Apr 23 2021 07:46 PM - edited Apr 23 2021 08:53 PM
And I'm back but I'm sure this is the easiest one. I just need to add the =IFERROR feature so it shows a 0 in the cell if there is no data in it. I know =IFERROR(P33/L33,0) is used in one of my cells but this isn't the one I need so I tried using the logic but can't get it to work for the following formula...
=(P34+Z34+AJ34+AT34+BD34+BN34+BX34)/J14
The error in the cell btw is...
#DIV/0!
Sorry, I just can't understand where place the ",0)" depending on the type of formula. ??????
Thank you in advance!
Apr 23 2021 09:23 PM
Apr 23 2021 09:30 PM
Apr 24 2021 02:32 AM
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.
Apr 24 2021 04:31 PM
Apr 25 2021 10:15 AM
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.
Apr 23 2021 11:09 AM
Solution