@honkin ,

Good day to you.

If you want to hide the rows with zeros (and the calculation now works by doing the "" with 0 replacement mentioned previously), please see this short video, and let me know if that works for you (and that we're on the same page).

Hi Christopher

Thanks so much for your work on this.

I may not have been clear enough on what this sheet is really doing. The bottom line is other users will be copying and pasting data into the unlocked cells, which go right across to column CY. Everything to the right of CY is locked, which was why I needed to do the formula the way I did. It meant I could have the locked cells to the right calculate automatically as data is input into the relevant left cells. AG & EZ are linked, AH & FA and so on. So as AG gets filled, EZ does the calculation, but NO calculation is required below the last row of actual data. So when AG and across to AT have NO data, then EZ to FL should also have NO data. Having 0 all the way down multiple rows just wouldn't work and creating helper columns to be able to hide just those zeroes would also be problematic

The columns down to 100,000 have been fine and the sheet worked well when the formula went only to the last row of visible data. Any rows below the current last row of data should always remain blank. But what is happening with the original formula is it takes into account that there is data in EZ and FM, even if they show as blank all the way down and it drastically alters the outcome.

Here is the original formula where I have changed the 100000 to the actual last row of data.

Code:

`=SUMPRODUCT(SUBTOTAL(103,OFFSET(FM17:FM31871,ROW(FM17:FM31871)-MIN(ROW(FM17:FM31871)),,1))*(FM17:FM31871>=0))/SUBTOTAL(109,EZ17:EZ31871)`

You can see that A/E is red 0.94. That is the correct answer, showing the model is 6% behind the market

Now below, when I change the formula back to to 100,000, you can see the red cell, Pool Impact Value or (A/E) changes to green and 5.72, which is way, way off. It is taking those cells into account, even though they really have no value in them...just a dormant formula waiting to fill.

Code:

`=SUMPRODUCT(SUBTOTAL(103,OFFSET(FM17:FM100000,ROW(FM17:FM100000)-MIN(ROW(FM17:FM100000)),,1))*(FM17:FM100000>=0))/SUBTOTAL(109,EZ17:EZ100000)`

I am wondering if something else might works, Christopher. Would there be a way to deduct the cells which are blank from this original formula? What I mean is, both FM and EZ have formulas which go right down to row 100,000 and both say if blank, then "". Now obviously those cells are not really blank, as they have a formula in them, which is what is causing the issue, but is there a way to deduct those cells which have no value; in other words there is a formula there, but visibly the cell is blank? I get the feeling that if both FM and EZ in the above formula could ignore those cells, the calculation would work fine. The syntax is just beyond me, though

What are your thoughts?

cheers