Array Formula issue

[dukeofscouts]

I'm combining two formulas that I have intergrated into workbook. I'm cleaning everything up so I would like to have the two formulas calculating in one cell, so this is what I came up with:

=INDEX(Food_Items[Item],SMALL(
IF($AM$3<>"",
IF($C$2<>"",
(--(IFERROR((OFFSET(Foods!$C$2,0,MAX(--('Item Comp'!$E$2:$AK$2<>"")*(COLUMN($E$2:$AK$2)-4)),COUNTA(Food_Items[Item]),1)/
OFFSET(Foods!$C$2,0,MATCH('Item Comp'!$B$1,Table7[[#Headers],[MPGrain]:[Sodium]],0),COUNTA(Food_Items[Item]),1))>(--$AM$3),FALSE())))*(--(Food_Items[Category]=$C$2))*ROW(INDIRECT("1:"&COUNTA(Food_Items[Item]))),
(--(IFERROR((OFFSET(Foods!$C$2,0,MAX(--('Item Comp'!$E$2:$AK$2<>"")*(COLUMN($E$2:$AK$2)-4)),COUNTA(Food_Items[Item]),1)/
OFFSET(Foods!$C$2,0,MATCH('Item Comp'!$B$1,Table7[[#Headers],[MPGrain]:[Sodium]],0),COUNTA(Food_Items[Item]),1))>(--$AM$3),FALSE())))*ROW(INDIRECT("1:"&COUNTA(Food_Items[Item])))),
IF($C$2<>"",(--(Food_Items[Category]=$C$2))*ROW(INDIRECT("1:"&COUNTA(Food_Items[Item]))),ROW(INDIRECT("1:"&COUNTA(Food_Items[Item]))))),ROWS($AN$5:AN5)))



I also did one that was more linear that would put in the applicable arrays and multiply each together. However, in both cases I am having the same issue. When I send the formula down the column, it only returns the first value from the array. I've gone over the syntax several times, and can't find the issue.

Is there a way to fix this, or is this just an impossible formula?

 

[dukeofscouts]

am3.formula = mconcat($E$2:$AK$2)

 

[Matty]

That is one long formula.

Rather than expecting us to deduce what's going on with it, could you perhaps elaborate as to what the formula is supposed to do?

Providing some sample data as well as the desired result will also increase the likelihood of you getting a response.

Matty

 

[dukeofscouts]

OK, if it will help get figure this out, here we go:

Overall the whole table is set to evaluate records by another record. Each of the other cells return various look up values based on the Primary Key (which is where this formula we are working on is to be placed). Each cell goes finds the value for it's respective column in the data table, and then divides it by another column in the same record.

This is essential a data extract array formula using the Index,small set up

The If portion first looks to see if I've entered a value above the table which is my minimum value to be shown in the column below it.
IF($AM$3<>""
Should it find a value, the formula then determines which column I'd like extracted and the column I'm dividing it by, extracts that one two, and then checks to see which values are above the limit, converting trues and falses to 1's and 0's.
(--(IFERROR((OFFSET(Foods!$C$2,0,MAX(--('Item Comp'!$E$2:$AK$2<>"")*(COLUMN($E$2:$AK$2)-4)),COUNTA(Food_Items[Item]),1)/
OFFSET(Foods!$C$2,0,MATCH('Item Comp'!$B$1,Table7[[#Headers],[MPGrain]:[Sodium]],0),COUNTA(Food_Items[Item]),1))>(--$AM$3),FALSE())))

Next we check to see if I've applied a filter to the text portion of the sheet, this checks to see which category the record is in, and if that record is from an appropriate category, if there is filter applied.
IF($C$2<>"",
(--(Food_Items[Category]=$C$2))

In the end there are four possibilities,
Filter on Category and numeric goal
Filter on Category only
Filter on Numeric only
no Filter

Yes I realize that power of the Pivot table and the use of Advanced Filter would clean this up easily, but I refer to this table so often that having it be dynamic with a formula is simply the most efficient answer for me.

My current theory is that the whole formula gets blown because I have a mix of array and non-array ifs, to allow for the four possible scenarios. But I may be wrong about that.

Does that help at all?

 

[dukeofscouts]

I GOT IT!

Can't belive I didn't see it sooner. Because I was converting my true's and falses to 1's and 0's just slapping the array into small doesn't work since false is 0 and thus the lowest number. I working on converting the 0's back to false in a quick step, (easy to do but I'd have to run the formula twice) I'll keep you updated. In the mean time just using LARGE will do the job

 

[PaddyD]

The good news:

- you are to be congratulated for getting your formula to work.

The bad news:

- I would strongly caution you not to do anything like that! 845 characters is a fairly long sentence, let alone a formula. Here are some things I'd be worrying about if I saw that:

- if it returns an error, how will I trace the source
- if it starts returning erroneous results, but not explicit errors, how would I know?
- if someone else had to maintain the file, would they be able to understand what the formula's trying to do?
- given that intermediary results could be put into separate cells, do I understand what's being gained by the 'mega-formula' approach?

If anyone else is going to have to use this, I'd unpack the formula (or at the very least document it thoroughly!)

 

[dukeofscouts]

Well the good news is that I am the only one using this workbook. I use use every day, and it fills a practicle need in my life. However, beyond being a very useful tool, I also use this, and my other personal workbooks as an outlet for my creative side. It is true that having this super huge formula is a bit overkill, but this has been sitting in my head for several months. I knew there had to be a way to get this to work, I just had to prove it to my self. and now that I have, I can move on to perfecting it untill I do indeed have a simpler version. Right now I've got it working, but the calculation time is higher than I'd like, so my next project is to get that down. I'm also working on making the sheet more user friendly. This is just the first step in my revision of a nearly two year old sheet. These Message board posts have been priceless in filling in the gaps of my understanding of excel.

THANK YOU ALL SO MUCH,
D.O.S.

 

[PaddyD]

"...but the calculation time is higher than I'd like, so my next project is to get that down."

A few starters-for-ten:

- Get rid of the iferror() components, and instead check directly for the condition at issue, rather than the error that results if it doesn't obtain
- Replace offset()s and indirect()s with non-volatile equivalents where possible

Info on calcuation speed issues here:

http://www.decisionmodels.com/


Happy creating...

 

[dukeofscouts]

Thanks for the advise I'll keep those in mind.