Search code examples
excelformula

How can I express easily a formula that has a lot of nesting Ifs

I want to express a formula that says if a number in a column is 50 to 99, then return 50. If 100-149, then return 100, 150-199, then return 150, etc, etc. I need a more concise way to do that for numbers that could reach 2000 (in 50 increments).

Right now my formula is =if(and >50 <100),50,if >100,100,true,0) or something like that, I can't see if right now.

Solution

  • There's probably a faster way, but here's what I would do:

    Create a new column that rounds down to the nearest 50: Assume the numbers are in Column A:

    =CONCAT(FLOOR(A2,50),"-",IF(FLOOR(A2,100)-1<FLOOR(A2,50),FLOOR(A2,100)+99,FLOOR(A2,100)-1))

    This will produce, for every row, the nearest 50 and nearest 100-1. Also, it allows you to go to 10,000, 50,000, 100,000 and never have to change this formula.

    The only thing is adding another nested if for any number below 50, but that's up to you. Otherwise, it shows as 0-99 for any number under 50 and 50-99 for any number below 99 but above 50.

    Edit

    I found out, after all that work, that you just wanted it rounded down to the nearest 50. Just use =FLOOR(A2, 50)

    Results