Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR IF AND
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR IF AND
=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) "Jake" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|