Using OFFSET function to find arrays in a spreadsheet?

[gcefaloni]

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

 

[NiMip]

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.

 

[StephenCrump]

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?

 

[gcefaloni]

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?

 

[StephenCrump]

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.