Get cell reference based on value

[Tadynn]

Hi all,

I have a spreadsheet that is laid out like below like below:

A B C D E F G->(AB)

8 Forecast: 1957 15200 1520 9000 6587 58874 8700
9 Actual: 1800 9000 1872
10 Variance: 157 6200 -352 0 0 0 0


The values in row 9 are keyed in daily. What I want to be able to do is a total called an estimated average that will
find the first empty row in row 9 and sum from the row field directly above, through to the last field.
Then this figure will be divided by the number of empty rows in row 9.

This is the formula that I have that will determine the empty value and get the value directly above.

=(OFFSET(AB$9,-1,CONCATENATE("-",COUNTBLANK(A$9:AB$9)-1),1,1))


But I am stuck on how to get it to sum from this point through to the last field in this row AB8.


Can anyone help?

Rgrds, Tadynn

 

[sfvb]

Tadynn,
Using what you already have:

    =(OFFSET(AB$9,-1,CONCATENATE("-",COUNTBLANK(A$9:AB$9)-1),1,1))

to get the sum, you just alter the formula to:

=SUM(

(OFFSET(AB$9,-1,CONCATENATE("-",COUNTBLANK(A$9:AB$9)-1),1,1))

:AB$8)

then to divide by the number of blank cells:

=SUM(

(OFFSET(AB$9,-1,CONCATENATE("-",COUNTBLANK(A$9:AB$9)-1),1,1))

:AB$8)

/COUNTBLANK(A$9:AB$9)

 

[Tadynn]

Thanks sfvb

I forgot to mention that I had already tried that, to no avail. I thought that I was doing something wrong but then read your post and realised that something else must be wrong.

Anyway, it turned out that one of my fields was actually summing the wrong field and it was causing a circular reference. Once that was fixed it all worked properly.

Thanks, tadynn