Need formula in excel for odd figures

[kalpeshkansara]

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.

 

[Scott Huish]

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

 

[narendra]

Assuming the value is in A1 and it is a whole number
use the following formula in B1:

=(INT(A1/25)+(MOD(A1,25)>0))*25

 

[Scott Huish]

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:

=(INT(A1/25)+(MOD(A1,25)>0))*25

 

[narendra]

Thanks Scott, that is neat.

 

[narendra]

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.

 

[Rick Rothstein]

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

 

[kalpeshkansara]

Yes its works perfectly. Very Thanks Mr. Narendra

 

[narendra]

Yes its works perfectly. Very Thanks Mr. Narendra

Thanks to Scott, those were his formulas.

and thank you Rick, I agree.