Forum Discussion

Stephen_Appleby5574's avatar
Stephen_Appleby5574
Copper Contributor
Oct 23, 2020

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 cel...
Formulas and Functions
wsantos's avatar
wsantos
Brass Contributor
Oct 23, 2020

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.

  • Stephen_Appleby5574's avatar
    Stephen_Appleby5574
    Copper Contributor
    Oct 26, 2020

    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

Resources