CODEWITHSUNDEEP
excelroundingformula
Ktown
Ktown

Reputation: 1

Excel rounding down to a specific number

I need a help with Excel. Can't figure out the formula for a number to round down to 10010. There is a column which calculates numbers and I want when the result is over 10010, to round it down and display it. Else to show 0.

Thanks!

Upvotes: 0

Views: 441

Answers (1)

Darren Wooder
Darren Wooder

Reputation: 51

if you would like to change the number (cell A1) to 10010 if it is greater than or equal to 10010, and if it is blank or less than 10010 set it to 0 this formula would work for you.

=IF(A1>=10010,10010,0)

In response to your comment if in cell B1 you put the formula:

 =MIN(IF(A1:A20>10010,A1:A20))

...and make sure you press CTRL-SHIFT-ENTER instead of just ENTER when coming out of formula. This will turn it into an array formula and will return you next largest value to 10010 from you range A1:A20.

In cell C1 can then just put:

 =B1-10010

Hope this helps.

Upvotes: 2

Related Questions