# Thread: Array Formula - Value Check (2002)

1. ## Array Formula - Value Check (2002)

I have table of data in the following cells: k7 down to k70 all the way through v7-v60. Nothing of importance in row 1-6. Is it possible to have a formula that would look at each row separately and place a "show" in the corresponding cell in column w if any value in the row was >= \$100,000 or <=-\$100,000 and a "Don't show" in the same cell in W if this criteria isn't met?

For example if row 8 had \$150,000 in k8 then w9 would say "show". If row 9 did not have a value meeting the above it would say "Don't show".

Thanks.

2. ## Re: Array Formula - Value Check (2002)

Does this Array Formula do what you want?

<code>
=IF(SUM(--(K7:V7>=100000)),"Show","Don't Show")
</code>

3. ## Re: Array Formula - Value Check (2002)

What is the purpose of the -- to the right of sum( ?

4. ## Re: Array Formula - Value Check (2002)

The result of K7:V7>=100000 is a series of TRUE/FALSE values. The double negative sign forces these to numeric values 1 and 0 that can be summed.

5. ## Re: Array Formula - Value Check (2002)

Just discovered it doesn't work for values <=-100,000... How do I add this feature?

6. ## Re: Array Formula - Value Check (2002)

Try this as an array formula (confirm with Ctrl+Shift+Enter):
<code>
=IF(SUM((K7:V7>=100000)+(K7:V7<=-100000)),"Show","No Show")
</code>
The use of + forces the expressions on either side to be interpreted as numbers, so no need for the -- this time.

7. ## Re: Array Formula - Value Check (2002)

Perfect - thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•