Excel formula help [Solved/Closed]

Report
Posts
4
Registration date
Wednesday February 19, 2014
Status
Member
Last seen
February 19, 2014
-
 Blocked Profile -
Hello,

I would like to request some assistance with a formula I have been trying to figure out. I know it's a simple one, but it evades my mind.

I have 4 columns:

A1-NAME, B1-OWE, C1-PAYMENT and D1-PAID OR NOT

How can I make D1 write "NEEDS PAID" or "PAID" IF the following conditions are met:

Row 1, Column D
A1 has a name and B1 has a numeric figure, I want D1 to read, "NEEDS PAID"
IF C1 equals B1 (total paid) then D1 will read, "PAID"

IF A1 does NOT have a name, I would like D1 to be blank.

I do hope I explained this really simple for anyone to assist with.

Thank you,
Joe

4 replies


=IF(A1<>"",IF(AND(C2>=B2,B2>0),"Paid",IF(AND(A2<>"",B2>0),"Needs Paid","")),"")

Ok let us break down the nest:
first it:
=if(a1<>"", true, false)
*a1=true
**IF(AND(C2>=B2,B2>0),"Paid" This is where the code can start getting messy, and it is all logic related. If you understand the nested logic, then modify to your own specifications. In the above example, c2 has to be equal or greater than b2, AND B2 has to be greater than 0 to satisfy the true (displays PAID)

Let me know if this gets closer!
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Wednesday February 19, 2014
Status
Member
Last seen
February 19, 2014

No, not homework my friend. I am a marketing coordinator for a communications company looking for a easier way to track my reps money logs.

Okay, I modified this a tad.. Let me give you the values and what it's doing.

A1 NAME
B1 AMOUNT OWED
C1 PAID AMOUNT
D1 STATUS

A1 = if blank, D1 needs to be blank

With your current formula, if A1 is blank, D1 shows "Paid".

=IF(C2=B2,"Paid",IF(AND(A2<>"",B2>0),"Needs Paid",""))
(you used row 2, but it's all the same for what I need to do)

I need to check also that if A1 has a name (text) that it does a check to see if C1 is equal to B1. If C1 is NOT equal (total owed is paid), then D1 needs to state "Needs Paid" but if it's fully paid (B1 and C1 are equal), D1 states "Paid".

I have really confused myself so I took a breather.
Posts
4
Registration date
Wednesday February 19, 2014
Status
Member
Last seen
February 19, 2014

Here is a screenshot of what it's doing with no text in A2.

http://s28.postimg.org/car2rje59/paid.jpg
Posts
4
Registration date
Wednesday February 19, 2014
Status
Member
Last seen
February 19, 2014

You're BRILLIANT! +1 for your wonderful, dedicated help!
Blocked Profile
Thank you. I am here to help. Come back often with anything.