Reputation: 967
DailyLog.o3 through DailyLog.o55 all happen to have the value of 0 in them currently.
This formula in WeeklyLog.f3 works just fine and returns 0:
=IF(DailyLog.O3:DailyLog.O9 > 0, AVERAGE(DailyLog.O3:DailyLog.O9), 0)
This formula in WeeklyLog.f4 gives a #VALUE error:
=IF(DailyLog.O10:DailyLog.O16 > 0, AVERAGE(DailyLog.O10:DailyLog.O16), 0)
Just as a test, I entered 1 through 14 in cells DailyLog.o3 through DailyLog.o16. Now WeeklyLog.f3 returns "4" and WeeklyLog.f4 is still returning "#Value"
I simplified WeeklyLog.f4 and it still returns "#VALUE"
=DailyLog.o10:DailyLog.016
I also tried adding the formula to WeeklyLog.f5 through WeeklyLog.F8 with their appropriate ranges and all of those show "#VALUE"
Now I changed WeeklyLog.f3 and it shows "17.5":
=IF(DailyLog.O3:DailyLog.O16 > 0, AVERAGE(DailyLog.O3:DailyLog.O16), 0)
Upvotes: 0
Views: 2820
Reputation: 13820
It may be a bug, but to me it looks like a bad idea to use a range without an array formula or a function expecting a range.
Here is an alternative that does not require an array formula:
=IF(COUNTIF(DailyLog.O10:DailyLog.O16, "<=0"), 0, AVERAGE(DailyLog.O10:DailyLog.O16))
Upvotes: 1