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.

 

[Special-K99]

Might not be practical.
Your formula is based on E11-J11 and E5-J5 with rightmost columns taking precedent - so only one calculation will result since once an IF is executed the rest of the formula does not get executed.
If E11-J11 or E5-J5 is entered by a user, and they enter more than one value in that row then your array will calculate on ALL user data entered which would result in the wrong calculation.

 

[pgc01]

Hi siamond
Welcome to the board

Remember that AND(A,B) is only True if both A and B are True.

This means that

=IF(AND(A,B),C,D)

is equivalent to

=IF(A,IF(B,C,D),D)

You can use this in your formula looking for the last result,since your formula wants the first right to left result.

Try:

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

 

[pgc01]

Remark:

The formula that I posted is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

 

[siamond]

Try:

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

Brilliant! Exactly what I was looking for! I tried, worked perfectly.

The main trick is to use LOOKUP to enforce the priority order, that is quite creative, well done.

Now I don't quite understand why I can't keep the AND() part of the formula. Yes, it doesn't work, but why?

<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: #2da33a}span.s5 {color: #003ecc}</style>=(LOOKUP(1E+300, IF(AND(ISNUMBER(E11:J11), E$5:J$5), (1+E11:J11%)/(1+E$7:J$7%), NA()))-1) * 100

 

[pgc01]

Now I don't quite understand why I can't keep the AND() part of the formula. Yes, it doesn't work, but why?

This excel AND() is not the one you'd need for this formula.

The excel AND() performs the AND of all the parameter values and returns the total AND value.

For ex., if you have

=AND(A1:A3,B1,B3)

you get the value of =AND(A1,A2,A3,B1,B2,B3) which would be just True or False

What you'd need for the formula is something like and AND_ARRAY() that would return not a scalar but an array.

Ex., a function that out of

=AND_ARRAY(A1:A3,B1,B3)

would return an array {AND(A1,B1),AND(A2,B2),AND(A3,B3)}, which would be something like {True,False,False}

If this were the case, if you had such a function, then you could use it in the formula the way you wanted.

Hope it's clear.

Remark:
Sometimes, instead of the 2 If's you use Condition1 * Condition2 which will also return an array value.

For ex.:

=IF((A1:A3=3)*(B1:B3="OK"),...

This is the equivalent to and AND array function that would return and array with the AND's of each pair of conditions.
The return of the expression would be something like {1,0,1} which you could use as the condition in the IF()

 

[siamond]

Sometimes, instead of the 2 If's you use Condition1 * Condition2 which will also return an array value.

Ah ah!

So I CAN further simplify with:


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


That works and is quite elegant. Very cool.



<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}</style>

 

[siamond]

I have one last problem though. Either version of the array formula doesn't work well enough with LibreOffice (I do my work with Excel, but some folks use my -public- spreadsheet with LibreOffice, so I try hard to stay compatible).

Problem is, if you have a vector like 3 #N/A 2 #N/A #N/A, Excel will rightfully return '2', and so does the clunky nested formula I started from. But stupid LibreOffice returns 3... It appears to stop scanning the vector at the first error value (I tried with other types of error, same issue). Hmpf.

Any idea? Any other way to get the last meaningful value out of a vector of numbers?

 

[siamond]

^^^^ Maybe I wasn't clear, I was speaking of the LOOKUP function not behaving the same way on LibreOffice vs. Excel.

 

[pgc01]

I don't know LibreOffice.

If LookUp() does not work for your problem you can try another solution.

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 calculationwith for ex. Indirect() or Offset(). You'll have to see how these functions behave in LibreOffice.