0

How do I write a formula that will auto start sum from the first cell with value though to the end which including blanks, and then divide the total by number of cells from the one with value through to the end(including the blanks

Jan    Feb    Mar    April    May     June    July    Total    Comment
       200            10      90              50      0.6667  divide by 6 months
100    200            10      90              50      0.7143  divide by 7 months
                      10      90              50      0.7500  divide by 4 months
50     200    50      10      90              50      0.8571  divide by 7 months
       200            10      90              50      0.6667  divide by 6 months
                              90      100     50      1.0000  divide by 3 months
       200                    90              50      1.0000  divide by 3 months
              200     10      90              50      0.8000  divide by 5 months
1
  • 1
    Shouldn't the 7th line be 0.5 because there's Feb, Mar, Apr, May, Jun and Jul (6 months) and 3 months with values, so that we have 3/6 = 0.5?
    – Jerry
    Jan 23, 2014 at 15:44

1 Answer 1

1

You can use the following formula:

=COUNTA(A2:G2)/(8-MATCH(0,(A2:G2="")*1,0))

Entered as an array formula (i.e. Press Ctrl+Shift+Enter instead of Enter after inputting the formula).

COUNTA(A2:G2) will get the number of cells with values.

8 in the denominator is the number of months (Jan - Jul) plus 1.

MATCH(0,(A2:G2="")*1,0) is the array formula here, which will find the first position of the non-blank cell. 8 minus this position will give you the number of cells from the first non-blank cell to the last cell, including blanks.

3
  • 1
    +1 for not getting sucked into thinking it was a sum / count (avg) problem like I was trying to solve. ie, 200+10+90+50/6
    – panhandel
    Jan 23, 2014 at 15:54
  • @panhandel Yea, that's what I initially thought, but the results were too small to be using the actual values!
    – Jerry
    Jan 23, 2014 at 15:56
  • @user292654 You're welcome. Could you mark the answer as accepted if it solved your problem please :)
    – Jerry
    Jan 27, 2014 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .