Clever truth table formula

[JVink]

Hi,

I want to calculate a result of 3 values depending on a parameter that can be L or S. I either use a linear sum (in case of L) or RSS (in case of S) or a combination. In order to avoid a very long formula I created the formula below for a few cases. But I no realize that i want to do all cases of the truth table.

=SQRT(SUMSQ((AA6=“S”)*Z6,(AC6=“S”)*AB6,(AE6=“S”)*AD6))+NOT(AA6=“S”)*Z6+NOT(AC6=“S”)*AB6+NOT(AE6=“S”)*AD6

Question: is there a neat way to create a clever formula comprising all 8 combinations?
Note: I can’t attach an example document because my karma… Note that I need to copy this formula to many other cells (for other values) and don’t want to create intermediate results somewhere else in the sheet. I also would like to avoid a UDF because I need to use it in Excel as well.

[JohnSUN]

May be function CHOOSE? =SQRT(CHOOSE((AA6=“S”)*4+(AC6=“S”)*2+(AE6=“S”)+1;SUMSQ1;…;SUMSQ8))

[mariosv]

I think a formula like this can do the job, changing the ranges as you need:

={SQRT(SUM((Z6:AD6*(AA6:AE6="S"))^2))+SUM((Z6:AD6*(AA6:AE6<>"S")))}

This is an array formula so do not write the braces { }, and use Ctrl+Shift+Enter to enter the formula.

[JVink]

Ah Mario,

Yes, that is the way to go. I’m not very fluent in matrices, that’s why I didn’t think about it. I get an error in my sheet Err:512 but I’ll figure out what I’m doing wrong.

Thanks, Jaap