IF Statement - Payment Collection Spreadsheet - Cash Conversion to Non-Cash Payment Tracking

[steve5740]

Hello fellow members,

I appreciate you taking the time to view this post, and hope that I can fully explain the challenge that I need assistance with.

I am working on a project that we are working with our customers to no longer pay with cash, and have given the option to pay with check, credit card or eCheck.

My spreadsheet columns include Account Number(A), Account Name (B), Payment Date (C), Payment Type (D, this refers to Cash, Check, Credit Card, eCheck), Receipt Number (E, Check # if check), Payment Amount (F). Columns G-J include Cash, Check, Credit Card, eCheck. I have written an IF statement in these columns to count the times when the customer has paid by cash, check, credit card or eCheck. Next, I performed a Subtotal for all payment types.

My challenge is that I need to create an IF statement that will identify any customer that has paid with non-cash (check, credit card, eCheck) on the last 3 consecutive deliveries.

What is the best function to achieve the desired result? Thanks again.

 

[sergioMabres]

Hi Steve,
Here is an idea, I tried to do a formula where rows can be unsorted but I could not, so if you have a set like this

You sort the data by customer and date
Then you add to D2 the formula =IF(A2=A1,IF(AND(A2=A1,C2="Nocash"),1+D1,0),IF(C2="Nocash",1,0))
And in E2 the formula =IF(D2>2,"Yes","No")

This is the data I used:

customer date      pay by
a        7/2/2016  Nocash
a        7/17/2016 Nocash
a        7/21/2016 Cash
a        7/26/2016 Cash
a        8/17/2016 Nocash
a        9/25/2016 Nocash
a        9/28/2016 Nocash
b        8/10/2016 Cash
b        8/15/2016 Cash
b        8/17/2016 Nocash
b        8/20/2016 Cash
b        9/6/2016  Cash
b        9/12/2016 Nocash
b        9/13/2016 Cash
c        7/3/2016  Nocash
c        7/7/2016  Nocash
c        7/28/2016 Nocash
c        9/28/2016 Cash

Column D has the amount of consecutive Nocash delivers
Column E has if the amount is 3 or more consecutive Nocash delivers
I hope this helps
Cheers
Sergio

 

[steve5740]

Sergio,

Thank you for your reply.

This is very helpful, and has given me an idea on how to move forward with this. I may reach out to you again for further discussion.

Thank you again.

Steve Lopez

 

[steve5740]

Hi Sergio,

I need your assistance again.

How could I incorporate a statement into the existing statement that would identify a customer that has paid with noncash 3 times in a row but then paid cash 2 times in a row? I would like for the end result to say "Review" in Column E in your chart above.

Thank you again.

 

[sergioMabres]

Hi Steve
You can use this in E2: =IF(D2>2,"Yes",IF(AND(D1>2,C2="Cash",A2=A1),"No.",IF(AND(C2="Cash",A2=A1,E1="No."),"Review","No")))
Cheers
Sergio

 

[steve5740]

Hi Steve
You can use this in E2: =IF(D2>2,"Yes",IF(AND(D1>2,C2="Cash",A2=A1),"No.",IF(AND(C2="Cash",A2=A1,E1="No."),"Review","No")))
Cheers
Sergio

Hola Sergio,

Thank you again for your assistance. The above statement works however is there a way that it can be written to only say "Review" once after a customer has returned to Cash 2 times in a row.

For example: Customer A paid 3 times in a row with NonCash and Column E now says "Yes". Customer A then pays Cash 6 times in a row therefore every other row says "Review". Is there a way to only say "Review" at the second of six cash payments? I hope this makes sense.

Thank you,

Steve

 

[sergioMabres]

Sorry Steve I do not have the behavior you've described
See in the onedrive link how the formula is working for 3 nocash flowed by 6 cash
https://1drv.ms/x/s!AovCE1fDrrdSjSQDBV9RbvPMvcTb
See the sample in page Steve
Cheers
Sergio

 

[steve5740]

Hello Sergio,

The sample seems to give me the desired result.

Thank you!

Steve