Iris Stein
New Member
Hi,
I would appreciate anyone's help here. I have set up a dashboard using figures that sales sends me on a semi-daily basis. For some days I have actual figures (when sales sends them) and for the days that I don't have actual figures, I enter the average sales amount.
I am trying to automate the excel table to take the actual sales figure when it exists and when it doesn't I would like it to take the average of the last seven days for which there is actual data.
How do I create a formula that would recognize which cells to take the information from - especially since the cells change on a daily basis and an additional row is added every day. Is there a formula that can take this into consideration.
I have attached a sample file for reference.
I would appreciate anyone's help here. I have set up a dashboard using figures that sales sends me on a semi-daily basis. For some days I have actual figures (when sales sends them) and for the days that I don't have actual figures, I enter the average sales amount.
I am trying to automate the excel table to take the actual sales figure when it exists and when it doesn't I would like it to take the average of the last seven days for which there is actual data.
How do I create a formula that would recognize which cells to take the information from - especially since the cells change on a daily basis and an additional row is added every day. Is there a formula that can take this into consideration.
I have attached a sample file for reference.