Last seen: May 22, 2025
You can use: =AVERAGE(IF(C3:V3<>"",(C3:V3-$C$2:$V$2)/$C$2:$V$2,"")) array-entered with Ctrl+Shift+Enter, then copied down.
Interestingly, it seems to be the combination of INDIRECT with ADDRESS. INDIRECT on its own works fine. As it happens, you can avoid both here by u...
In B3: =INDEX('Reference tale sheet'!B:B,4+INT((ROWS($A$1:$A1)-1)/9)) Copy to C3, then you can copy and paste your 9*2 blocks.
INDIRECT expects a text address, not an actual cell reference (which is what it returns). So you can use that to provide the necessary starting cell f...
It needs to be: INDIRECT("Sheet1!"&ADDRESS($J$2,3)) to specify the sheet for the returned range.
Assuming you can't simply apply the filter to the PQ output, here's one method per Chris Webb:
You have to include a sheet name in the INDIRECT part, otherwise there's no context as to what range you actually want.
If they’re files using PQ or data models, perhaps they’ve made improvements to the Vertipaq storage methods. Be interesting to see what Mynda hears fr...
Switching from the old BIFF8 (.xls/.xlt/.xla) format to any of the newer compressed formats (.xlsx/.xlsm/xlsb/.xlst/.xlam) would reduce the file size,...
That's not quite what I suggested. It should be this: IF(EXACT('Data 2019'!$A$1:$A$517,$A$2) not this: IF(EXACT('Data 2019'!$A...
You'd need: IF(EXACT('Data 2019'!$A$1:$A$517,$A$2) instead of: IF('Data 2019'!$A$1:$A$517=$A$2 Ideally, you'd also use...
Why not just filter them out?
First off, it looks like you're using Google sheets, not Excel? The 1 doesn't make the formula dynamic, it's the value that is being looked up...
Just for info, you could also use an autofilter for that: Sub DelRowsLessThanOne() Application.ScreenUpdating = False With Range("A1").Current...
There are plenty of ways but you haven't explained what you want. Do you want a prompt to specify how many columns across to offset? Or should it actu...