Forum Discussion
Complicated formula to search for certain data
Okay, how it works. I'll be based on my example, i.e. on formula in the helper cell for row #2.
=IF(SUMPRODUCT(($D$2:$D$82=$D2)*($A$2:$A$82>=($U$1-90))),"Pays", "Didn't pay")
First in SUMPRODUCT
($D$2:$D$82=$D2)
takes your entire range from second to 82nd rows and compare with D2. If it match it returns 1 (TRUE), othwervise FALSE (0). As result you have an array like {1,0,0,...1,..}
Second expression
($A$2:$A$82>=($U$1-90)
returns for the same range an array where the payment was done within last 90 days (TRUE/1) or not (FALSE/0). As the result an array like {0,0,...1,...}.
After that SUMPRODUCT do exactly what it name means, i.e. sum of these two arrays product. In product of these arrays you have 1 where the customer is the same as in D2 and payment was done within lates 90 days; otherwise 0. Resulting array before sum will be like {0,0,...1,0,...}. The sum on it returns how many times the customer in D2 paid within latest 90 days.
When you repeat the same for every row, i.e. for customers in D3, D4, etc. If number of payments was >0 for the customer in the row (TRUE) you assign the value "Paid", othrewise (FALSE) "Didn't pay".
Not sure my explanantion is clear enough. You may check how SUMPRODUCT works for example here https://exceljet.net/excel-functions/excel-sumproduct-function
Next step - better to convert your data range into the table (if you are on Excel 2007 or later). When you may use structured refrences and don't depend on concrete range size when it changes. Select your data and in Ribbon->Home->Format as table. Our helper formula will be now
=IF(SUMPRODUCT(([Customer]=[@Customer])*([Value Date]>=($U$1-90))),"Pays", "Didn't pay")
see Table tab in the attached file
I also solved a problem with products, I used pivot table - silly me, never thought about that before :D
Had a little problem with transferring the formula but it finally worked for the whole file, thanks :)
Here is the file with formula. Please note i removed transactions from the bottom to make the file less.
Products - okay, understood.
there is some mistake in this formula cause when I'm addin that, the outcome of formula is always just "FALSE"
How many diffrent products per cusotmer, sorry for not clarifying.
Hi Bartosz,
Transactions: you may add one more column to the table ("Currency")
=[@[DR_CCY]] & "/" & [@[CR_CCY]]
or like, make one more pivot table with Customers in rows, Currency in columns and Count of Currency in columns. Add filtering if necessary.
Products: not sure right now. Please clarify if you'd like to know how many different product per customer (doesn't matter which ones); or how many customers are with same product; or what.
- thank you for your time and response. Table looks great! We are almost in the end of my data analysis task. Two more questions, how to count how many transactions every customer made by currency pair. For instance customer made 2 transactions on EUR/USD and 3 transactions on GBP/USD. How to count that? And last one would be counting the number of product used by customer. I erased product names from table but I hope that its doable even without it. Thank you Sergei for your help! Is there any way I could maybe help you?
And if to compare payments in prev month to the payments month before the previous.
I added two columns - PaidPrevMonth
=IF(AND([@[Value Date]]>EOMONTH(TargetDate,-2),[@[Value Date]]<=EOMONTH(TargetDate,-1)),[@[Revenue $]],0)
and PaidMonthBefore
=IF(AND([@[Value Date]]>EOMONTH(TargetDate,-3),[@[Value Date]]<=EOMONTH(TargetDate,-2)),[@[Revenue $]],0)
after that calculated field Payments to pivot table
= (PaidPrevMonth< 0.4*PaidMonthBefore)
(better <= on real figures)
All 3 are added to the values of the pivot table with SUM aggregation, after that filter values as Sum of Payments = 1
Details are in attached file. Not sure everything works correctly, no data in source file to test.
Hi Bartosz,
In Excel operations with the arrays are time consuming, let try to do the same without the arrays and without using of data model.
First, i'd highly recommend you to transform your data range into the table, that will be much easier to work. For your file - stay on A1, press Shift+Ctrl and after that arrow down key. Keep Shift pressed, by right arrow key expand the selection till colimn O and in Ribbon find Format as Table to do. After that better to change the name of the table on something more human friendly, let say Transactions
Let put in U1 the date with which we will compare dates transactions and in Ribbon->Formulas->Name Manger add the name for this cell, let it'll be TargetDate
Stay on any cell in most right Team column, right click, Insert, Insert table column to the right. Change the Column1 name on more suitable, DaysBefore in my case.
Into any empty cell of this column add the formula
=TargetDate-[@[Value Date]]
Not necessary to type the second part, after you type "-" (minus) click on the cell in in same row and first column, Excel will substitute structured refrence. Enter and the result immediatelly will be in all cells of the column.
After that add pivot table, customers in Rows, DaysBefore in Values. Change aggregation for DaysBefore from Sum on Min.
From the Row Labels drop down menu select Values Filter, here Greater Than, and use 90 to select customers who didn't pay for more than 90 days before target day.
Please see attached file. Days frequency is also here. Not sure right now about comparing paid sums, perhaps some later.
That makes the life harder. Microsoft added data model directly into the Excel starting from version 2013. To work with it in 2010 you have to install some add-in which could be not possible in corporate environment.
Okay, i'll think what could be done with not using data model.
- Im on 2010 version
Okay, good. On your big file performance could be an issue with current formula. I could play with that, but need to know on which version of Excel you are (2007, 2010, 2013 or 2016) assuming you are on Windows desktop.
As for the frequency the simplest way is to add one more column with day of month
=DAY(<date>)
and after that pivot your table with Customers names in rows, Days in columns and Count of customer names in Values.
- Sorry for the trouble, I forgot to adjust formula to cell number :) it works now thank you!
Just one moge question do you maybe know how to find out about frequency? if I would like to check if there is a pattern about on which day the customer usually makes transactions? Hi Bartosz,
Okay, i'll check bit later today, will be back then
Ok great, so formula is doing exatcly what I need. But it seems to still doesnt work on my real data. Im uploading a real file in which it should work.
For instance look at client 34652 he made transactions every month and it says "didnt pay" as his answer
