# Excel Formula ?

Copper Contributor

# Excel Formula ?

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

# Re: Excel Formula ?

@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.

# Re: Excel Formula ?

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
=IFS(A1>3,3,A1>2,2,A1>1,1)
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..
=IF(A1>3,3,IF(A1>2,2,IF(A1>1,1,0)))
However, you may be looking for a more generalized or different type of solution.

# Re: Excel Formula ?

With a number in A1, how about

=INT(A1)

or perhaps

=ROUNDUP(A1,0)-1

# Re: Excel Formula ?

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