Data Buckets (in a range)
 

I currently have data orgnised as such:

CUSTOMER[/b] *YEARS* AMOUNT(USD)
CUSTOMER A 1.2 100
CUSTOMER B 2.5 200
CUSTOMER C 0.66 300
CUSTOMER D 6.2 100
CUSTOMER E 3.4 150
CUSTOMER F 1.7 50
CUSTOMER G 5.0 200
CUSTOMER H 4.2 25

I HAVE A BIG SPREADSHEET OF AROUND 6000 ROWS AND WOULD LIKE TO
REARRANGE THE DATA INTO A RANGE BUCKET BY YEARS (USING PIVOT TABLES),
I.E.

*YEAR BUCKET * [b]AMOUNT(USD)
0-3 650
3.1-5 375
5.1-10 100


Many thanks in advance for your help

Attached is the excel version of the example above


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5187 |
+-------------------------------------------------------------------+

--
salireza
------------------------------------------------------------------------
salireza's Profile: http://www.excelforum.com/member.php...o&userid=37465
View this thread: http://www.excelforum.com/showthread...hreadid=571256

Data Buckets (in a range)
 

Add a column to calculate the bucket

=LOOKUP(B2,{0,3.1,5.1},{"0-3","3.1-5","5.1-10"})

copy this down and include in the pivot.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"salireza" wrote in
message ...

I currently have data orgnised as such:

CUSTOMER[/b] *YEARS* AMOUNT(USD)
CUSTOMER A 1.2 100
CUSTOMER B 2.5 200
CUSTOMER C 0.66 300
CUSTOMER D 6.2 100
CUSTOMER E 3.4 150
CUSTOMER F 1.7 50
CUSTOMER G 5.0 200
CUSTOMER H 4.2 25

I HAVE A BIG SPREADSHEET OF AROUND 6000 ROWS AND WOULD LIKE TO
REARRANGE THE DATA INTO A RANGE BUCKET BY YEARS (USING PIVOT TABLES),
I.E.

*YEAR BUCKET * [b]AMOUNT(USD)
0-3 650
3.1-5 375
5.1-10 100


Many thanks in advance for your help

Attached is the excel version of the example above


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5187 |
+-------------------------------------------------------------------+

--
salireza
------------------------------------------------------------------------
salireza's Profile:
http://www.excelforum.com/member.php...o&userid=37465
View this thread: http://www.excelforum.com/showthread...hreadid=571256