Pivot Tables

Isabella · Jan 8, 2011

Hi,

Could someone please show me how i can perform a pivot table that will give me the same result as show in Col J-P. I want to avoid using formula if possible.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

4

2-29

30-59

5

Type

Amount

CCY

Age

Source

CCY

Rate

Source

No. of items

Value (AUD)

ABS (AUD)

No. of items

Value (AUD)

ABS (AUD)

6

SCR

8268.57

AUD

15

Team1

AUD

1

Team1

3

29,268.57

29268.57

7

SCR

20000

AUD

20

Team1

EUR

0.75581

Team2

1

6,844.94

6844.94067

8

SCR

1000

AUD

5

Team1

GBP

0.645411

Team3

0

0.00

0

9

SCR

4417.8

GBP

7

Team2

JPY

82.56223

Team4

17

29,314.60

1803231.35

10

SCR

2208.9

GBP

30

Team2

USD

1.00655

11

SCR

100000

AUD

40

Team3

12

LCR

1163.22

EUR

12

Team4

13

LDR

-3320.14

EUR

39

Team4

14

LCR

9646.51

EUR

15

Team4

15

LDR

-12618.8

EUR

40

Team4

16

LCR

476017.8

EUR

15

Team4

17

LDR

-1688137

JPY

38

Team4

18

LCR

7187517

JPY

4

Team4

19

LDR

-6303971

JPY

15

Team4

20

LCR

108431.3

USD

15

Team4

21

LCR

15652.36

USD

15

Team4

22

LDR

-280467

USD

17

Team4

23

LCR

52025.14

USD

17

Team4

24

LCR

7589.03

USD

17

Team4

25

LCR

1965.98

EUR

3

Team4

26

LDR

-98555.5

EUR

17

Team4

27

LDR

-33658.3

EUR

17

Team4

28

LDR

-19670

EUR

10

Team4

29

LDR

-247310

EUR

17

Team4

30

LDR

-2068.46

EUR

13

Team4

31

LDR

-800

EUR

28

Team4

Sheet1

ziad alsayed · Jan 8, 2011

which version of excel are you using??

if the below is ok by you , inform please to tell you how to do it

<TABLE style="WIDTH: 414pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=550><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" span=4 width=106><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 width=79></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 35pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=47></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 80pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=106>Age</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 80pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=106>Values</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 80pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=106></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 80pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=106></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63><29</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63>29-59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Source</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">CCY</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Count of Source</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Sum of Amount</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Count of Source</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Sum of Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Team1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>29268.57</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Team2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">GBP</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4417.8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2208.9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Team3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>100000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Team4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">EUR</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>86731.25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-15938.94</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">JPY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>883546</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-1688137</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-96769.17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Grand Total</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>907194.45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>-1601867.04</TD></TR></TBODY></TABLE>

Isabella · Jan 8, 2011

I am building in 2010 to use in 2003.

Ok, i dont need to break the data down in CCY i need to convert the amounts into AUD which is col L in my example and then convert to ABS in Col M. See my example in #1

ziad alsayed said:
which version of excel are you using??

if the below is ok by you , inform please to tell you how to do it

<table style="width: 414pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="550"><colgroup><col style="width: 59pt;" width="79"><col style="width: 35pt;" width="47"><col style="width: 80pt;" span="4" width="106"></colgroup><tbody><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); width: 59pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20" width="79">
</td><td style="border: medium none rgb(240, 240, 240); width: 35pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="47">
</td><td style="border: medium none rgb(240, 240, 240); width: 80pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="106">Age</td><td style="border: medium none rgb(240, 240, 240); width: 80pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="106">Values</td><td style="border: medium none rgb(240, 240, 240); width: 80pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="106">
</td><td style="border: medium none rgb(240, 240, 240); width: 80pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="106">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" class="xl63"><29</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" class="xl63">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" class="xl63">29-59</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" class="xl63">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Source</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">CCY</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">Count of Source</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">Sum of Amount</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">Count of Source</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">Sum of Amount</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Team1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">AUD</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">3</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">29268.57</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Team2</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">GBP</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">4417.8</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">2208.9</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Team3</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">AUD</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">100000</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Team4</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">EUR</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">10</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">86731.25</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">2</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-15938.94</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">JPY</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">2</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">883546</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-1688137</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); background-color: transparent; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">USD</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">5</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-96769.17</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Grand Total</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">21</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">907194.45</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">5</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-1601867.04</td></tr></tbody></table>

ziad alsayed · Jan 8, 2011

pleae find the below table
<TABLE style="WIDTH: 537pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=716><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=9 width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 78pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 width=104>Sum of Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68>Age</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68>CCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 51pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"><30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">30-59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Source</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">AUD</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">EUR</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">GBP</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">JPY</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">USD</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">AUD</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">EUR</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">GBP</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">JPY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Team1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>29268.57</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Team2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4417.8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2208.9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Team3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>100000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Team4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>86731.25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>883546</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-96769.17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-15938.94</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>-1688137</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Grand Total</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>29268.57</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>86731.25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>4417.8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>883546</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>-96769.17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>100000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>-15938.94</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2208.9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>-1688137</TD></TR></TBODY></TABLE>

select anywhere within you table, go to insert tab, pivot table, drag the source to row labels, age to columns labels adn CCY also to columns labels,drag the amount to sum values.
now select the age in the pivot table, go to design choose group selection. in the starting date put 30, in the ending date put 59, by out 29.

as for the number of items you can also drag the source, but the pivot will be somehow big.

hopw this can help.

Isabella · Jan 8, 2011

Could you please provide an example similar to my #1, which converts the value to AUD

ziad alsayed said:
pleae find the below table
<table style="width: 537pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="716"><colgroup><col style="width: 78pt;" width="104"><col style="width: 51pt;" span="9" width="68"></colgroup><tbody><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); width: 78pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20" width="104">Sum of Amount</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">Age</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">CCY</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;"><30</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">30-59</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Source</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">AUD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">EUR</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">GBP</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">JPY</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">USD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">AUD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">EUR</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">GBP</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">JPY</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">29268.57</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team2</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">4417.8</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">2208.9</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team3</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">100000</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team4</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">86731.25</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">883546</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-96769.17</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-15938.94</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-1688137</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Grand Total</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">29268.57</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">86731.25</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">4417.8</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">883546</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-96769.17</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">100000</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-15938.94</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">2208.9</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-1688137</td></tr></tbody></table>

select anywhere within you table, go to insert tab, pivot table, drag the source to row labels, age to columns labels adn CCY also to columns labels,drag the amount to sum values.
now select the age in the pivot table, go to design choose group selection. in the starting date put 30, in the ending date put 59, by out 29.

as for the number of items you can also drag the source, but the pivot will be somehow big.

hopw this can help.

Isabella · Jan 8, 2011

Please note every currency must be converted to AUD, if you look ta the Sumproduct formulas in my example this does that. How would you do this in a pivot table.

ziad alsayed said:
pleae find the below table
<table style="width: 537pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="716"><colgroup><col style="width: 78pt;" width="104"><col style="width: 51pt;" span="9" width="68"></colgroup><tbody><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); width: 78pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20" width="104">Sum of Amount</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">Age</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">CCY</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td><td style="border: medium none rgb(240, 240, 240); width: 51pt; font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" width="68">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;"><30</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">30-59</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td><td style="border: medium none rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Source</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">AUD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">EUR</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">GBP</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">JPY</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">USD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">AUD</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">EUR</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">GBP</td><td style="border-width: medium medium 0.5pt; border-style: none none solid; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(149, 179, 215); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;">JPY</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team1</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">29268.57</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team2</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">4417.8</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">2208.9</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team3</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">100000</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td></tr><tr style="height: 15pt;" height="20"><td style="border: medium none rgb(240, 240, 240); background-color: transparent; height: 15pt;" height="20">Team4</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">86731.25</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">883546</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-96769.17</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-15938.94</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;">
</td><td style="border: medium none rgb(240, 240, 240); background-color: transparent;" align="right">-1688137</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); height: 15pt; color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" height="20">Grand Total</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">29268.57</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">86731.25</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">4417.8</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">883546</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-96769.17</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">100000</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-15938.94</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">2208.9</td><td style="border-width: 0.5pt medium medium; border-style: solid none none; border-color: rgb(149, 179, 215) rgb(240, 240, 240) rgb(240, 240, 240); font-family: Calibri; background: none repeat scroll 0% 0% rgb(220, 230, 241); color: black; font-size: 11pt; font-weight: 700; text-decoration: none;" align="right">-1688137</td></tr></tbody></table>

select anywhere within you table, go to insert tab, pivot table, drag the source to row labels, age to columns labels adn CCY also to columns labels,drag the amount to sum values.
now select the age in the pivot table, go to design choose group selection. in the starting date put 30, in the ending date put 59, by out 29.

as for the number of items you can also drag the source, but the pivot will be somehow big.

hopw this can help.

Isabella · Jan 8, 2011

anyone?

Isabella · Jan 9, 2011

*bump*

ManDrone · Jan 9, 2011

Pivot tables are for summarizing (by collapsing into categories) data that is in its final form. You should (if you can) add a "convert to AUD" column in your original data. I'm not sure that you can add that sort of logic within a pivot table.

Isabella · Jan 9, 2011

ManDrone said:
Pivot tables are for summarizing (by collapsing into categories) data that is in its final form. You should (if you can) add a "convert to AUD" column in your original data. I'm not sure that you can add that sort of logic within a pivot table.

People always tell me to use pivot table to avoid formulas and additional work, now that i use it it does not work. I should just stick to formulas.

Pivot Tables

Isabella

Well-known Member

Excel Facts

ziad alsayed

Well-known Member

Isabella

Well-known Member

ziad alsayed

Well-known Member

Isabella

Well-known Member

Isabella

Well-known Member

Isabella

Well-known Member

Isabella

Well-known Member

ManDrone

New Member

Isabella

Well-known Member

Similar threads

Forum statistics

Share this page

Pivot Tables

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock