Share via

Same formula for pivot table calculated field gives different output in Excel and Google Sheets

Anonymous
2023-09-27T23:32:43+00:00

Hello! I'm trying to figure out how pivot tables work differently in Excel and Google Sheets.

I inputted the same dataset into both Excel and Sheets, created a pivot table from the data, and created a calculated field with the formula "=AVERAGE('Box Office Revenue ($)') - AVERAGE ('Budget ($)')". However, I ended up getting different values in the two spreadsheet programmes.

Google Sheets returned the value I expected, whereas Excel returned a different value that I cannot figure out.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-09-28T16:02:09+00:00

    Hello sandisamixture

    Thank you for posting in Microsoft Community.

    It seems like you wanted to know the difference between Excel and Google sheets pivot table calculated field. Let’s work together in finding the best resolution to this issue.

    The difference in the results you’re seeing is due to how Excel and Google Sheets handle calculated fields in pivot tables.

    In Excel, when you create a calculated field, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount. This means that Excel is actually calculating =(SUM('Box Office Revenue ($)')/COUNT('Box Office Revenue ($)')) - (SUM('Budget ($)')/COUNT('Budget ($)')), which might not give you the expected result.

    On the other hand, Google Sheets performs the calculation on each row first, and then averages those results. This means that Google Sheets is calculating the average of ('Box Office Revenue ($)' - 'Budget ($)') for each row, which is what you intended.

    To get the same result in Excel as in Google Sheets, you could add a new column to your original data with the formula ='Box Office Revenue ($)' - 'Budget ($)', and then take the average of this new column in your pivot table.

    We look forward to your next response.

    Regards,

    Jason Guer

    Microsoft Community Moderator

    3 people found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest