pivot table etc.

[RET79]

Hi.

I have a spreadsheet, with many columns. For instance here are two typical columns

FundType Code YV FundValue
A 2 3 24.3
A 3 4 43.2
A 3 -6 56.7
A 3 -2 23.53
B 1 2 28.90
B 1 5 54.3
B 1 -5 -56.3
B 2 2 -100.5


Now, just as a bit of background, I put these into a pivot table to get summaries for such data under different classes.
For example, my pivot table would calculate the sum of FundValue for FundType B and Code 1 (in the example above it would be 26.90, and so on).However, I need to find the sum of FundValue for different subsets of YV. For example, I need the pivot table to give me the FundValue for FundType B, Code 1 for -2
At present, I have to insert a column in my initial spreadsheet, and put an IF function such as IF(YV<3, IF(YV>-2, FundValue,""),"") and then import this column into the pivot table. Although this works fine, I would rather not have to insert any extra columns into my initial spreadsheet, I would prefer the pivot table to be doing all the calculations as I have many of these conditional columns I need to sum.

I'm sure there is a way, I would be grateful for any advice. Thanks.



[ This Message was edited by: RET79 on 2002-03-29 16:17 ]

[RET79]

My message again, did not let me edit it for some reason:

Hi.

I have a spreadsheet, with many columns. For instance here are some typical columns

FundType Code YV FundValue
A 2 3 24.3
A 3 4 43.2
A 3 -6 56.7
A 3 -2 23.53
B 1 2 28.90
B 1 5 54.3
B 1 -5 -56.3
B 2 2 -100.5


Now, just as a bit of background, I put these into a pivot table to get summaries for such data under different classes.
For example, my pivot table would calculate the sum of FundValue for FundType B and Code 1 (in the example above it would be 26.90, and so on).However, I need to find the sum of FundValue for different subsets of YV. For example, I need the pivot table to give me the FundValue for FundType B, Code 1 for 3 > YV > -2. In the example above this would give me a total of 28.90 .

At present, I have to insert a column in my initial spreadsheet, and put an IF function such as IF(YV<3, IF(YV>-2, FundValue,""),"") and then import this column into the pivot table. Although this works fine, I would rather not have to insert any extra columns into my initial spreadsheet, I would prefer the pivot table to be doing all the calculations as I have many of these conditional columns I need to sum.

I'm sure there is a way, I would be grateful for any advice. Thanks.

[RET79]

Hi, did anyone have any ideas about this particular query?

(Just askin!)

RET79

[Aladin Akyurek]

I have a spreadsheet, with many columns. For instance here are some typical columns

FundType Code YV FundValue
A 2 3 24.3
A 3 4 43.2
A 3 -6 56.7
A 3 -2 23.53
B 1 2 28.90
B 1 5 54.3
B 1 -5 -56.3
B 2 2 -100.5


Now, just as a bit of background, I put these into a pivot table to get summaries for such data under different classes.
For example, my pivot table would calculate the sum of FundValue for FundType B and Code 1 (in the example above it would be 26.90, and so on).However, I need to find the sum of FundValue for different subsets of YV. For example, I need the pivot table to give me the FundValue for FundType B, Code 1 for 3 > YV > -2. In the example above this would give me a total of 28.90 .

At present, I have to insert a column in my initial spreadsheet, and put an IF function such as IF(YV<3, IF(YV>-2, FundValue,""),"") and then import this column into the pivot table. Although this works fine, I would rather not have to insert any extra columns into my initial spreadsheet, I would prefer the pivot table to be doing all the calculations as I have many of these conditional columns I need to sum.

I'm sure there is a way, I would be grateful for any advice.

Although I think you'd be better off sticking with the Pivot Tables, what follows might be of use either in conjunction with Pivot Tables or stand alone.

I'll assume that A1:D9 houses the sample data you provided. Everything below take place in the same sheet, while it can be very easily in a separate worksheet.

In F3 enter:

=MATCH(9.99999999999999E+307,D:D)-1

Enter the unique codes that you have in F from F4 on. It would look like this for the sample data:

{"Code";
1;
2;
3}

This unique list can be created by using e.g., Adv Filter.

Enter the unique fund types that you have in row 4 from G4 on. It would look like this for the sample data:

{"A","B"}

In G5 enter:

=SUMPRODUCT((OFFSET($B$2,0,0,$F$3,1)=$F5)*(OFFSET($A$2,0,0,$F$3,1)=G$4),OFFSET($D$2,0,0,$F$3,1))

Copy this across to H5 and copy down.

The foregoing will give you the total of fund values for FundType by Code.

In I1:I4 enter:

{"YV";
-2;
3;
"B"}

which are params of a specific query that you mention.

In I5 enter:

=SUMPRODUCT((OFFSET($B$2,0,0,$F$3,1)=$F5)*(OFFSET($A$2,0,0,$F$3,1)=I$4)*(OFFSET($C$2,0,0,$F$3,1)>$I$2)*(OFFSET($C$2,0,0,$F$3,1)<$I$3),OFFSET($D$2,0,0,$F$3,1))

The processing area will look like this:

{"","","","YV";
"","","",-2;
8,"","",3;
"Code","A","B","B";
1,0,26.9,28.9;
2,24.3,-100.5,"";
3,123.43,0,""}

Aladin

[RET79]

Thanks Aladdin for your suggestion, it is certainly an interesting alternative especially so that I coudl leave the original data intact one on sheet with all the calcs taking place on another sheet.
I like it, thanks.

RET79