Hey guys, I'm trying to wrtie an IF statement to display one of Q1, Q2, Q3 or Q4 based on the three letter month abbrev. All i have so far is the following, which isn't leading anywhere - and i have a feeling theres an easier way to write it. Thanks!
=IF(((OR(G2="Jan","Feb","Mar"),)),"Q1",""),IF(OR(G2="Apr","May","Jun"),"Q2","")
wrong application of OR()
=IF(OR(G2="Jan",G2="Feb",G2="Mar"),"Q1",IF(OR(G2="Apr",G2="May",G2="Jun"),"Q2",""))
if you just put a real month in cell g2 eg 01/jan/2009 format cell as mmm
then
="Q"&CEILING(MONTH(g2)/3,1)
OR with text
="Q"&IF(OR(G2={"jan","feb","mar"}),1,IF(OR(G2={"apr","may","jun"}),2,IF(OR(G2={"jul","aug","sep"}),3,4)))
Last edited by martindwilson; 11-16-2009 at 07:13 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Ok, thanks. Still trying to learn a lot of the functions as i've never used most of them, even the basic ones like OR.
Ok, ill try that formula too. Its two steps. The original cell will have a date in it, and another cell will convert that to just the three letter abbrev, then the quarter, which i couldnt do.
Here's one way: ="Q"&INT((MATCH(G2,{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)-1)/3)+1
Where A1 is the abreviation "Jan", "Feb" etc
Please Login or Register to view this content.
And Months refers to ={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
With the 3 letter abbreviation in G2 you can get the quarter with this formula
="Q"&INT((MONTH(1&G2)+2)/3)
You can use OR similar to what you had thought, but not exactly.
=IF(OR(G2={"Jan","Feb","Mar"}),"Q1",IF(OR(G2={"Apr","May","Jun"}),"Q2",IF(OR(G2={"Jul","Aug","Sep"}),"Q3","Q4")))
Another option, so there's only one test, would be to use:
="Q"&LOOKUP(G2,{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"},{2,3,4,1,1,3,2,1,2,4,4,3})
Thanks for all the suggestions! They all work great and now i can try and apply/learn what they actually mean instead of just copying.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks