Offset Reference error

leeshan

New Member
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

<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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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)

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??

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)

yeap. it works! guys, thanks!!

yeap. it works! guys, thanks!!

You are welcome. Which option did you adopt?

Replies
2
Views
109
Replies
1
Views
159
Replies
5
Views
268
Replies
1
Views
229
Replies
3
Views
495

Forum statistics

1,203,127
Messages
6,053,667
Members
444,677
Latest member
LWilliamsAtchristies

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.

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

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