Data Buckets (in a range)

[salireza]

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 * 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 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

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)