Solved Custom Rounding Formula Question

February 27, 2012 at 12:14:08
Specs: Windows 7, 2-3.0/4gb
I am need of a function that rounds numbers as shown below. I really need some expert help on this as I am not good at writing functions. I am using excel 2007. Could someone come to my assistance on this please?

0.00 to 1.24 = 1
1.25 to 1.74 = 1.5
1.75 to 2.24 = 2
2.25 to 2.74 = 2.5
2.75 to 3.24 = 3
3.25 to 3.74 = 3.5
3.75 to 4.24 = 4
4.25 to 4.75 = 4.5
4.75 to 5.00 = 5

See More: Custom Rounding Formula Question

Report •


#1
February 27, 2012 at 13:16:14
Read up on the VLOOKUP function in the Excel Help files and/or via Google so that you understand the following suggestion.

Create a table with the low end of your ranges in one column and the desired results in the next:

       A          B
1      0	  1
2      1.25       1.5
3      1.75       2
4      2.25       2.5
5      2.75       3
6      3.25       3.5
7      3.75       4
8      4.25       4.5
9      4.75       5

Then use a VLOOKUP with the range_lookup argument set to 1 (TRUE).

For example, if you want to round the number entered into C1, enter this in D1:

=VLOOKUP(C1, $A$1:$B$9, 2, 1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 27, 2012 at 13:27:39
✔ Best Answer
Here is a Rounding solution:

With your data in cell A1 enter this formula in B1

=IF(A1<0.8,CEILING(A1,1),MROUND(A1,1/2))

See how that works out.

MIKE

http://www.skeptic.com/


Report •

#3
February 28, 2012 at 06:03:45
mmcconaghy,
thank you, this works perfect for what I need.

Report •

Related Solutions


Ask Question