budget spreadsheet with min and max and roundup functions

[twinchcombe]

Hi There,
I'm working on my budget spreadsheet, i have a column of numbers generated by our budget formula. I want to add a formula in the next column that will adjust those number to a maximum of $35,000 a minimum of $10,000 and round up everything else to the nearest $100. It is possible to do this in 1 cell?

Thanks for your help!
twinchcombe

 

[AlanY]

try

=ROUND(IF(A1>=35000,35000,IF(A1>10000,A1,10000)),-2)

 

[Joe4]

Alan,
I think you need to use ROUNDUP, not ROUND, as they said:

round up everything else to the nearest $100.

Here is another solution without any IF statements:

=MIN(MAX(ROUNDUP(A1,-2),10000),35000)

And Welcome to the Board, twinchcombe!

 

[twinchcombe]

It worked! Thanks so much.

try

=ROUND(IF(A1>=35000,35000,IF(A1>10000,A1,10000)),-2)

 

[AlanY]

Joe's solution is more compact

 

[Joe4]

It worked! Thanks so much.

If you really want it to "round up" as opposed to just "rounding", you will need to amend the solution slightly (see my previous post).