Forum Discussion
How do I reference a range of cells using a function?
I am trying to use a formula where a function will return a cell reference to then use in another function of the same cell. The formula that I am trying to use is =average(vlookup(K9,A:B,2,false):vlookup(L9,A:B,2,false))
I am trying to get excel to look up a range of dates and then give me the average of the values that are right next to those dates.
1/29/18 $500
1/30/18 $400
1/31/18 $300
2/1/18 $400
2/2/18 $300
2/5/18 $400
2/6/18 $600
2/7/18 $800
2/9/18 $300
2/10/18 $400
K9 and L9 are the dates of the range that I would be looking for. I want to get the average on a weekly basis. How can I do this?
- Check out the AVERAGEIFS function. Suppose your min and max dates are in F1 and F2:
=AVERAGEIFS($B$2:$B$100,$A$2:$A$100,"<="&$F$1,$A$2:$A$100,">="&$F$2)Thank you. I just had to switch the lesser than/equal and greater than/equal symbols and it worked.
