Log In
Register
Home
July 11, 2017
Offline
Hi
I'm trying to compare the average Revenue & GP for specified Years/Weeks, I've tried using Index/Match but with no success. I need a formula that gives me the average for the Years Selected then the weeks selected. Any suggestions?
Many thanks
Paul
VIP
Trusted Members
December 7, 2016
Offline
Hello Paul,
See attached file for two different solutions.
For the Index/Match formula, you can read more information about the technique used in this article.
If you are not so familiar with using Pivot Tables, I can recommend this article to start with.
July 11, 2017
Offline
Hi Anders,
Sorry for not replying sooner, but work took over,
Really great solution, but when I tried moving to a different position on spreadsheet I get a #REF! error?
I can not see what is causing the error, but only part of formula I do not understand is Match(1, what does the 1 do as I would normally use a cell ref?
Thanks
Paul
VIP
Trusted Members
December 7, 2016
Offline
Hello Paul,
Currently viewing your file from my tablet and don’t have full view or functionality, but will try to give some help anyway.
Update: I have now checked your file in Excel and can confirm that the named ranges were missing in the name manager. As soon as I created those named ranges the values shows up correct. The name manager does not allow % characters in the name, so in order to still use the "dynamic" approach using the INDIRECT() function I renamed the GP% column to GP_Percent (as spaces are not allowed either).
If you want to use the GP% as header then use absolute cell reference instead.
Current formula in cell B310:
{=AVERAGE(INDEX(INDIRECT($A310);MATCH(1;($B$305=$B$4:$B$295)*($B$306=$A$4:$A$295);0));INDEX(INDIRECT($A310);MATCH(1;($B$305=$B$4:$B$295)*($C$306=$A$4:$A$295);0)))}
New formula in cell B310 using absolute cell reference:
{=AVERAGE(INDEX($G$4:$G$295;MATCH(1;($B$305=$B$4:$B$295)*($B$306=$A$4:$A$295);0));INDEX($G$4:$G$295;MATCH(1;($B$305=$B$4:$B$295)*($C$306=$A$4:$A$295);0)))}
I believe the error is due to the INDIRECT($A307) reference, it requires that you have set a named range Occupancy for D4:D294, do create named ranges for E4:E294, F4:F294 and G4:G294 using the headers in row 3 as the name for each column. Or change so that you use the cell references instead.
The answer to your question about number 1 as lookup value in the MATCH formula is found in the blog article I linked to previously, check the section ”So, how does it all work?” https://www.myonlinetraininghu.....o-criteria
1 Guest(s)
