Shortened formula
 

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

Shortened formula
 

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:

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