Shortened formula "Rob" <anonymous[ at ]discussions.microsoft.com> 29.09.2006 19:46:47 Hi, I have a need for a formula whereby the follow criteria applies: if D6 = 0, return 0, if D6 is between .01 and .49 = .49, if D6 is between .50 and 1.24 = .99, if D6 is between 1.25 and 2.24 = 1.99 and so on. Is there a shorter formula that someone else could understand easier than this one which works but seems long winded? =IF(D6=0,0,IF(D6<0.5,0.49,IF(D6<1,0.99,IF(D6-INT(D6)<0.25,INT(D6)-0.01,IF(D6-INT(D6)<0.5,INT(D6)+0.49,IF(D6-INT(D6)<1,INT(D6)+0.99)))))) Thanks, Rob

RE: Shortened formula Ron Coderre 29.09.2006 20:03:01 I believe the VLOOKUP function will suit your situation. See Debra Dalgleish's website for instructions: http://www.contextures.com/xlFunctions02.html#Range Does that help? *********** Regards, Ron XL2002, WinXP "Rob" wrote: [Quoted Text] > Hi, > > I have a need for a formula whereby the follow criteria applies: if D6 = 0, > return 0, if D6 is between .01 and .49 = .49, if D6 is between .50 and 1.24 > = .99, if D6 is between 1.25 and 2.24 = 1.99 and so on. Is there a shorter > formula that someone else could understand easier than this one which works > but seems long winded? > > > =IF(D6=0,0,IF(D6<0.5,0.49,IF(D6<1,0.99,IF(D6-INT(D6)<0.25,INT(D6)-0.01,IF(D6-INT(D6)<0.5,INT(D6)+0.49,IF(D6-INT(D6)<1,INT(D6)+0.99)))))) > > Thanks, Rob > > >