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
SUMPRODUCT is more powerful than functions such as AVERAGEIF. For example, start with the following data in column A.
The following formula finds the average, so 23 divided by 3 equals 7,66.
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.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.
Perhaps you are trying to do something like the following. It averages all rows from the current row upward, if they are numbers.