Calculate only on visible rows

honkin

Active Member
Joined
Mar 20, 2012
Messages
319
Office Version
  1. 2016
Platform
  1. MacOS
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

Code:
=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

Code:
=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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Maybe it is as simple as replacing the "" with 0, since SUBTOTAL(109 is calculating the sum.
Excel Formula:
=IF(AG17<>"",(1/AG17),0)
 
Last edited:

honkin

Active Member
Joined
Mar 20, 2012
Messages
319
Office Version
  1. 2016
Platform
  1. MacOS
Maybe it is as simple as replacing the "" with 0, since SUBTOTAL(109 is calculating the sum.
Excel Formula:
=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

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
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

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
@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.
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
319
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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

Screen Shot 2564-10-05 at 14.17.50.png

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)

Screen Shot 2564-10-05 at 14.19.36.png


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

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
I am taking yet another guess, but this is different than the previous.
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
319
Office Version
  1. 2016
Platform
  1. MacOS
I am taking yet another guess, but this is different than the previous.
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

Active Member
Joined
Mar 20, 2012
Messages
319
Office Version
  1. 2016
Platform
  1. MacOS
I am taking yet another guess, but this is different than the previous.
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,240
Messages
5,836,189
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top