I wanted to create a bin range for data in excel

[Kumar Shanmugam]

I have data in cell value A2:A57838 in Sheet1. I wanted to create a bin range in a group of 20. Please refer image screenshot. Though I am able to group by using pivot table, but I want to assign a cell value A2 to a bin range.

For eg Cell value A2 is 26708325. it falls in the range of 26708321-26708340 (Desired result).

 

[John Jairo V]

Hi @Kumar Shanmugam

You could use this formula (Based on your Binrange):
=LOOKUP(A2,--LEFT(Binrange!$A$2:$A$1789,8),Binrange!$A$2:$A$1789)

Or instead, you can use this formula (independent):
=1+MROUND(A2,20)&"-"&MROUND(A2+20,20)

Blessings!

 

[Kumar Shanmugam]

Thank you very much. Very much surprised - your formula worked. I didn't expect that I would get such a fast reply for this question. Chandoo forum really rocks.

 

[ajaypal.sp]

Hi @Kumar Shanmugam

You could use this formula (Based on your Binrange):
=LOOKUP(A2,--LEFT(Binrange!$A$2:$A$1789,8),Binrange!$A$2:$A$1789)

Or instead, you can use this formula (independent):
=1+MROUND(A2,20)&"-"&MROUND(A2+20,20)

Blessings!

I'M SORRY TO SAY, THE MROUND FORMULA IS NOT WORKING.....PLS SUGGEST MODIFIED FORMULA

 

[Kumar Shanmugam]

Hi, its working.

 

[Kumar Shanmugam]

Sorry second formula is not working.

 

[John Jairo V]

Hi!

Try this:
=1+MROUND(A2-11,20)&"-"&MROUND(A2+9,20)

Blessings!