using a "sum of ..." field in a calculated field

[Vegaper]

Hi, let's see if you can help me with this question:
I have a sheet with some fields, one of them is 'nș of ind'. I then create a pivot table and add a data field with 'nș of ind' as an average xlaverage (Function = xlAverage), that field is called "ind avrg", for example. Now I need to add a calculated field who uses that value (the average of nș of ind) in a function, something like "ind avrg" * 3 but of course in the list of fields available for my new calculated field doesn't appear my field "ind avrg", only the data field "nș of ind". I've tried using = average("nș of ind")* 3 in the calculated field but I get wrong data.
Don't know if I'm explaining it well.

I need to use a field of the pivot table (not from the data sheet) as a source for a calculated data. If you can provide a solution... even a vba solution would be welcomed.

Thank you very much.

[dilipandey]

Hi Vegaper,

Welcome to the forum.

Would be better if you could upload a sample workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Vegaper]

Ok, thank you for your answer, although several people have told me that what I want to do is just impossible...
See the example, I have a field that gives me the average number of individuals, I want to add a new calculated field which uses THAT average in a formula so I need to get the value from the "Promedio de nș ind" field and use it in a calculated field.
I'll give you the example, but my calculated field now is quite useless "=1*1" What I need there is is ("Promedio de nș ind" * 10)/área. (since my excel is in Spanish, I really don't know but I'm quite sure what you'll see is "average of nș ind" instead)

prueba.xls

Thanks.

[Vegaper]

BTW I'm now working in a workaround for this problem copying the pivot table and pasting it as values and adding the desired formulae in the new table, and i's working, so I don't need that solution, but I would still want to know if is possible to do that trick within the pivot table.
Thanks a lot.

[dilipandey]

Hi Vegaper,

I have inserted a calculated column based on the logic:- "Promedio de nș ind" * 10)/área

See if this helps you :
prueba.xls


Regards,
DILIPandey

<click on below 'star' if this helps>

[Vegaper]

it works... perfectly, but it's so odd... I mean, you are not really working with average of nș of ind in the calculated field, but with sum of nș of ind instead, It has taken me several minutes to figure out why your formula works, because before posting the question here I had tried what I supposed was the same, I mean: =('nș of ind' * 4) since (10/área =4) and it didn't work. Now I see that what your formula does in fact is 'sum of nș of ind'*10/'sum of área' and it works... now I see that my problem is not knowing how calculated fields really works with the data.
Thank you very much for your solution, it has helped me also to understand a bit more calculated fields.

[dilipandey]

you are welcome...!!

Suggest you to mark this thread as [solved].. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Vegaper]

ops, sorry, I forgot. Done. your suggestions are my commands