Sorry for the title! It's hard to describe exactly in such few words.
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
These two equations correctly mimic the behaviour of the original static one above and resolve correctly when combined: i.e. =A1/A2
So, does anyone know why I can't combine them into a single equation? i.e.:
"A4"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))/SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
For some reason it just returns the value 0. No error, no #REF or whatnot. I can't see what I'm doing incorrectly!
Any help or advice would be appreciated, thanks!
Bookmarks