Calculate only on visible rows

[honkin]

I have a workbook with around 30,000 rows of data, which expands daily when new data is downloaded. All of the formulas work well, but the need arose to have the workbook be shared between some other users, so certain columns needed to be hidden, but more importantly, a lot of the calculations needed to be automated and filled much further down in the sheet....down to row 100,000 to cover it for maybe another 2 years or so. That part is all fine, as those cells only fill when something is added to a corresponding cell.

One issues has arisen, though. It affects just this one formula and I cant work out how to fix it. The formula calculates what is known as A/E (Actual vs Expected). It does nothing more than tell you whether you have an edge on the market. So an A/E of 1 shows that you are pretty much matching the market expectations of the result, whereas 1.04 means you have a 4% edge on the market, meaning selections will win 4% more than they are expected to. An A/E of 0.97 means your selections perform 3% worse than the market expects. It uses SUBTOTAL and 103 so that it still works when the sheet is autofiltered

Here is the formula

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

All of it was fine until I extended the data in column EZ down to 100,000 rows. Initially that column only had data down to the last row of visible selections, but now it continues right down to row 100,000 with this formula

=IF(AG17<>"",(1/AG17),"")

It simply means if there is nothing in AG, then nothing will show in EZ. The catch is the first formula was affected by this as now there is data in EZ all the way down to row 100,000

I am wondering how to adjust that first formula to have it calculate ONLY on actual rows with visible data

cheers

 

[cmowla]

Maybe it is as simple as replacing the "" with 0, since SUBTOTAL(109 is calculating the sum.

=IF(AG17<>"",(1/AG17),0)

 

[honkin]

Maybe it is as simple as replacing the "" with 0, since SUBTOTAL(109 is calculating the sum.

=IF(AG17<>"",(1/AG17),0)

Cheers cmowla and thanks so much for your reply

The downside of that one is there will be 70,000 rows of zeroes instead of them appearing blank in column EZ. I think for neatness of appearance when sharing the files, that having those rows appear empty is a better look.

I really think it needs to be an adjustment to the first formula so that it somehow ignores calculating those cells which have the formula in them, but no actual result. I'm just unsure as to how to go about it.

cheers

 

[cmowla]

At first you mentioned that you only want to calculate only the rows for which AG17<>"" is true. Does the above fix work for your formula?

If so, now you want to hide the rows for which the condition

AG17<>""

is false?

And do you want to literally hide the entire rows or just make the contents of the rows "appear" blank?

Or did I misunderstand you?

 

[cmowla]

@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).
<< unavailable video removed >>

 

[cmowla]

@honkin ,
If it is more ideal to hide the rows completely (rather than making the cell contents appear blank), just to "make your data even", please see the following video (part 2) and see if that works for you. But that's all I've got based on what you told me so far.
<< unavailable video removed >>

 

[honkin]

@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.

=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.

=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

 

[cmowla]

I am taking yet another guess, but this is different than the previous.
<< unavailable video removed >>

 

[honkin]

I am taking yet another guess, but this is different than the previous.
<< unavailable video removed >>

My sincere apologies for the late reply, Christopher. Got a promotion and been absolutely snowed under with the new tasks it involves.

I found the video very exciting and will be trying what you suggest in the next few days. It seems a novel way to approach it, but have no issues in giving it a try.

Thanks so much for taking the time

 

[honkin]

I am taking yet another guess, but this is different than the previous.
<< unavailable video removed >>

ah, things went from bad to worse with time, Christopher and as such, by the time I got to rewatching the video, it had naturally gone. I assume you deleted it off your machine as well.

My apologies...I should have downloaded it from YouTube when I had the chance