Roundup/Rounddown/Mround/IF statement?

W

wayne665

I am trying to round a number to the nearest 500. I want it to round down
until the number entered reaches a point of greater than 500, then round up
to the nearest 500
Example: enter 4499 in the cell=4000 (I have this working fine)
However, when I enter 4501, It rounds to 4500. I would like for it to round
to 5000 because the number exceeds 500 in the hundredths. If the spreadsheet
needs to be sent to you guys, just let me know. Thank you in advance for
your help
 
D

duane

this works for numbers >1000 (value in cell a6)

=IF(VALUE(RIGHT(A6,LEN(A6)-1))<500,0,1000)+ROUNDDOWN(A6/(10^(LEN(A6)-1)),0)*(10^(LEN(A6)-1)
 
F

Frank Kabel

Hi
sorry make this
=ROUND(A1,3)
if I understood you correctly. You don't round to the nearest 500 but
to the nearest 1000 according to your example
 
W

wayne665

Ok,
After reading the responses, I don't think I explained it correctly. I will
try again. If I enter a number into a cell ex. 2333, I need two other cells
to populate 2000 & 2500 because this is the range in which 2333 falls. If I
enter 2633 then the cells need to populate 2500 & 3000. I have a chart of
numbers to which I am referencing, and the values are in increments of 500.
So, once the range (ex. 2000-2500) is determined the reference point on the
chart is populated on my spreadsheet. If the need arises, I can e-mail
someone the spreadsheet and chart I am working on.
 
A

Anastasia

Can you please be more clear as to how to enter this formula in the cell; I
tried using it and it did not work for me (spacing of parenthesis, commas,
etc.).
Thank you.
 
Top