Forum Discussion

Copper Contributor

Sep 06, 2021

Solved

Average of nonsequential range if adjacent values meet some criteria...

Hi 🙂 I am trying to construct a formula that will allow me to find the average of a nonsequential range of values, given the value in the cell to their immediate left is 1 and not 2 (where 1 rep...

excel

Formulas and Functions

Juliano-Petrukio
Sep 06, 2021
Press the Ctrl + Shift + Enter keys simultaneously

=AVERAGE(IF(A2:H2=1,OFFSET(A2:H2,0,1),""))

Noths760
2021-09-06-Microsoft-Excel-Forum-Average-Non-Continuous-Cells.xlsx11 KB

SergeiBaklan

Diamond Contributor

Sep 06, 2021

Noths760

You may use AVERAGEIF function (microsoft.com)

Noths760
Copper Contributor
Sep 06, 2021
...
- SergeiBaklan
  Diamond Contributor
  Sep 06, 2021
  Noths760
  
  As variant
  
  =SUMPRODUCT(ISODD(COLUMN(A2:G2))*(A2:G2=1)*B2:H2) / SUMPRODUCT(ISODD(COLUMN(A2:G2))*(A2:G2=1))
  
  average confidence.xlsx12 KB
- Juliano-Petrukio
  Bronze Contributor
  Sep 06, 2021
  Press the Ctrl + Shift + Enter keys simultaneously
  
  =AVERAGE(IF(A2:H2=1,OFFSET(A2:H2,0,1),""))
  
  Noths760
  2021-09-06-Microsoft-Excel-Forum-Average-Non-Continuous-Cells.xlsx11 KB
  - Noths760
    Copper Contributor
    Sep 13, 2021
    Thank you so much - this worked nicely.