Nesting Index formula to dynamically create new range in a

:help: Hi, I need some help with a formula where I want to start the calculation when the first cell with a value is found. I format this data so the cells in Yellow are truly empty.

Here is sample data:



I have a huge vendor data set every month to look for "missing" Invoices that have not been entered, so I must accrue for them. In the above data set I only want to accrue for Column N.
My range from $C3:$N3 represents 12 months, but this specific vendor didn't start business with us until April 2019 (201904). I do not want to accrue for any months prior to April 2019.

The result I am looking for is: 1 month * Average = $416.23 in the above data.

=COUNTBLANK($C3:$N3)*AVERAGE($C3:$N3) works fine when all cells are populated.

I have been trying to put together an index match formula, but to no avail.

Using =INDEX($C3:$N3,MATCH(FALSE,ISBLANK($C3:$N3),0)) I find the first value in the data no problem, Cell I3 in this case.

I just can't seem to work out how to nest and build the logic so the formula will use a new range starting at the first value found, i.e. I3 and new range = (I3:N3) and then complete =COUNTBLANK($I3:$N3)*AVERAGE($I3:$N3)

Any help would be greatly appreciated!! :help:

Try =sumproduct(--(C2:N2<>"")*(C3:N3))/COUNT(C3:N3)

If those cells are truly blank, AVEAGE(C3:N3) works fine

More Detail for formula

Thank you both for your replies. I think I did a poor job explaining exactly what I needed. Here is another look at a bigger data set.

I do not want to include in my final calculation range the blue cells. I need the final calculation range to start at the first value found in any row from Column C:N. I only want to multiple the average of the final calculation range by(*) the number of Yellow (Blank) Cells in the that range.

Using =INDEX($C2:$N2,MATCH(FALSE,ISBLANK($C2:$N2),0)) I find the first value in the row. Cell I2 in Row 2 below.How do I nest a formula and build the logic so the formula will start a new range at the first value found in any row, i.e. I2 in Row 2 below, with the new range of (I2:N2)?

Constants

• The dates are from oldest to newest going left to right.
• The last cell in any range I use for this calculation is Column N.

So I need to combine:

1. =INDEX($C2:$N2,MATCH(FALSE,ISBLANK($C2:$N2),0)) to find the first cell with a value in any row
2. Then create a new range starting at the cell found in #1 above
3. Then calculate =COUNTBLANK(New Range)*AVERAGE(New Range)

However, I am open to any solution, including Macros/VBA.

I am attaching the spreadsheet example. Thanks for any help.
View attachment FormulaHelp.xlsx

Ugh, I apologize, my original data set has an additional Column in Column A that I did not include when I copied the data into a new spreadsheet.

Please take that into account when reviewing my post. All Column C's should be Column B's and Column N's should be Column M's. Every column should be shifted one letter to the left.

I would add a helper column R for First Data Col with this array formula (i.e. commit with Ctrl-Shift-Enter, not just Enter)

=MIN(IF(B2:M2<>"",COLUMN(B2:M2)))

O2 then becomes =IF(N2="yes",COUNTBLANK(INDEX(A2:M2,1,R2):M2),0)

P2 becomes =SUM(INDEX(A2:M2,1,R2):M2)/(14-R2)[SUB][/SUB]