Help with moving average and sum based on cell colour

[soundchaser99]

I was wondering if someone can help me with a few problems.

I am looking for a moving average that updates automatically as more data is entered, and uses a user defined figure to determine how far back to average.

The basics are as follows:

I have a list of weekly data (Week 1-52) contained in cells I7 - BH7.

These columns can contain either a number, a 0, or can be blank if I haven't reached that week yet.

AT the moment I am simply averaging all of the 52 columns, so if I have 26 weeks worth of data then I get an average of the 26 weeks.

What I want to do is to be able to place a number in a cell (doesn't matter where) which represents how far back (in weeks) the average works - so if I put 10 in the cell then the average for I7 - BH7 is calculated only for the last 10 columns, starting at the last column with a value in it.

So, if I have data in the first 20 columns, and the remaining 32 columns are blank, and I select 15 as my average, then the average will be calculated from column 20 back to column 5 (15 columns).

This would also need to update automatically as new data was entered, so that if I entered a value into the 22nd column, then the average would automatically adjust to move from columns 5-20 to columns 7-22.

I hope this makes sense


I also have another question which I might as well ask here too

Is there any way to get the sum of a number of cells, excluding those which are shaded grey?

I have a list of data in a column, some of which is not required for a particular report(hence the cell being filled in colour grey), and I need to total the sum of all the cells except those that are grey. Deleting those cells is not an option (I currently hide those rows when printing the sheet out so alternatively a formula that doesn't count hidden rows would also be good).

 

[pgc01]

Hi soundchaser99

If the number of weeks you want to look back is in I1 then the average of the last I1 weeks is

=AVERAGE(OFFSET(I7,0,MAX(IF(I7:BH7<>"",COLUMN(I7:BH7)-COLUMN(I7))),1,-I1))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

This formula assumes that you will not ask for the average of more weeks than the number of weeks that have data. So if you have data for only 5 weeks and ask the average of the last 30 weeks you may get an error.

It can be added to the formula. I didn't do it because the formula would be a bit uglier and maybe you don't need it. If you want it post. I'm leaving but someone will pick it up or give you a better solution or I'll do it tomorrow.

About the second question: I don't think you can do it with formulas only. You can use vba and check the background colour of the cells or if they are visible.

Hope this helps
PGC

 

[soundchaser99]

Thanks! that worked perfectly.

Not to be a pain, but how would I adapt this to a spreadsheet where the columns are not next to eachother?

I use this formula on another sheet to return the average of every third column starting from column I7 up to column FF7:


=IF(ISERROR(AVERAGE(IF(MOD(COLUMN(I7:FF7)-COLUMN(I7)+0,3)=0,I7:FF7))),0,AVERAGE(IF(MOD(COLUMN(I7:FF7)-COLUMN(I7)+0,3)=0,I7:FF7)))

Is there any way I can adapt this formula so it has the same feature as your earlier reply?

 

[pgc01]

Hi soundchaser99

I adapted your formula so that you can also choose in I1 how many weeks you want for the average.

These three formulas are array formulas and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

=AVERAGE(IF((COLUMN(I7:FF7)<=MAX(IF((I7:FF7<>"")*(MOD(COLUMN(I7:FF7)-COLUMN(I7),3)=0),COLUMN(I7:FF7))))*(COLUMN(I7:FF7)>=-3*(I1-1)+MAX(IF((I7:FF7<>"")*(MOD(COLUMN(I7:FF7)-COLUMN(I7),3)=0),COLUMN(I7:FF7)))),IF(MOD(MAX(IF((I7:FF7<>"")*(MOD(COLUMN(I7:FF7)-COLUMN(I7),3)=0),COLUMN(I7:FF7)))-COLUMN(I7:FF7),3)=0,I7:FF7)))

This method can also be used for yesterday's problem (contiguous cells instead of every third cell):

=AVERAGE(IF((COLUMN(I7:FF7)<=MAX(IF(I7:FF7<>"",COLUMN(I7:FF7))))*(COLUMN(I7:FF7)>=-(I1-1)+MAX(IF(I7:FF7<>"",COLUMN(I7:FF7)))),I7:FF7))

Finally an equivalent equation for the average of every third week given the number of last weeks in I1 using yesterday's method:

=AVERAGE(N(OFFSET(I7,0,-COLUMN(I7)-3*(-1+ROW(INDIRECT("1:"&I1)))+MAX(IF((I7:FF7<>"")*(MOD(COLUMN(I7:FF7)-COLUMN(I7),3)=0),COLUMN(I7:FF7))))))

After you test the formulas you may trap the errors with

=IF(ISERROR(formula),0,formula)


So you have 2 solutions for each of your 2 problems. You can either use the offset formula or the other.

Differences.
Formulas with the offset function:
- The offset function is volatile. This is a drawback.
- Are smaller and easier to read
- Give wrong result if you ask the average of more weeks that the ones that have data.

Formulas without the offset function:
- Are bigger, not so easy to read.
- If you ask the average of more weeks that the ones that have data give you the average of the existing ones. Ex: If you have data for 5 weeks and if I1=4 then you get the average of the last 4 weeks, but if I1=10 you get the average of the 5 weeks that have data.

I prefer the latter.

Please test the formulas and let me know if they work as you expect.

Hope this helps
PGC

 

[soundchaser99]

Thank you very much!

I ended up using the first two formulas in your previous post and they are working great!

Thanks again.

 

[soundchaser99]

Hi soundchaser99

If the number of weeks you want to look back is in I1 then the average of the last I1 weeks is

=AVERAGE(OFFSET(I7,0,MAX(IF(I7:BH7<>"",COLUMN(I7:BH7)-COLUMN(I7))),1,-I1))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

This formula assumes that you will not ask for the average of more weeks than the number of weeks that have data. So if you have data for only 5 weeks and ask the average of the last 30 weeks you may get an error.

Hope this helps
PGC

Hi,

Just an update on this topic. I have adapted this formula to some other spreadsheets and have come across a little problem.

Is it possible to change the formula to include a manual end date or cell reference so that it counts cell back from this end date?

In this formula:

=AVERAGE(OFFSET(H4,0,MAX(IF(H4:BG4<>"",COLUMN(H4:BG4)-COLUMN(H4))),1,-BJ$2))

Cell BJ$2 represents the number of weeks to look back (cell I1 from your answer)

If you have data in cell J4 and put for example "4" in cell BJ$2 then it counts back 4 columns from column J to column G, and therefore includes data from columns that shouldn't be included.

What I need is to manually tell this formula not only the period in which to look back (BJ$@) but also the starting week to look back from.

Is this possible?