Forum Discussion
Kandor5
May 14, 2023Copper 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?
- sanjibduttaBrass Contributor
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
- sanjibduttaBrass ContributorAssuming 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. - Riny_van_EekelenPlatinum Contributor
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.