Check four conditions return TRUE or FALSE

**mkvassh** · 11-16-2009, 05:56 AM

I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:

Please Login or Register  to view this content.

**DonkeyOte** · 11-16-2009, 05:59 AM

How about

=SUM(A1:D1)=0

**mkvassh** · 11-16-2009, 06:52 AM

It works, but I can't use it on the example below since it totals to zero. I need a FALSE in the example since there are individual balances.

1000 -1000 0 0 TRUE

**DonkeyOte** · 11-16-2009, 06:57 AM

Numerous ways really... another then might be

=MAX(ABS(MIN(A1:D1));MAX(A1:D1))=0

another

=SUM(COUNTIF(A6:D6;{">0"\"<0"})*{1,1})=0

**mkvassh** · 11-16-2009, 07:13 AM

It works but why do you use ABS on the MIN value. It will work without ABS?

**DonkeyOte** · 11-16-2009, 07:17 AM

It will work without ABS?

Not necessarily, no. Consider

A1:D1
-1000 0 n/a 0

E1: =MAX(MIN(A1:D1);MAX(A1:D1))=0 --> returns TRUE as MAX is 0

whereas

E1: =MAX(ABS(MIN(A1:D1);MAX(A1:D1))=0 --> returns FALSE as MAX becomes 1000

If you wish to avoid ABS you could switch to an AND based test, ie:

E1: =AND(MIN(A1:D1)=0;MAX(A1:D1)=0)