Simplify formula? simple math per column, outcome classified by condition & column priority

[siamond]

Hi there,

I have the following formula referencing columns E to J. As you can see, I do some basic math on some cells from a column, and use the leftmost column which fulfills a condition referencing some other cells from the column.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #005109}span.s2 {color: #9900cc}span.s3 {color: #92371f}span.s4 {color: #003ecc}span.s5 {color: #2da33a}span.s6 {color: #ff8a17}span.s7 {color: #fa2ad5}</style>=(IF(AND(ISNUMBER(J11), J$5), (1+J11%)/(1+J$7%), IF(AND(ISNUMBER(I11), I$5), (1+I11%)/(1+I$7%), IF(AND(ISNUMBER(H11), H$5), (1+H11%)/(1+H$7%), IF(AND(ISNUMBER(G11), G$5), (1+G11%)/(1+G$7%), IF(AND(ISNUMBER(F11), F$5), (1+F11%)/(1+F$7%), IF(AND(ISNUMBER(E11), E$5), (1+E11%)/(1+E$7%), NA()))))))-1)*100

I would like to make it simpler (hence more readable) and I'd like to make it more flexible to accommodate an arbitrary number of columns, e.g. using an array formula of sorts. But I can't figure out how to do it... Ideas?

PS. I also would like to keep the property that I can copy the formula to the next group of similar columns, and it will automatically adjust itself. Assuming the number of columns in the group is the same, of course.

 

[pgc01]

This is a quick example that I just tried:

=INDEX(((1+E11:J11%)/(1+E$7:J$7%)-1)*100,MAX(ISNUMBER(E11:J11)*(E$5:J$5<>0)*(COLUMN(E11:J11)-COLUMN(E11)+1)))

But I don't know how it is with LibreOffice Index() function.
Will it accept something like

=INDEX({#N/A,3,#N/A,7,#N/A},4)

and give the result 7 like in excel? or does it have problems with the error values?

 

[siamond]

For ex., start by getting the column you want to use for the calculation with something like:

MAX(ISNUMBER(E11:J11)*(E$5:J$5<>0)*COLUMN(E11:J11))

and then get the values for the calculation with for ex. Indirect() or Offset().

Yup, I was just reading about the same approach on another forum... Trouble is Indirect() or Offset() are both volatile functions. Which would be very undesirable as the data I am processing is used in turn by a bunch of rather heavy duties computations in the rest of the spreadsheet.

This is so annoying, your LOOKUP() solution is just perfect. Thank you so much for your help, by the way. Much appreciated.

 

[pgc01]

INDEX(), that I used in the formula, is not volatile in excel.

 

[siamond]

This is a quick example that I just tried:

=INDEX(((1+E11:J11%)/(1+E$7:J$7%)-1)*100,MAX(ISNUMBER(E11:J11)*(E$5:J$5<>0)*(COLUMN(E11:J11)-COLUMN(E11)+1)))

But I don't know how it is with LibreOffice Index() function. Will it accept something like

=INDEX({#N/A,3,#N/A,7,#N/A},4)

and give the result 7 like in excel? or does it have problems with the error values?

Very clever. I guess I could simplify a tad by having a row precomputing the COLUMN math.

Your formula works just fine, E11:J11 and E$7:J$7 are either blank or numerical, no NA() issue to plan for.

Still have a problem though. In the case where the MAX() returns 0 (say because all booleans in E$5:J$5 are set to FALSE), then I would want either a blank value or NA(), but on both Excel and LibreOffice, the INDEX() function acts like the index is 1 and returns the first value of the computed vector. That's weird?

 

[pgc01]

In the case where the MAX() returns 0 (say because all booleans in E$5:J$5 are set to FALSE), then I would want either a blank value or NA(), but on both Excel and LibreOffice, the INDEX() function acts like the index is 1 and returns the first value of the computed vector. That's weird?

That's not true, at least in excel. If you use 0 in Index() it will return the whole array. Evaluate the formula and check. This does not mean it will not display in the cell one of the values of the array, but the Index() will return the whole array.

As for the rest, I'd replace the Max() with Aggregate(). This way I can get an error when the conditions are never met, something like:

AGGREGATE(14,6,(COLUMN(E11:J11)-COLUMN(E11)+1)/ISNUMBER(E11:J11)/(E$5:J$5<>0),1)

 

[siamond]

That's not true, at least in excel. If you use 0 in Index() it will return the whole array. Evaluate the formula and check. This does not mean it will not display in the cell one of the values of the array, but the Index() will return the whole array.

Ah ok, thanks for clarifying. Well, the array starts by the first value, which is the one ending up as result in the cell where the formula is located, hence what I observed (but misinterpreted).

As for the rest, I'd replace the Max() with Aggregate(). This way I can get an error when the conditions are never met, something like:

AGGREGATE(14,6,(COLUMN(E11:J11)-COLUMN(E11)+1)/ISNUMBER(E11:J11)/(E$5:J$5<>0),1)

This is getting hard to understand, I get it now (after looking up the AGGREGATE function), but people looking at the spreadsheet (intended to be public) will scratch their heads, I'm afraid. And me too, a few months from now! Plus it doesn't seem to work with LibreOffice, sigh.

I tweaked a bit your previous formula to always return a "1" in case none of the conditions are met:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #005109}span.s2 {color: #9900cc}span.s3 {color: #003ecc}span.s4 {color: #92371f}</style>=INDEX(((1+E11:K11%)/(1+E$7:K$7%)-1)*100, 1+MAX(ISNUMBER(E11:K11) * (E$5:K$5) * (COLUMN(E11:K11)-COLUMN(E11))))

I added a column (notice the E to K range now), and the first column has an NA() in E11 while columns F to K hold the real data to analyze. And this all works perfectly with both Excel and LibreOffice. This seems a rather concise solution, which isn't too hard to understand. It's actually clearer than the LOOKUP() approach if you ask me.

Now is there a way to avoid the extra column by adding a small tweak to the formula, so that the array in the first argument of the INDEX function would start by the NA() value, then the regular computed values?

 

[pgc01]

You know that Index() doesn't like negative numbers, so you can change the zero value into for ex. -1

Instead of

Max(...)

use

Max(0+TEXT(...,"0;;-1"))

 

[siamond]

You know that Index() doesn't like negative numbers, so you can change the zero value into for ex. -1

Yes, I had the same idea and tweaked the formula as shown below. Works well on both Excel and LibreOffice, and it's reasonably concise/easy-to-understand.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #005109}span.s2 {color: #9900cc}span.s3 {color: #92371f}span.s4 {color: #003ecc}span.s5 {color: #2da33a}span.s6 {color: #ff8a17}</style>=IFERROR(INDEX(((1+E11:J11%)/(1+E$7:J$7%)-1)*100, MAX(IF(ISNUMBER(E11:J11) * E$5:J$5, (1+COLUMN(E11:J11)-COLUMN(E11)), -1))), NA())

I was kind of hoping that the following would work, but I was reaching, LOL!

=INDEX(((1+E11:J11%)/(1+E$7:J$7%)-1)*100, MAX(IF(ISNUMBER(E11:J11) * E$5:J$5, (1+COLUMN(E11:J11)-COLUMN(E11)), NA())))

Ok, I think I'm good. Thank A LOT for your help, much appreciated. And call me impressed about your expertise!

 

[pgc01]

I'm glad it helped. Cheers.