unable to use offset or indirect in array with non-numerical cell reference

[NooobV]

Hi All, My first post and after hours of scouring the google, I've been left wanting, i'm very tired at present. any ways.
Apologies in advance for not knowing how to attach a file display info easily.
i'm attempting to do an array that looks like: {=LARGE(IF(MEMBER=$B$26,IF(LOC=$D$26,IF(SALE,(INDIRECT(MEAL))))),3)}
also tried using offset, and looks like this: {=LARGE(IF(MEMBER=$B$26,IF(LOC=$D$26,IF(SALE,(OFFSET(SALE,0,-2))))),1)}

the formula works with numerical cells in the data table, but the cell reference i'm attempting to display is text.
funny thing is when using OFFSET() & INDIRECT(), on their own they display text without an issue.
i haven't found an alternative non array type formula, or a workaround.
I'm almost certain that when I catch up with this, #num result, we're gonna have words.
any advise is very much appreciated.
data table excel forum question.JPG

[kersplash]

To attach a file go to Go Advanced (near Post Quick Reply) -> Manage Attachments -> Upload

[NooobV]

ok, i'll try that...

[AliGW]

Try this:

=OFFSET(SALE,MATCH(LARGE(IF(MEMBER=$B$26,IF(LOC=$D$26,SALE)),1),SALE,0)-1,-2)

[FlameRetired]

Have you tried redefining the named ranges. They include the headers.

It also appears you are trying to apply the LARGE function to text arrays. This doesn't work.

Other than that I am unable to understand the mission.

Perhaps OFFSET / INDIRECT aren't the tools of choice here.

BTW: Could you tell us what the objection is to array formulas?

[AliGW]

Thanks for the rep.

Don't leave the thread hanging - if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[NooobV]

AliGW, you are awesome. thankyou so much for your help.!!

so elegant, ie: "try this". that worked perfectly!!!

I haven't tested it (tried to break it) yet (will after a good night sleep) to see why the.?
Would it be correct to evaluate that my leading with the large argument, prevented the function of the offset or indirect to calculate text?

and FlameRetired, I though I had named ranges correct.? And I did realise my formula layout was poor, no objection to array formulas, I prefer them, as I think they look neater.

[FlameRetired]

OK. I misinterpreted the comment about array formulas.

[AliGW]

It was nothing to do with it being text or a number - it was just a case of getting the syntax right to use the LARGE function to identify the row number that the OFFSET needed. I suggest you step through the formula using the Evaluate Formula option on the Formulas ribbon - it's very enlightening in these cases, and shows you step-by-step how a formula resolves.

[NooobV]

AliGW, thank you for your further explanation, the "-1" row reference, was confusing (cause i'm a noob), but the penny dropped eventually.

=OFFSET(SALE,MATCH(LARGE(IF(MEMBER=$B$26,IF(LOC=$D$26,SALE)),1),SALE,0)-1,-2)
After testing the formula on a large data set, some problems arose.

1. the reference to the 'meal' "-1,-2)" becomes inconsistent when pasting the formula down or using it with a 'LOC' reference to other areas,
eg: bistro, coffee shop.
2. the 'LARGE' function becomes inconsistent when asking for the 4th largest sale,
'meal' ")),4)" description.
3. the named ranges had to be specified to a finite column value, not just the whole column.
eg: $B:$B Didn't work, but $B$1:$B$40000 did work.
4. Excel's performance slows significantly as it attempts to process the formulas, and attempting to evaluate the formula,
excel freezes into 'not responding'.
5. When attempting to view calculation steps, excel spits a message at me that says;
"your formula is too long, formulas should not be longer than 8192 characters."

Given the last two issues I believe as FlameRetired suggested to be true: "Perhaps OFFSET / INDIRECT aren't the tools of choice here."

I will continue to search and test on other ways to tackle the issue,
any further suggestions would be greatly appreciated,
and I will post the solution when I find one.

[JohnTopley]

Try

in D29

=LARGE(IF((Table2[LOCATION_DESCRIPTION]=$D$26)*(Table2[MEMBER_NUMBER]=$B$26),Table2[SALES]),ROWS($1:1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

in C29

=INDEX(Table2[MEAL],MATCH(1,(Table2[LOCATION_DESCRIPTION]=$D$26)*(Table2[MEMBER_NUMBER]=$B$26)*(Table2[SALES]=$D29),0))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

As you are using Excel TABLE format, you do not need named ranges as TABLES expand/contract as ROWS are Added/Deleted.

Unlike Named Ranges, DELETING data from a TABLE e.g. using Clear Contents ,does NOT change the TABLE size.

[jason.b75]

I was going to suggest a similar method to John, but he beat me to it.

Roast Pork Small appears twice in your example, (rows 3 and 15) should the formula be looking at the highest individual row, or the sum of all matching rows?

JT, your formula suffers the same fault that I was trying to correct in mine before posting, incorrect results with duplicate amounts in the sales column.

[jason.b75]

Taking a different approach, non array formula method.

Delete all named ranges (except for Table2)

In H2

=IF(AND([@[LOCATION_DESCRIPTION]]=$D$26,[@[MEMBER_NUMBER]]=$B$26),COUNTIFS([LOCATION_DESCRIPTION],$D$26,[MEMBER_NUMBER],$B$26,[SALES],">"&[@SALES])+COUNTIF(E$2:E2,E2),"")  

Change column heading in H1 to "Rank" (without the double quotes).

In A29

=SUMIFS(Table2[SALES],Table2[LOCATION_DESCRIPTION],$A$28,Table2[MEMBER_NUMBER],$B$26)  

in C29

=INDEX(Table2[MEAL],MATCH(ROWS($C$29:$C29),Table2[Rank],0))  

in D29

=INDEX(Table2[SALES],MATCH(ROWS($C$29:$C29),Table2[Rank],0))  

Copy down as needed.

Might need some changes, (see previous post).

[JohnTopley]

Using Helper column H (which can be hidden)

=E2+COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2)*0.00001

in D29

Please Login or Register  to view this content.

NOTE: this value is the one in the helper column

To get the TRUE sales value in D29 (column E in attached)

Please Login or Register  to view this content.

in C29

Please Login or Register  to view this content.

All array-entered

[JohnTopley]

Or (No helper)

In D29

=LARGE(IF((Table23[LOCATION_DESCRIPTION]=$D$26)*(Table23[MEMBER_NUMBER]=$B$26),Table23[SALES]),ROWS($1:1))

in C29

=INDEX(Table23[MEAL],SMALL(IF((Table23[LOCATION_DESCRIPTION]=$D$26)*(Table23[MEMBER_NUMBER]=$B$26)*(Table23[SALES]=$D29),ROW(Table23[SALES])-ROW($E$2)+1,""),COUNTIF($D$29:$D29,$D29)))

both array entered

See Sheet2 of attached

[NooobV]

Hi JohnTopley,
Another thing that I've learned here today as I didn't know that before:
"using Excel TABLE format, you do not need named ranges as TABLES expand/contract as ROWS are Added/Deleted.
Unlike Named Ranges, DELETING data from a TABLE e.g. using Clear Contents ,does NOT change the TABLE size."
Thank you.

in D29
{=LARGE(IF((Table2[LOCATION_DESCRIPTION]=$D$26)*(Table2[MEMBER_NUMBER]=$B$26),Table2[SALES]),ROWS($1:1))}
in C29
{=INDEX(Table2[MEAL],MATCH(1,(Table2[LOCATION_DESCRIPTION]=$D$26)*(Table2[MEMBER_NUMBER]=$B$26)*(Table2[SALES]=$D29),0))}
entering in as an array CTRL+SHIFT+ENTER.
I tried your suggestions; they seem to work efficiently, ie: no more Excel slowing down to a halt (freezing).
Apart from the misinterpreting duplicate sales values it works very well!

=E2+COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2)*0.00001
The 'helper' column added to the end column on the table, and 'no helper' version fixed that issue.!
That's awesome! Thank you so much!

Correct me if I'm wrong, but the result caused by the LARGE function using the helper column,
will always make the duplicate value the lowest (furthest down) one on the list.
Nice touch using the "SMALL(IF" to differentiate, replacing the helper column (If I'm on the right track).

You've got me thinking that maybe if it refers to the 'QTY' column, the product with more items sold could get ranks.
Also, thank you for attaching the files to explain, that helps immensely.

Hi also to, Jason.b75,
when you mentioned: "Roast Pork Small appears twice in your example, (rows 3 and 15) should the formula be looking at the highest individual row, or the sum of all matching rows?"
Yes the sum of, as a member can change classification in an omitted column, within the evaluated period, therefore producing two instances of the same 'MEAL' type.
Your non array attempt is impressive also, and thank you for attaching the file example to help me understand better.
Although I had some trouble implementing into a larger data set of ~40000 rows (maybe I didn't follow your directions exactly),
thank you very much for your efforts!

[NooobV]

I'm going to mark the thread solved, as the original question was answered by three people, and each way worked.

The best way for me was JohnTopley's suggestion, especially after adding in the helper and no helper versions into the formula.

Although there are three remaining issues in my case;
1. as Jason.b75 mentioned; find a way to sum the sales that match (matching rows) when the returned 'MEAL' is duplicated,
but also omitted from further results.
the cause is where the member classification had changed in the period and therefore has more than one of the same 'MEAL' items (may not even need the extra column)

2. Find a way that the summed cells and meal types, don't reoccur in the next favourite meal results.

3. use the QTY, value to determine the priority of the listed item if the same sales price occurs.

I think then it will be perfect!! but may be beyond the scope of my original question.
nevertheless I've attached the file adjusted by JohnTopley, and added in the MEMBERSHIP_TYPE column.

Thank you again to everyone for their contribution, I'm learning a lot here, it is very much appreciated.!

[jason.b75]

The first 2 parts are easy enough, membership type does not need to be considered unless you only want the results relating to a single tier.

Part 3, the tiebreaker is a little more complicated, unless I'm missing something obvious.

Should the priority be given to highest qty, or lowest qty (highest value per single item)?

As a side thought, have you considered the simple method?

Filter the table, then sort the results?

[jason.b75]

See if this is of any use, I've added some comments to explain any ranges that are not table names, hopefully that will make it easier to copy over to your real workbook.

I've gone for a non-array method, the downside, 3 helper columns. The first of which contains the formula that does the brunt of the work, eliminating non matching locations and member numbers, duplicate and zero value lines and totaling sales by meal.

The second helper totals the quantities by meal for the remaining rows, the third ranks the results of the first 2 columns.

Perhaps not the most elegant looking method, but far simpler and more efficient than the arrays that would be needed to do it without helpers (if at all possible with the revised scope).