Forum Discussion
Which formula to use when i want to have return a value based on a range of cells having a value
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.
Charla74 Hi and thanks for your reply
I understand your thinking with the added formula in AV:AX however this doesn't work for Delivered sales average YTD. Could you explain why you have used these formulas please?
Many thanks
Hi - Sorry, you're right! There was a slight oversight on the YTD formula - attached has been fixed. As for an explanation of the formulas:
=IF(AND(ISNUMBER(A3),Q3>0),A3,0)
This looks at the week numbers in column A and also the value in column Q - If col A is a number (which discounts the subtotal rows) and col Q has a value greater than 0 it pulls in the week number.
=MAX(AV3:AV66)
This looks at the results of the above formula and picks out the largest week number in the column (so the largest week number with an input value >0 in column Q)
=SUM(SUM(Q3:Q66)/2)/MAX(AV3:AV66)
In this one (where I had the error) takes the sum of column Q (divided by two because you have the monthly totals), divided by the latest week number with a value in col Q (from above formula).
Hope this makes sense.
Charla74 Hi and thanks again
Still didn't work but I have sussed it :), the divide by 2 didn't calculate correctly as each month was an average and not a sum. So using your formula I have replaced Sum Q3:Q66/2 by adding each week and ignoring the month end.
Very much appreciate your time on this, awesome