Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula - Value Check (2002)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula - Value Check (2002)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •