: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: