greater than less than or round up

[Slow Learner]

Hi All,

I am trying to construct a formula for the following
If B4 is between 1-200 it returns 200 if between 201-230 it returns 230 and if over 230 it rounds to the nearest 5
Any help with this would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(AND(B4>=1,B4<=200),200,IF(B4<=230,230,MROUND(B4,5)))

 

[Slow Learner]

Hi & welcome to MrExcel.
How about

=IF(AND(B4>=1,B4<=200),200,IF(B4<=230,230,MROUND(B4,5)))

That's fantastic!
Is there anyway of it showing a 0 if there is no data in B4 ?

 

[Fluff]

How about

=IF(B4="",0,IF(AND(B4>=1,B4<=200),200,IF(B4<=230,230,MROUND(B4,5))))

 

[Slow Learner]

How about

=IF(B4="",0,IF(AND(B4>=1,B4<=200),200,IF(B4<=230,230,MROUND(B4,5))))

I wish I had come here sooner!!
One last thing so apologies I need it to round up to the nearest 5 not round to the nearest 5 like I previously stated. Sorry to be a pain.

 

[Fluff]

For that, just change mround to ceiling.

 

[Slow Learner]

For that, just change mround to ceiling.

Thanks it's working fantastically now. Your help on this is greatly appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.