one column that returns a text based on criteria in other cells

Hi all,

I have a whole lot of customer data in excel that I am trying to group into categories based on criteria in other cells.

Does anyone know of a formula to be able to do this?

"Newbie" - registration date is less than 2 months old =IF(EDATE(I2,2)>TODAY(),"Newbie")
"Freeloader" - customer is not a "Newbie" and cells M, O , and P are blank.
"Packer" - customer is not a "Newbie" and the date in cell M is less than 2 months from today AND cells O and P are blank or greater than 2 months from today

Any ideas?

Hi,

There are a few subtleties in what you are asking for which I'm not sure about, so I'll provide a formula that you can test and tell you what I want verification on, then you can let me know how the formula needs to be edited for your use. This also assumes that you are working in row 2, so adjust this as necessary:

Formula:

Please Login or Register  to view this content.

Things that need verification:

1. For "Packer", you said "the date in cell M is less than 2 months from today", so I have assumed you meant 2 months either side of today. Please let me know if this needs to only be 2 months before OR after.
2. For "Packer", you said "cells O and P are blank or greater than 2 months from today", so I have assumed that if either O OR P meets this condition then they are not a "Packer", but please correct me if this needs to be O AND P meets the condition.
3. This will return blank ("") if the date in any of the reference cells is EXACTLY 2 months from today, so you need to let me know how to categorise EXACTLY 2 months, as you have only mentioned "is less than" and "is greater than", but not "is equal to".
4. If none of the conditions that you have requested hold true, than a blank is returned. Let me know if you want this changed.

Hope this helps somewhat

thanks so much for your help!

It works! is it possible to add a few fields?

for "Freeloader" - how do I add to the criteria "customer is not a "Newbie" and cells M, O , and P are blank. " Or the date in cells M, O, and P is greater than 3 months from today?
So if it is not blank in the cells but the date is older than 3 months ago from today they are still a freeloader.

Also can i add in a
"Demander" - customer is not a "Newbie" (column I date is older than 2 months from todays date) and column O date is less than 3 months from todays date.
"Spectator" - customer is not a "Newbie" (column I date is older than 2 months from todays date) and column P date is less than 3 months from todays date.


thank you for your help, I really appreciate it.

Lol sure, try this:

Formula:

Please Login or Register  to view this content.

I haven't tested this to its full extent as there are far too many possibilities to test without knowing your data structure, but feel free to let me know of any changes that are required once you have had a chance to test it for yourself

Cool, I'm having trouble with the Freeloader category. It is pulling through blank at times so I must be missing a criteria. I have attached an example.

basically, if the registration date (column I) is more than 3 months ago and columns M, O, P are more than three months ago or blank then they are a Freeloader

I hope this makes sense!

Sorry, my mistake. Try this one

Formula:

Please Login or Register  to view this content.

I have one more addition/change if my requests havent run to thin!...

I tried to add in another category "Utopia" if there is a date in all three columns M, O, and P that is less than 3 months from today.

Here is what I tried adding but it is not working...

HTML Code: 

Are you able to help?

Here is the formula that you are looking for:

Formula:

Please Login or Register  to view this content.

However, I have observed that you will never be able to reach "Utopia" because it is blocked by overlapping conditions from "Demander" and "Spectator"

aaarrrgghh! good point! yeah I might be over complicating things now. Thanks heaps for all your help on this.

No worries!

Please don't forget to mark this thread as solved and please click the star * next to my post to say thank you if you don't mind