user2021539
user2021539

Reputation: 967

Why would this cause a "#VALUE" error in LibreOffice Calc

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

Answers (1)

Jim K
Jim K

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

Related Questions