Hello Dave, Thanks for the below formulas.
The reason I am asking for the macro is that we have more then 25 sheet in a file and more then 200 line items in each sheet, macro would help to reduce the file size and minimize the changes of error. I have attached a file in which i have tried to write a macro. Two things that i am unable to add. 1 )The macro should able to apply the formula till the last line 2) It should copy and paste special value all the formula applied. Thanking you once again in advance. Thanks and Regards Atul Kesaria On Tue, May 26, 2009 at 2:47 PM, Dave Bonallack <davebonall...@hotmail.com>wrote: > Hi Atul, > Just as a by-the-way, those formulas are much longer than needed. There are > lots of redundant parts. The following would do the same thing: > > > =IF(AND(B2>0,B2<=1,0),1%,IF(b2<=2,3%,IF(b2<=3,5%,IF(b2<=4,7.5%,IF(b2<=5,8%,IF(b2<=6,10%,0)))))) > > > =IF(AND(b2>0,b2<=1,0),11%,IF(b2<=2,13%,IF(b2<=3,15%,IF(b2<=4,17.5%,IF(b2<=5,18%,IF(b2<=6,20%,0)))))) > > > =IF(AND(b2>0,b2<=1,0),21%,IF(b2<=2,23%,IF(b2<=3,25%,IF(b2<=4,27.5%,IF(b2<=5,28%,IF(b2<=6,20%,0)))))) > > A multiple IF statement stops when it finds the first true statement, so > you don't need to define the lower level of each range. > > Also, this sort of thing is done better using a table with VLOOKUP. You can > change the values more easily, and the formulas are much simpler. > > As to a macro, if no one else answers, I could provide you with that. > > Regards - Dave. > > ------------------------------ > Check out the new Windows Live Messenger Looking for a fresh way to share > your photos? <http://windowslive.ninemsn.com.au/article.aspx?id=792335> > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
SAMPLE.xls
Description: MS-Excel spreadsheet
