Offset Reference error

leeshan

New Member
Joined
Jan 27, 2013
Messages
24
I am using the offset formula to be used as a source reference for my percentile formula.

=OFFSET($A$1,(MATCH("ANZ",$A:$A,0))-1,2,COUNTIF($A:$A,"ANZ"),0)

However, i keep getting the #REF error.

ABC
ANZ44410
ANZ33387
ANZ55534
ANZ66613
ANZ67812

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>


What i wan to do is to find the 95th percentile of column C provided column A is ANZ.
i was trying to write a dynamic data source.

Thanks.
 

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
I'm not following exactly what you are doing, but I think your #REF is due to having a width of 0 - you should have something a tad larger than that

It sounds like you maybe want to add up all of the items in Col C based on Col A being ANZ, why not use a sumif() and divide by a countif() of the number of ANZ's? Unless I'm misunderstanding what your goal is...? (its been a little while since I did much with statistics)
 
Upvote 0
hi. ya thats my purpose. i cannot use sumifs because i still have other data, column A not only ANZ, there are still others, so i will need a dynamic range.. i thought width 0 because only 1 column that i want so sum up??
 
Upvote 0
I am using the offset formula to be used as a source reference for my percentile formula.

=OFFSET($A$1,(MATCH("ANZ",$A:$A,0))-1,2,COUNTIF($A:$A,"ANZ"),0)

However, i keep getting the #REF error.

A
B
C
ANZ
444
10
ANZ
333
87
ANZ
555
34
ANZ
666
13
ANZ
678
12

<tbody>
</tbody>


What i wan to do is to find the 95th percentile of column C provided column A is ANZ.
i was trying to write a dynamic data source.

Thanks.

Option 1, Sheet1 is assumed to contain the data.

Define Lrow as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$C:$C)

Define Data as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$C:$C,Lrow)

Now you can invoke formulas like...

=PERCENTILE(IF(INDEX(Data,0,1)="ANZ",INDEX(Data,0,3)),0.95)

which must be confirmed with control+shift+enter, not just enter.

Option 2

E2:

=MATCH(9.99999999999999E+307,C:C)

E4, control+shift+enter, not just enter:

=PERCENTILE(IF($A$2:INDEX(A:A,$E$2)="ANZ",$C$2:INDEX(C:C,$E$2)),0.95)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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