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

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 5Then use a VLOOKUP with the

range_lookupargument 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.

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

mmcconaghy,

thank you, this works perfect for what I need.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History