# Need formula in excel for odd figures

#### kalpeshkansara

##### New Member
If I need any rupees amount of figures in fixed figure like (25 - 50 - 75 - 100)only for every any amount.
(Here we take 25,50,75,100 as criteria)
How can we do in excel with using some formulas?
i.e. If I have figures like odd in amount as below than?
Odd - even
101 - 125 - If I get odd figures 101 than formula will set that amount to 125 because odd figure is below than 25 criteria and more than 100+1. so
126 - 150 - If I get odd figures 126 than formula will set that amount to 150 because odd figure is more than 25 criteria and more than 125+1. so
149 - 150 - If I get odd figures 149 than formula will set that amount to 150 because odd figure below than 50 criteria and more than 125. so
151 - 175 - If I get odd figures 151 than formula will set that amount to 175 because odd figure below than 75 criteria and more than 150. so
169 - 175 - If I get odd figures 169 than formula will set that amount to 175 because odd figure below than 75 criteria and more than 150. so
176 - 200 - If I get odd figures 176 than formula will set that amount to 200 because odd figure below than 200 criteria and more than 175+1. so
181 - 200 - If I get odd figures 181 than formula will set that amount to 200 because odd figure below than 200 criteria and more than 175. so
199 - 200 - If I get odd figures 199 than formula will set that amount to 200 because odd figure below than 200 criteria and more than 175. so
219 - 225 - If I get odd figures 219 than formula will set that amount to 225 because odd figure below than 25 criteria and more than 200. so
342 - 350 - If I get odd figures 342 than formula will set that amount to 350 because odd figure below than 50 criteria and more than 325. so
356 - 375 - If I get odd figures 356 than formula will set that amount to 375 because odd figure below than 75 criteria and more than 350. so
881 - 900 - If I get odd figures 881 than formula will set that amount to 900 because odd figure below than 900 criteria and more than 875. So
Like this all figures we have to set odd figures like magnetic to 25,50,75,100 respectively as we get amounts.
This we need to set all in 5 digits figures too.
i.e.
If we get odd figure 34876 then its formula will set to 34900.
If we get odd figure 11738 then its formula will set to 11750
I need help for this.

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Scott Huish

##### MrExcel MVP
Are you just trying to round up to multiples of 25? If so, try this:
=ROUNDUP(A1/25,0)*25

Last edited:

#### narendra

##### Board Regular
Assuming the value is in A1 and it is a whole number
use the following formula in B1:
Code:
``=(INT(A1/25)+(MOD(A1,25)>0))*25``

Last edited:

#### Scott Huish

##### MrExcel MVP
If you're going to use INT, this is shorter:

=-INT(-A1/25)*25

Assuming the value is in A1 and it is a whole number
use the following formula in B1:
Code:
``=(INT(A1/25)+(MOD(A1,25)>0))*25``

#### narendra

##### Board Regular
Thanks Scott, that is neat.

Last edited:

#### narendra

##### Board Regular
Hi Scott,

I just noticed that both version of your formulas will give 2 different options when used with -ve numbers.
The roundup formula will increase the number to next multiple of 25 irrespective of the + or - sign (so you get -150 as result for -126)
The INT formula will actually roundup to higher multiple of 25 (here you get -125 as the result for -126).

Users can choose which formula to use according to their need.

Last edited:

#### Rick Rothstein

##### MrExcel MVP
I just noticed that both version of your formulas will give 2 different options when used with -ve numbers.
The roundup formula will increase the number to next multiple of 25 irrespective of the + or - sign (so you get -150 as result for -126)
The INT formula will actually roundup to higher multiple of 25 (here you get -125 as the result for -126).
This version of Scott's INT function formula fixes the problem so that it and ROUNDUP produce the same results for both positive and negative numbers...

=SIGN(0.0000000005-A1)*INT(SIGN(0.0000000005-A1)*A1/25)*25

Personally, I would go with Scott's ROUNDUP function myself.

Edit Note: The above formula will produce an incorrect value for 0.0000000005 in the cell

Last edited:

#### kalpeshkansara

##### New Member
Yes its works perfectly. Very Thanks Mr. Narendra

#### narendra

##### Board Regular
Yes its works perfectly. Very Thanks Mr. Narendra
Thanks to Scott, those were his formulas.

and thank you Rick, I agree.

Replies
1
Views
358
Replies
6
Views
278
Replies
1
Views
211
Replies
14
Views
655
Replies
3
Views
165

1,195,674
Messages
6,011,096
Members
441,582
Latest member
Topkapi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back