HI guys,
I have a formula that currently works and is in the following format:
SUM(‘A_Worksheet’!$D82:$U82)/INDEX(Named_Range1,MATCH(Type_of_money,Named_Range_of_money_types,0),Match(Specific_Year,Named_Range_of_Years)
What I need to do is to modify the formula so that each of the items in A_Worksheet is divided by the variable from the same year, rather than the specific year that it currently is, and then summed. I thought that I could achieve the desired result by using SUMPRODUCT and inverting the denominator. This seemed to work for a simple test case, but when I apply it here, I get something like this…
SUMPRODUCT((‘A_Worksheet’!$D82:$U82)/INDEX(Named_Range1,MATCH(Type_of_money,Named_Range_of_money_types,0),Match(‘A_Worksheet’!$D80:$U80,Named_Range_of_Years))
Unfortunately, the denominator evaluates as #VALUE!, and I’m not sure how to fix it.
Any suggestions would be most appreciated.
Thanks!!
Roy
Bookmarks