Oops The below should read: Is A5 less than or equal to T5? - No - Go to the next statement Is A5 less than or equal to T6? - No - Go to the next statement Is A5 less than or equal to T7? - Yes - Stop. Answer: S7
Dave. From: davebonall...@hotmail.com To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula Date: Mon, 1 Aug 2011 12:50:31 +0800 Hi, Try this: =IF(A5<=$T$5,$S$5,IF(A5<=$T$6,$S$6,IF(A5<=$T$7,$S$7,IF(A5<=$T$8,$S$8,IF(A5<=$T$9,$S$9,IF(A5<=$T$10,$S$10,"")))))) The multiple IF statement stops as soon as it finds a TRUE. So you don't need to use AND statements to create 'windows' The above asks: Is A5 greater or equal to T5? - No - Go to the next statement Is A5 greater or equal to T6? - No - Go to the next statement Is A5 greater or equal to T7? - Yes - Stop. Answer: S7 This could probably be easier done with VLOOKUP Regards - Dave. > Date: Sun, 31 Jul 2011 17:50:12 -0700 > Subject: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula > From: velocity...@gmail.com > To: excel-macros@googlegroups.com > > Any help in shortening or optimizing this formula would be great. Im > limited by the nested if statements to match week numbers (column s) > and week ended date (Column T), and continue to bog down the > recalculations. It does work, but slows things down.....I'm using it > to determine if a date falls within a certain period in Column T > > > =IF(A5<=$T$5,$S$5,IF(AND(A5>$T$5,A5<=$T$6),$S$6,IF(AND(A5>$T$6,A5<=$T > $7),$S$7,IF(AND(A5>$T$7,A5<=$T$8),$S$8,IF(AND(A5>$T$8,A5<=$T$9),$S > $9,IF(AND(A5>$T$9,A5<=$T$10),$S$10,"")))))) > > > many thanks, > > Financeguy > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
