How to construct an Excel formula that will average non contigous cells in the same column

My worksheet is used as a daily log containing such information as “Date”, “Actual Weight”, “Target Weight”, “Average Weight” “Actual Food” etc.  “Average Weight” is a calculated field and this is where I have the problem. – Quite often for a given daily input, data for “Actual Weight” isn’t available (there could 1-3 days missing data) .

I would like if possible to construct a formula that will only average the most recent contigous entries for “Actual Weight” as in:-

Date      Actual Weight      Target Weight    Average Weight   Actual Food …….

1/11 17      870                     855                   870                   85

2/11/17      865                     855                   867,5                80

3/11/17                                855                                           85

4/11/17      868                     855                   868*                 87

5/11/17      873                     855                   870.5                87

* Because of the previous missing data for “Actual Weight” I would like to restart the averaging process from this point, which would the case for every break in data.

This is perhaps a tall order but I would appreciate any guidance on the matter.

Kind Regards

M.

 

By: Malcolm Edgar

Leave a Reply

Your email address will not be published. Required fields are marked *