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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
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)
 

leeshan

New Member
Joined
Jan 27, 2013
Messages
24
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??
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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