Search code examples
functionif-statementlibreofficelibreoffice-calc

formulas in sumif or other something-if funtions in office

i am trying to use a function that returns true or false in the avgif function for criteria, for example i have this column in cells H2:H15 :

22,53
21,08
X
23,8
21,4
20,66
random string 1
21,51
Xaksjdjasd
23,45
19,9
27,76
22,4
oijf,mdknfsjp

and i'd like to calculate the average of the last x cells above (x is a number in cell O26 ), i tryed:

=IF(ISNUMBER(H2);AVERAGEIF($H$2:$H2;&ISNUMBER($H$2:$H100);$H$2:$H2);"X")
=IF(ISNUMBER(H3);AVERAGEIF($H$2:$H3;ISNUMBER($H$2:$H101);$H$2:$H3);"X")
=IF(ISNUMBER(H4);AVERAGEIF($H$2:$H4;ISNUMBER($H$2:$H102);$H$2:$H4);"X")
=IF(ISNUMBER(H6);AVERAGEIF($H$2:$H6;"ISNUMBER($H$2:$H104)";$H$2:$H6);"X")
=IF(ISNUMBER(H7);AVERAGEIF($H$2:$H7;IF(ISNUMBER($H$2:$H104));$H$2:$H7);"X")
=IF(ISNUMBER(H8);AVERAGEIF($H$2:$H8;ISNUMBER("");$H$2:$H8);"X")
=IF(ISNUMBER(H9);AVERAGEIF($H$2:$H9;ISNUMBER(CELL("address"));$H$2:$H9);"X")
=IF(ISNUMBER(H10);AVERAGEIF($H$2:$H10;"="&IF(ISNUMBER(CELL("address"));CELL("contents");"");$H$2:$H10);"X")
=AVERAGEIF($H$2:$H11;"="&IF(ISNUMBER(CELL("address"));CELL("contents");"");$H$2:$H11)
=AVERAGEIF($H$2:$H12;&IF(ISNUMBER(CELL("address"));TRUE();FALSE());$H$2:$H12)
=AVERAGEIF($H$2:$H13;IF(ISNUMBER(CELL("address"));TRUE();FALSE());$H$2:$H13)

outputs:

Err:510
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Err:510
#DIV/0!

(yes i am trying to use cell() as cell currently being evaluated by the function, i would like to know if this doable or if this is a bad approach to the problem)

i know avg()skips automatically every not number cell but i need the avgif to work becouse after that i would like to implement a function that averages the last five cells not counting the ones that are strings, for example if one of the previous last 4 cells was a string and x was set to 6 (counting current one) the function would average the current cell and at least 5 of the previous cells (or more if the fifth last was a string too)

thanks to everybody for the help

Solution

  • SUMPRODUCT is more powerful than functions such as AVERAGEIF. For example, start with the following data in column A.

    1              
2
random string
20

    The following formula finds the average, so 23 divided by 3 equals 7,66.

    =SUMPRODUCT(A1:A4; ISNUMBER(A1:A4)) / SUMPRODUCT(ISNUMBER(A1:A4))

    Breakdown:

    • SUMPRODUCT(A1:A4; ISNUMBER(A1:A4))
      • ISNUMBER(A1:A4) will return zero for any values which are not numbers, and multiplying by zero results in zero to be added for that row.
      • The other rows in A1:A4 will be summed, resulting in 23 in this example.
    • SUMPRODUCT(ISNUMBER(A1:A4)
      • SUMPRODUCT(ISNUMBER(A1:A4)) will add 1 for each row that has a number, resulting in the total number of rows with numbers, which is 3 in the example.

    Actually in the example above, the formula can be shorter because SUMPRODUCT excludes erroneous results.

    =SUMPRODUCT(A1:A4) / SUMPRODUCT(ISNUMBER(A1:A4))

    Perhaps you are trying to do something like the following. It averages all rows from the current row upward, if they are numbers.

    =IF(ISNUMBER($H15); SUMPRODUCT($H$2:$H15) / SUMPRODUCT(ISNUMBER($H$2:$H15)); "X")