CODEWITHSUNDEEP
excelformula
mhn
mhn

Reputation: 2750

Output range of cell value in excel

I need to compute the range of a given cell in excel. For example, if the cell is like below,

Val
---
25
23
18
52
66

I need a formula to just find which range it belongs to. the ranges can be in intervals of 5 .. like 0-5,5-10,10-15 and so on. So desired output is 

Val    Range
-------------
25     21-25
23     21-25
18     16-20 
52     51-55
66     66-70

Upvotes: 0

Views: 2576

Answers (1)

Will BeDeleted
Will BeDeleted

Reputation: 87

Assuming A1 holds the value 25, then put this formula in A2:

=TEXT(CEILING(A1/5,1)*5-4,"0")&" - "&TEXT(CEILING(A1/5,1)*5,"0")

Copy it down to other rows as required

Upvotes: 1

Related Questions