Forum Discussion

Skeletor35's avatar
Skeletor35
Copper Contributor
Jul 20, 2023
Solved

Why is this failing?

https://imgur.com/a/oQJG6HY   =AVERAGE(IF(D3:J3<>0 AND D3:J3<>"",D3:J3,""))   Trying to get an average for d3:j3, if zero or blank do not average, if "pto" use 9 instead. Started with bing faile...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 20, 2023

    Skeletor35 This one if you also want to take the "pto" into account:

    =AVERAGE(IF(D3:J3<>"pto",IF(D3:J3<>0,D3:J3,""),9))

     

Harun24HR's avatar
Harun24HR
Bronze Contributor
Jul 20, 2023

Skeletor35 If you are on older version of Excel then use below formula as array entry.

=AVERAGE(IF(D3:J3<>"",D3:J3,""))

Array entry means, after putting formula to cell, press CTRL+SHIFT+ENTER as the same time.

For newer version use AVERAGEIF() function.

=AVERAGEIF(D3:J3,"<>",D3:J3)

Resources