Hi,

I have a datasheet and every column is given grade as per their headings in
columns and these grades have a particular value e.g.

A = 50
B = 60
C = 70
D = 80

I want to calculate the average or sum of the grades as per their values.

One way is to use the 'Vlookup' function but I'm looking for any other way
as we have an option for 'Calculated Field' in Pivot Table. Is there any way
to use that calculated field to calculate the values with which I need not to
put Vlookup function for each and every column ?

Or any other way to calculate these values ?

Any help will be appreciated ?

Regards,
Miki

Miki

if you are already using a pivot table you can have the average calculated
by the pivot table itself - in the same place where you found 'calculated
field' you can change the function to 'average' instead of Sum or Count

RegMigrant

"Miki" wrote:


Hi,

I have a datasheet and every column is given grade as per their headings in
columns and these grades have a particular value e.g.

A = 50
B = 60
C = 70
D = 80

I want to calculate the average or sum of the grades as per their values.

One way is to use the 'Vlookup' function but I'm looking for any other way
as we have an option for 'Calculated Field' in Pivot Table. Is there any way
to use that calculated field to calculate the values with which I need not to
put Vlookup function for each and every column ?

Or any other way to calculate these values ?

Any help will be appreciated ?

Regards,
Miki

just as FYI,

Have you tried using the getpivotdata() function in combination with a vlookup?

The excel help explains how to use the getpivotdata() to retrieve info from a pivot table.

all the best