Logical condition in IF statement

[AK]

Is there any easy way to write a formula which will do the following:


=if(concatenate(a2*b2-c2+d2*e2)="",NA,concatenate(a2*b2-c2+d2*e2))


The reason is if there is complex calculation, then the 'if' statement becomes too long and also changes have to be done at 2 places.


Regards

AK

 

[Hui]

Here is a quick one

=+IF(SUM(A2:E2),CONCATENATE(A2*B2-C2+D2*E2),+NA())

I wasn't sure if you meant "NA" or NA()

 

[Hui]

or you could use a named range like


Test =A2*B2-C2+D2*E2


and change the formula to be


=+IF(SUM(A2:E2),CONCATENATE(Test),+NA())

 

[Hui]

AK

I never asked why do you need the Concatenate around the formula?

as

=IF(SUM(A2:E2),+A2*B2-C2+D2*E2,+NA())

will do the same job without concatenate?

 

[oldchippy]

Hi,


I'm not sure I understand where SUM(A2:E2) comes into this? If A2:E2 has 1,2,3,4,5 then the SUM is 15, but A2*B2-C2+D2*E2 is 19 and if A2:E2 where blank then it would be zero not "". So maybe this?


=IF(A2*B2-C2+D2*E2=0,NA(),A2*B2-C2+D2*E2)

 

[Hui]

If there is anything in A2:E2 then Sum(A2:E2) will be a value and the If will pickup that Sum(A2:E2) is true and execute the formula

If all are blank or 0 the Sum(A2:E2) will be false and the If will execute the NA()

 

[oldchippy]

Thanks for the explanation Hui

 

[AK]

Thanks for the explanations.


What I had put forth was just an example, sorry for the confusion it caused. In reality I wanted to do this


concatenate(A2," - ", b2, " - ", c2) where a2, b2 and c2 are strings. If c2 is empty then the resultant string will have an hyphen at the end, which I wanted to remove.


So using named ranges, my formula became


if(right(Range1,3)=" - ",left(Range1,len(Range1)-3),Range1)


where Range1 is a named range for "=concatenate(A2," - ", b2, " - ", c2)"


Earlier I had to repeat the entire concatenate function at 4 places in the formula above.


Hence, using a named range did the trick.


Thanks