×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

IF; not for a cell, for a table

IF; not for a cell, for a table

IF; not for a cell, for a table

(OP)
Hi Guys,
If I want to check a cell is negative or positive I write:
=IF(A1>0,"OK","ERROR")
But I want to check a table,if there is even one negative number, I see ERROR.
Is that possible?

Thanks

RE: IF; not for a cell, for a table

You could try conditional formatting.  For example, you can set the format for each cell in the table so the text turns red if the cell is less than zero.  This would help to easily identify negative numbers in a table.  Its not exactly what you were looking for, but it might help.

Conditional formatting is under the format menu.  

RE: IF; not for a cell, for a table

Sure

why not try something like

=if((product(a1:b2))<0,"Error","OK")

works for me

Cheers

Greg Locock

RE: IF; not for a cell, for a table

Oops no it doesn't!

you need to use countif

=if(COUNTIF(a3:B6,"<0")>0,"error","ok")

 

Cheers

Greg Locock

RE: IF; not for a cell, for a table

To catch the error as it occurs:

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Select Case Target.Row
Case 2 To 6
If Target.Value < 0 Then
MsgBox "Must be non-negative"
Target.Select
End If
End Select
End If
End Sub
This is set for Column 4 (D) and rows 2 to 6

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: IF; not for a cell, for a table

(OP)
Thanks all.

RE: IF; not for a cell, for a table

If you don't need to know where the negative number is, why not simply:

IF(MIN(table_range)>0,"OK","ERROR")

RE: IF; not for a cell, for a table

drat, I was hoping nobody had posted that yet!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close