Hi, I originally posted on Excelguru, but I am not getting any traction. I am hoping I can get an answer here, even if the answer is "Its not possible".
I need some help with a formula where I want to start the calculation range when the first cell with a value is found. I format this data so the cells in Yellow and Blue Cells are truly empty.
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 B:M. I only want to multiple the average of the final calculation range by(*) the number of Yellow (Blank) Cells in the that range.
Here is sample data:
FormulaHelp.JPG
Using =INDEX($B2:$M2,MATCH(FALSE,ISBLANK($B2:$M2),0)) - I find the first value in the Row 2 - H2.
How do I nest the index formula and add formula logic so the formula will start a new range at the first value found in any row, i.e. H2 in Row 2 below, with the new range of (H2:M2)?
Constants
*The last cell in any range I use for this calculation is Column M.
*The dates are from oldest to newest going left to right.
So I need to combine:
1) =INDEX($B2:$M2,MATCH(FALSE,ISBLANK($B2:$M2),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.
Thanks for any help.
Bookmarks