Hi, I would like to categorise the data in a culumn as either D (day), W (week), M(month) or Q (Quartler). The formula needs to say... If Cell A1 = 1, then input "D", Else if between 1 and 6, then input "W", else if between 5 and 21, then input "M", Else if between "20 and 61, then input "Q", otherwise 0.
Would someone know the proper string for this forumla. Much appreciate any help. Thx.
Last edited by surfer1; 07-04-2013 at 08:00 PM. Reason: amending improper title.
Administrative Note:
•We would love to continue to help you with your query, but first, before we can proceed…
•Please see Forum Rule #1 about proper thread titles and adjust accordingly...
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Your data ranges contradict one another.
You say if A1 is from 1 to 6 the result is W and from 5 to 21 the result is M
Assume A1 is 5. Whats the result, W or M? Both conditions are true.
Again, if A1 is from 5 to 21 the result is M and from 20 to 61 the result is Q.
Assume A1 is 20. What's the result M or Q? Both conditions are true again.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Most of your ranges overlap.
Can you clarify them?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the title change
Maybe something like this?
=IF(A1=1,"D",IF(AND(A1>1,A1<6),"W",IF(AND(A1>=6,A1<21),"M",IF(AND(A1>=21,A1<61),"Q",0))))
sorry guys, if the number is 1, then it's D, if the numbers are 2-5, then W, if the numbers are 6-20, then M, and if the numebrs are 21-60, then Q. Sorry for the confusion. cheers.
Based on the revised description
=LOOKUP(A1,{1,2,6,21},{"D","W","M",Q"})
One way...
=IF(OR(A2<1,A2>60),0,IF(A2>=21,"Q",IF(A2>=6,"M",IF(A2>=2,"Q","D"))))
did you see post #5?
Hi FDibbins, I did. many thanks. I was just working out how to write the string to say "from one number to another". That formula is great. Cheers.
Your formula works great, but not elegant. You don't need whole bunch "AND" functions in this case.Originally Posted by FDibbins
Original:
=IF(A1=1,"D",IF(AND(A1>1,A1<6),"W",IF(AND(A1>=6,A1<21),"M",IF(AND(A1>=21,A1<61),"Q",0))))
Removed "AND" functions:
=IF(A1=1,"D",IF(A1<6,"W",IF(A1<21,"M",IF(A1<61,"Q",0))))
If A1 is <1 or is an empty cell then that formula will return W. Of course, we don't know if an entry <1 is a possibility.
Teethless / Tony, Fantastic! That's exactly what I was after. Many thanks all; really appreciate your prompt help. Have a good day. cheers, all.
teethless, thanks for the lesson
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks