Excel Formula ?

Occasional Visitor

Can anyone help with a format for Excel that can do this:  I want to look at a value and if it's greater than say, 1, then I want it to return "1" - BUT, if it's greater than 2, I want it to return "2", greater than 3, then return "3", etc.


Is there a formula configuration that can do that?


4 Replies

@Kandor5 Please explain a bit more. Let's say the number is 3. That's both greater than 1 and 2. What should be the result.

Assuming you have the value in cell A1 and you want to return 0 if it is less than 1 you might use this formula for your specific requirement
You can customize accordingly. Please Note this formula returns #N/A if the value is 1 or less than 1.If you want it to return 0 if the value is 1 or less than 1 you can use formula like..
However, you may be looking for a more generalized or different type of solution.


With a number in A1, how about




or perhaps



@Hans Vogelaar 

I thought about =INT(A1) and =ROUNDUP(A1,0) but then for value 0f 3 it returns 3 instead of 2 (since >2 and !>3) and same for 2.
However, Kandor5 might be looking for this solution...Thanks