CODEWITHSUNDEEP
excel
Moti laluom
Moti laluom

Reputation: 31

Rounding to a set of values in Excel

I have a set of data which I want to round to a given set of values:

Example: the set values is : 10000,5000,2500,2000,1000,500,250,200,100,50,25,20,10,5,2,1

I want to round to the nearest value (and not up/down round). In case that the value is in the middle - to one of the options (don't care which).

26 -> 25 3.5 -> 2 or 5

Thank you

Upvotes: 0

Views: 150

Answers (3)

pnuts
pnuts

Reputation: 59495

UNTESTED

I doubt an array formula is required, though assuming series is in A1 to A16 (sorted descending) following will return #N/A for numbers over 1000 and 0 for those less than 1 in C1:

 =IF(INDEX(A:A,MATCH(C1,A:A,-1))-C1<C1-INDEX(A:A,MATCH(C1,A:A,-1)+1),INDEX(A:A,MATCH(C1,A:A,-1)),INDEX(A:A,MATCH(C1,A:A,-1)+1))

Upvotes: 0

A.S.H
A.S.H

Reputation: 29352

Array in A1:A16 and you want to match the nearest for C1, you can use this Array formula

D1:
=INDEX($A$1:$A$16,MATCH(MIN(ABS($A$1:$A$16-C1)),ABS($A$1:$A$16-C1),0))

Ctrl+Shift+Enter

Copy and paste down along column D to match values in column C.

enter image description here

Upvotes: 2

am2
am2

Reputation: 371

You could do this by vlookup

Build a table

       |      A |      B
-------+--------+--------
     1 |      0 |      1
     2 |      1 |      2
     3 |      2 |      5
     4 |      5 |     10
     5 |     10 |     20
     6 |     20 |     25
     7 |     25 |     50
     8 |     50 |    100
     9 |    100 |    200
    10 |    200 |    250
    11 |    250 |    500
    12 |    500 |   1000
    13 |   1000 |   2000
    14 |   2000 |   2500
    15 |   2500 |   5000
    16 |   5000 |  10000

A20: 320
B20: =VLOOKUP(320,$A$1:$B$16,1,TRUE) --> 250
C20: =VLOOKUP(320,$A$1:$B$16,2,TRUE) --> 500
D20: =(B20+C20)/2 --> 375
E20: =IF(A20 < D20;B20;C20)

in E20 you will find your result

Upvotes: 0

Related Questions