What formula/filter/lookup can I use to accomplish this task???

Hello guys,

I need help with a formula or come up with a way for this type of filtering/search I am doing. The business I work for has many different business units under it. Suppose John and Jimmy, etc. are our customers. 90% of all the customers have purchased ABC-1 product. There’s 2 parts to this problem:

I need to come up with a formula that spits out this data for me: the sum of all products excluding “ABC-1” divided by 2018 total is 30% or higher. I also need to implement a filter in this formula that disregards every 2018 total that is under 100k (we are not worried about tracking that type of scenario).





Sub-business, 2018 total

Jimmy, 250000

ABC-1, 170000

Apples, 50000

Oranges, 30000

In the case of Jimmy: (Apples+oranges)/2018 total = 32% or (50+30)/2018 total=0.32



Sub-business, 2018 total

John, 472000

ABC-1, 262000

Potatoes, 50000

Carrots, 40000

Grapes, 70000

Strawberries, 50000

In the case of John: (Potatoes+Carrots+Grapes+Strawberries)/2018 total = 44% or (50+40+70+50)/2018 total = 0.44



2) As I mentioned 90% of the sub-businesses have purchased “ABC-1” product, but I also need to come up with a formula in a different column that finds me all the customers who have not purchased “ABC-1” product at all, and their 2018 total is greater or equal to 100k.





Is this doable using filters, lookups, or formulas in Excel? If yes, I would need help with the formula or how to go about doing this type of filtering/search. Just manually checking all the sub-businesses is not feasible in my case, as we have more than 50000 rows. So far I have found a few sub-businesses that are part of the first part of the problem just by manually checking and I have also found a few that fit the second part of the problem. But again, I need to come up with a formula or pivot, or filter on how to do these for all the 50000 rows of data…

Thank you in advance

Sounds doable. Would be much easier to direct you if you could post a sample workbook with some sample data. The problem I'm having with giving you direct solution is that I need to see how the data is organized in the workbook.

I have created the exact setup of my business. Sorry for attaching a picture but I can’t seem to attach the simulated file itself. I need to come up with the formulas for column K-L-M

Column K & L will have the same formula since the same information is required for both years.

There are many slicers/fields/pivots in the workbook that I am working with since it is contains all the products that my business sells so I hope that the formulas for columns K-L-M won’t get messed up if different managers filter by different parameters/sub-businesses.


Thank you.
Attachment 626449

I get this message when trying to download your attachment.

Capture.JPG

Did you use Go Advanced -> Manage Attachments -> Upload?

I uploaded the excel file. Are you able to access it?

I have updated the layout and have also included some sample results. The layout mirrors the data I'm working with 100%. Hopefully this will make it more understandable of what I'm trying to achieve.

Yes, I can change it if that will allow me to get the data I'm looking for. What suggestions do you have to change it?

Ok, I just had some time to come back to your request. I have attached a workbook that accomplishes what you were looking for, but also gives you an idea about how to layout the data in different ways.

I've included 4 tabs: The first three tabs, "Less Flexible", "More Flexible", and "Most Flexible", show different ways you can layout the data. The last tab "Most Flexible Pivot Examples" shows why the last data layout is superior. It allows you to create quick and simple pivot tables that report exactly what you are wanting and can be changed quickly to give more robust information. I have defaulted the two pivot table examples to demonstrate how they provide the information you were looking for in your original question.

If you don't know much about pivot tables, there are tons of online videos demonstrating how to create and use them. Even a 15 minute lesson on using pivot tables will open up your mind to the power of Excel and allow you to create some really nice reports very quickly. Enjoy!