Forum Discussion

Kandor5's avatar
Kandor5
Copper Contributor
May 14, 2023

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?

 

Excel
    • sanjibdutta's avatar
      sanjibdutta
      Brass Contributor
      May 14, 2023

      HansVogelaar 

      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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 14, 2023

    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.

  • sanjibdutta's avatar
    sanjibdutta
    Brass Contributor
    May 14, 2023
    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.

Share

Resources