Using OFFSET function to find arrays in a spreadsheet?

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
119
Hi guys,

I'm trying to find the average result within a percentile. This is the formula I have for that:
=IFERROR(AVERAGE(IF('1b. Import Morningstar Data'!$AF2:$HL2<PERCENTILE('1b. Import Morningstar Data'!$AF2:$HL2,0.001),'1b. Import Morningstar Data'!$AF2:$HL2))/100,"")

It works perfectly fine on its own. However I would like to replace the 3 "$AF2:$HL2" portions of it with some kind of OFFSET MATCH MATCH type function to find the arrays to compute it from instead of a fixed row numbers.

I came up with the following (that portion doesn't work):
=OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!1:1,0),,COUNT('1b. Import Morningstar Data'!AF1:HL1))

The combined function as of right now looks like this (Obviously also doesn't work):
=IFERROR(AVERAGE(IF(OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1))<PERCENTILE(OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1)),0.001),OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1))))/100,"")

I know that last one is a big formula and it's hard to figure out where the error is but if you could manage. It would be SOOOOOOOOOO appreciated.

Thank you so much in advance for taking the time to figure out this problem with me.

Gabriel
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hard to say without seeing your dataset, but as a first guess, do you have headings for your data? If so, COUNT('1b. Import Morningstar Data'!AF1:HL1) will return 0, and you should use the COUNTA(...) function instead.
 
Upvote 0
Your formulae haven't posted properly. I'm guessing the first missing character is "<" which gets interpreted as html. Put a space in front of any "<" and the formula should post correctly.

But rather than posting formulae that don't work, and can probably be improved on in any event, can you please post a screenshot of a simplified example, showing what your data looks like and the result you're expecting to see?
 
Upvote 0
You're right! sorry.

=IFERROR(AVERAGE(IF('1b. Import Morningstar Data'!$AF2:$HL2 < PERCENTILE('1b. Import Morningstar Data'!$AF2:$HL2,0.001),'1b. Import Morningstar Data'!$AF2:$HL2))/100,"")

and

=IFERROR(AVERAGE(IF(OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1))<percentile(offset('1b. import="" morningstar="" data'!$a$1,match(a3,'1b.="" data'!$a:$a,0),match('1b.="" data'!af1,'1b.="" data'!$1:$1,0),,counta('1b.="" data'!$af$1:$hl$1)),0.001),offset('1b.="" data'!$af$1:$hl$1))))="" 100,"")
</percentile(offset('1b.> < PERCENTILE(OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1))<percentile(offset('1b. import="" morningstar="" data'!$a$1,match(a3,'1b.="" data'!$a:$a,0),match('1b.="" data'!af1,'1b.="" data'!$1:$1,0),,counta('1b.="" data'!$af$1:$hl$1)),0.001),offset('1b.="" data'!$af$1:$hl$1))))="" 100,"")
</percentile(offset('1b.>,0.001),OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!$1:$1,0),,COUNTA('1b. Import Morningstar Data'!$AF$1:$HL$1)))/100,"")

Your formulae haven't posted properly. I'm guessing the first missing character is "<" which gets interpreted as html. Put a space in front of any "<" and the formula should post correctly.

But rather than posting formulae that don't work, and can probably be improved on in any event, can you please post a screenshot of a simplified example, showing what your data looks like and the result you're expecting to see?
 
Last edited:
Upvote 0
Your questions distils down to:

1. This formula works OK for the row vector AF2:HL2

=IFERROR(AVERAGE(IF('1b. Import Morningstar Data'!$AF2:$HL2 < PERCENTILE('1b. Import Morningstar Data'!$AF2:$HL2,0.001),'1b. Import Morningstar Data'!$AF2:$HL2))/100,"")

2. Instead of AF2:HL2, I want to use this row vector:

=OFFSET('1b. Import Morningstar Data'!$A$1,MATCH(A3,'1b. Import Morningstar Data'!$A:$A,0),MATCH('1b. Import Morningstar Data'!AF1,'1b. Import Morningstar Data'!1:1,0),,COUNTA('1b. Import Morningstar Data'!AF1:HL1))

3. But this vector is wrong.

To us, it is not clear what "wrong" means. Perhaps this vector formula originally returned a #REF ! error because COUNT was zero, as NiMip identified. Perhaps you need to replace COUNTA with COLUMNS? Or perhaps the OFFSET is just pointing somewhere other than where you intended?

Can you post a simplified example to show us how you want your row vector determined.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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
Back
Top