MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Limiting output range


Posted by Paul on January 29, 2002 9:38 PM

G'day everyone,

I've got a slight problem in a sheet I'm doing.

I've got a formula : =ROUNDUP((C6/5),2)
I want the output to display a value only between 1.00 & 3.00 (inclusive). So 0.20 becomes 1.00 or 6.40 becomes 3.00.

I've got no idea how to do it. Can anyone help?

Thanks



Posted by anno on January 29, 2002 11:16 PM

paul
i think this will do what you want:
=IF(C6<1,1,IF(C6>1<2,2,IF(C6>2,3))) and format the cell to 2 decimal places.

Posted by anno on January 29, 2002 11:24 PM

nope, that doesn't do what you asked. back to the drawing board.

Posted by Bertie Bagshot on January 30, 2002 12:19 AM

=IF(ROUNDUP((C6/5),2)<1,1,IF(ROUNDUP((C6/5),2)>3,3,ROUNDUP((C6/5),2)))

Posted by Paul on January 30, 2002 3:29 AM

BRILLIANT!

It works thankyou very much.

:)