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

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.

CodePudding user response：

Divide the number by 50, then multiply the integer of that by 50:

=INT(A1/50)*50

Or subtract half the number and use MROUND:

=MROUND(A1-25,50)

CodePudding user response：

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)

This took me some time, please make sure to upvote and mark as answer, as I'm sure this would benefit both of us.