Formula that sums then divides automatically by the number of rows below it

[halesowenmum]

Hi. In F36 I have this formula: =SUM(F37:F41)/6

It adds up all the % complete figures that users have manually entered in the rows below (in this case, there are six categories where they have to provide a % complete value).

That then gives me the overall % progress for all of the tasks so that when everyone has entered 100%, the total in F36 would also then make it to 100% as well.

However, the number of categories that need to be assessed could fill 3 rows sometimes, or 9 rows another time.

Is it possible to have a tweak to the formula shown that 'automates' the /6 part so I don't have to manually edit this formula every time I add a row or delete a row?

This formula then is next seen in F42 - so my question is, is there any way of telling it, when choosing what number of rows to divide by, to only include the values from the rows directly below this cell and up to and just before the next instance of this formula, however many, variable numbers of rows that might be?

 

[AlphaFrog]

Try this...
=SUM(F37:F41)/COUNT(F37:F41)

or....
=AVERAGE(F37:F41)

 

[halesowenmum]

Thank you AlphaFrog - however (and bear with me, I'm not anything even close to being an Excel expert!) but wouldn't that see me still having to manually dictate which rows were / weren't included? Which is kinda what I'm trying to get away from (iyswim). I need it somehow to 'know' what the rows are that should be included....

 

[AlphaFrog]

It includes cells in F37:F41 that have numbers. If you insert or delete a cell within that range, the formula automatically updates the range.

Why did you divide by 6 when F37:F41 is 5 cells?

 

[halesowenmum]

It includes cells in F37:F41 that have numbers. If you insert or delete a cell within that range, the formula automatically updates the range.

Ok, so regardless of where I pasted that formula, it will only include the values entered, if it hits a row with a formula it will stop at the preceding row and make that the last value it includes.

Why did you divide by 6 when F37:F41 is 5 cells?

Here you see the issue with the formula that I was previously provided with - rows get added and taken away and it's difficult to keep up with going back and manually adjusting them all the time.

Thank you. Now I can see what the formula does - you should bear in mind that the person asking the question may not automatically know HOW a provided formula works and thus may have a further question.

I've attempted to test It pasting your formula into F32. Below F32 there are 7 rows (33 thru 39) with the manually entered values in them.
I set each value to 10% and the formula returns 6% - is that right? I then further test it by manually setting all the values to 100% (ie every one has fully completed their individual elements) but the formula in F32 only gets up to 60% - if all contributing elements are 100%, the grand total should also be 100%.... hope this is clearly enough described for you to see what I mean.

 

[AlphaFrog]

I've attempted to test It pasting your formula into F32. Below F32 there are 7 rows (33 thru 39) with the manually entered values in them.
I set each value to 10% and the formula returns 6% - is that right? I then further test it by manually setting all the values to 100% (ie every one has fully completed their individual elements) but the formula in F32 only gets up to 60% - if all contributing elements are 100%, the grand total should also be 100%.... hope this is clearly enough described for you to see what I mean.

I don't know where we diverge, but I cannot duplicate this. I have to go to work now. Can't continue, sorry. Perhaps someone else will pick up from here.

 

[halesowenmum]

Can anyone assist with this - stating 'take rows 37-41' (or any other stated set of rows) like this isn't making this formula work because that has got to be changed every time it occurs. I love the second part of the formula where it does 'count' the rows that are involved so it always divides by the correct number, so my question is, is it possible to 'automatically' identify the rows in the first part of the formula (as described in my original question) because if I've got to define manually which rows then I may as well just define them manually as I was doing then divide by 3 or 6 or whatever. It's somehow got to be able to figure out which rows to include and how many rows to divide by, 'automatically' without the manually entry of the rows involved - am really hoping someone can assist with this. I kinda feel it can be done but I just don't know how - rows within the range involved could be added anew or removed so it needs to be dynamic to those types of changes.

Thank you.