OR IF AND

J

Jake

I have an invoice tracking worksheet with the following colums:
Bid amount - Estimate on a job (F2)
Invoice Amount - Actual amount invoiced for job (G2)
Over/Under - Formula: =IF(ISERR(SUM(F2-G2)),"",(SUM(F2-G2))) (J2)
Comments - Formula: =IF(AND(J2>=-49.99,J2<=29.99,J2<>""),"OK","") (K2)

1. I need to get the "Over/Under" column to be blank if there's no amount
in the "Invoice Amount" column and I cant seem to get it.
2. I want to add another "parameter" to the "Comments" column formula. We
track that if an invoice is between certain amounts its ok, but if beyond
those, its not ok (or Over). So how can i get the cell to show Blank if no
amounts are entered, OK if the invoice is between those amounts listed in my
current formula, and Over if its beyond those (like J2<=-49.99, J2>=29.99).

Or can this even happen? I've been working on it all morning.
Thanks in advance!
 
B

Bob Phillips

=IF(OR(G2="",ISERR(F2-G2)),"",F2-G2)

and

=IF(J2="","",IF(AND(J2>=-49.99,J2<=29.99),"OK","Over"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top