averaging Vlookup

Hi jrtaylor,

See if something like:

Please Login or Register  to view this content.

in M3 and pull down works for you.
Average Indirect Match.xlsx

Take M8 as an example. Why should the average be from B8 to B10, when B10 is PRESENT in column F?? Which is wrong? Your manually calculated result, or your description? What should it be?

Hey Glenn,

I'd bet they manually calculated M8 wrong. That is why they needed a formula.

I expect that AVERAGEIF() or AVERAGEIFS() will be at least part of the solution. I assume that you understand the basic "excel questions" like syntax and use of AVERAGEIFS() function. It seems to me that the question is more about the logic behind the function, which I could not back out of your example. I expect that, once the logic is understood, programming this into Excel (or any other programming language) will be easier.

Your examples include --

For 2 Jan, you are averaging the values for 1 and 2 January -- why not just pull the value for 2 Jan?
For 3 Jan, you are pulling the value for 4 Jan -- why not pull the value for 3 Jan?
For 9 Jan, you are averaging the values for 7, 8, 9 Jan.

As a guess, something like =AVERAGEIFS($B$2:$B$32,$A$2:$A$32,">"&F3,$A$2:$A$32,"<="&F4) in M4 copied down might mostly work. It won't work for the first entry (M3), so you would need to come up with something a little different for the first entry. This function looks at the current rows date and the previous rows date and averages everything from column B where column A is "between" the previous row's date and the current row's date (between meaning greater than but not equal to previous row's date and less than or equal to current row's date). That is my best guess so far at what you intend to do.

If I understand, my formula should do that. Did you try it? Did it give you what you expected? If not, which entries did it get wrong?

If you look more carefully at what I proposed, I proposed the averageifs between previous row's data and this row's date. The formula you put into column M is averageifs between this row's data and the next row's date. A slight and subtle difference, but enough to make the results one row off -- so my formula in M3 returns the same value as N4, M4 returns the same value as N5, and so on down the list (except for M13 and N14, and I don't understand why your desired outcome is pulling the value from 15 January when this row is for 17 January).

If I assume that the value in N14 should be 19 (the value from B18 that corresponds to 17 Jan), then the current formula in column M (between this row and next row) seems correct -- except for one row off. If I use the formula I proposed in post #5 (previous row and this row -- so the formula starts in M4, not M3) the results match up with your desired results. Obviously, a different formula is needing in M3 [Maybe =AVERAGEIFS($B$2:$B$32,$A$2:$A$32,"<="&F3)]. Again, I am assuming that the current value in N14 (7.0) is incorrect and this one should be 19.0. If 7.0 is correct, then explain why this result is 7.0 (the value in B16).

I think it should, assuming I have understood so far. The formula is averaging all of the values between the date in the previous row (but not including this date) and the date in the current row (including that date). So, if the previous row's date corresponds to last Thursday, and the current row's date corresponds to Tuesday, then it will average all of the days between (but not including) Thursday and Tuesday (including Tuesday). For that matter, if the previous row's date is the first of the previous month, and the current row's date is the first of the current month, it will average the entire month's data. The function really isn't trying to make any judgement or decision about how many days are included, it is only looking at "between" two dates. The only reason I can think of that this would not work is if there are scenarios where the desired average cannot be neatly described as "between" two dates.