I am trying to make an excel formula that averages across multiple ranges. I've tried 2 different methods but neither have worked 100%.
The first formula I tried is: =IFERROR(AVERAGE(IFERROR(VLOOKUP(B9,Week1!$B:$W,20,FALSE),0),IFERROR(VLOOKUP(B9,Week2!$B:$W,20,FALSE),0),IFERROR(VLOOKUP(B9,Week3!$B:$W,20,FALSE),0),IFERROR(VLOOKUP(B9,Week4!$B:$W,20,FALSE),0),IFERROR(VLOOKUP(B9,Week5!$B:$W,20,FALSE),0)),0)
This formula is averaging across multiple ranges, with an iferror to change the NA to 0s, the issue is that the 0s will be averaged in and cause the number to be inaccurate. I have tried adding the "<>0" to the end, but this just returned an error. I went through the formula evaluation and couldn't seem to pinpoint why the error was being returned. I have also tried changing the iferror to be a blank or to be a text character as I read online that the average formula will automatically ignore blanks or text. This did not work and just returned an error.
The second formula I tried is: =IFERROR(SUM(IFERROR(VLOOKUP(B8,Monday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Tuesday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Wednesday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Thursday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Friday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Saturday!B:W,20,FALSE),0)+IFERROR(VLOOKUP(B8,Sunday!B:W,20,FALSE),0)),0)/7
This formula sums the ranges and then divides by the number of days in the week. In theory, this would work, but our employees don't have data every single day of the week. We have been getting by by manually changing the 7 to the number of days they worked. This is not ideal of course because of how manual it is. I am out of ideas at this point and hoping to get some help. Any advice is appreciated, thank you!