Forum Discussion
Which formula to use when i want to have return a value based on a range of cells having a value
I need to return a value of ytd sales averages from a range of cells that may have data entered. currently in week 43 so weeks 1-42 will have data entered, i want to show the ytd average of these cells which will change as each week is populated
Thanks in advance
10 Replies
With permission from everyone, if I may add this example as an additional solution.
Mittelwert
Thank you all for your patience and understanding
Nikolino
I know I don't know anything (Socrates)
Stephen_Appleby5574 If you always average from week 1 through the current week, just fix the start cell. Ex: Average(a$1:a34). If there are no other weeks on the column that need to be filtered out you could average the whole column. It really depends on the actual spreadsheet content, so without seeing a sample it's impossible to tell.
wsantos Hi and thanks for your reply to my problem. I have attached the sheet for you. I would like to have a formula in cell D13 on Tab A1. This needs to return the current average of data entered in Column Q on Tab Franchisee 1. As you will see the raw data is weekly sales figures. So the data I would like to be returned is the average for all of the week's that have been entered.
I would also like a formula in Cell D6 on Tab A1 to return the latest figure entered in Column H on Tab Franchisee 1. This does not need to be an average but to return the latest week entered, so if the current week is week 44, and data has been entered, then return figure in cell H56. If data has been entered in row 58 then return the figure in H58
Thanks in advance
Hi Stephen - See attached. I added some helper formula in columns AV:AX on the sheet Franchisee 1.
It's better to use Excel Table, average will be automatically updated with adding rows to the table.
Otherwise that could be average on dynamic range which could be returned as A1:INDEX(A:A, COUNT(A:A)). This one only illustrates the approach, concrete formula depends on how your data is structured.
