IFS statement gets #N/A as result

English
#1

Hello,

I’ve quite new with libreoffice calc and I’ve having trouble with the following problem for over a week now.
I have 4 numbers that I need to do math operation with. First three numbers need to be average() and the 4th should be added to the sum after the average is caluculated. However, these numbers are also caluclated through inserting some data, so sometimes there is no data for a some of these numbers. If there are no numbers it says #DIV/0! which does not bother me but the problem lies when I need to caluclate the average of 3 + the 4th numbers when one or more numbers are missing. I get a #DIV/0!. So I found a function called ISERROR and I check if there is an error in the cell ignore the cell( meaning there is no data for that cell/number) and do the math with other data. I need to do all the cases in the IFS function but when I do 2 cases everything works fine after I enter 3rd case I get #N/A result in that cell - it doesn’t matter which cases I put, first 2 work good and then I get #N/A. If anyone can help me I would be really grateful. Also I’m aware that the table is probably horrible but I just started.

This is the function:

=IFS(AND(ISERROR(C39)=0;ISERROR(C84)=0;ISERROR(AF84)=0;ISERROR(AL84)=1) ;IF(AVERAGE(C39;C84;AF84) >=I1;I1;AVERAGE(C39;C84;AF84));AND(ISERROR(C39)=0;ISERROR(C84)=0;ISERROR(AF84)=1;ISERROR(AL84)=1) ;IF(AVERAGE(C39;C84) >=I1;I1;AVERAGE(C39;C84));AND(ISERROR(C39)=0;ISERROR(C84)=1;ISERROR(AF84)=0;ISERROR(AL84)=1) ;IF(AVERAGE(C39;AF84) >=I1;I1;AVERAGE(C39;AF84)))

However, when I put the 3rd case in a seperate cell it works fine… so I assume I’m making mistake somewhere in the IFS instead of case .

This works when put in different cell:

=AND(ISERROR(C39)=0;ISERROR(C84)=1;ISERROR(AF84)=0;ISERROR(AL84)=1)
=IF(AVERAGE(C39;AF84) >=I1;I1;AVERAGE(C39;AF84))

I’m attaching also the whole ods file if anyone cares to take a look. C39 is where the solution will be eventually but I36 is where I experimented with formula.

NORME-MOBERT-2019-lipanj.ods

Best regards,
Marko

(Slightly edited by @Lupp. Formulas ONLY set to ‘rreformatted text’! No mcharacters changed / deleted / inserted.)

#2

Reduce the complexity of your formulas. Otherwise you will waste time by the hours again and again trying to fix errors which may be caused by combinations of syntactical, semantical reasons, and -probably- bugs.

IFS() was implemented only for LibO Calc V 5.2 or higher trying to get more campatible with Excel 2016 (a dubious version). The function is not specified for ODF, and seemingly there is also no help text. I would suggest you don’t use it.

However, there is bug tdf#124710 likely related to your issue. It recently was taken by the original implementor of the function.

#3

I will try to make it less complex but I don’t see any other logic to do it.I’ve check the bug,yes it seems indeed that it is the reason also here. Thank you !

#4

Possibly simply editing C84 into something like =IFERROR(SUM(C52:C82)/SUM(R52:R82);0) (and other cells likewise) would already allow to greatly simplify things.

#5

And by the way, the complex formula is equivalent to

=MIN(I1;IFERROR(AVERAGE(C39;C84;AF84);IFERROR(AVERAGE(C39;C84);IFERROR(AVERAGE(C39;AF84);0))))

with much less overhead - see that each expression is only calculated once, and many checks are just eliminated (given that AL84 is always error - an invariant in your formula; and also that I1 >= 0).

Or - better matching your formula wrt AL84:

=ISERROR(AL84)*MIN(I1;IFERROR(AVERAGE(C39;C84;AF84);IFERROR(AVERAGE(C39;C84);IFERROR(AVERAGE(C39;AF84);0))))
#6

My idea would be to consolidate all the subtotals for your blocks in one place, like in C93 and below, so that in C93 would be the formula from C39; in C94 would be what is in C84; AF84 would go to C95; and AL84 would go to C96. This way, you’d have a vector of values. Then, your whole formula could be simplified to

=MIN(I1;AVERAGEIF(C93:C96;">=0"))

Note that AVERAGEIF even filters out the error values from its dataset.

#7

Possibly simply editing C84 into something like =IFERROR(SUM(C52:C82)/SUM(R52:R82);0) (and other cells likewise) would already allow to greatly simplify things.

As a final thought in this series, I’d even simply used

=IFERROR(SUM(C52:C82)/SUM(R52:R82);"ERROR")

returning text in case of error, right in the same place where it is now, and simplified formula in I36 to be

=MIN(I1;AVERAGE(C39;C84;AF84;AL84))

so even no rearrangement of data would be needed, since AVERAGE ignores textual values in its arguments.

#8

Thank you so much. Here is what I used: =IFERROR(SUM(C52:C82)/SUM(R52:R82);“ZERO”) and I had to change “ZERO” to 0 in AL84.
Because I used your formula and adjusted it cause AL84 should be added and not averaged with others: =MIN(I1;AVERAGE(C39;C84;AF84)+AL84) . Because AL84 was adding it wrote 0 if there is an error instead of “ZERO” as to others and it worked out. Once again thank you so much for the help. I’ve been stuck with this problem for over a week

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.