Pivot Table formula giving incorrect results

some_evil

New member
Joined
Feb 10, 2014
Messages
10
Reaction score
0
Points
1
Excel Version(s)
2019
Hi Guys, attached is a xlsx file with pipes in our government area, and I am trying to add a field that multiplies its unit rate by the length, which in turn gives us a total value for each pipe type. But when I manually calculate the values I am getting different results.

View attachment Current Sewer Main Types.xlsx

see formula im using:
Microsoft Excel - Current Sewer Main Types.xlsx_2014-02-20_14-46-29.jpg


see results i get vs excel calculates:
example errors.jpg


Is there a better way for me to be calculating these total values so they are correct?

Thanks heaps.
 
Try instead to add a new column to the main table called Total_Value with formula:

=[@Length]*[@UnitRate]

Then Add that column instead to the Pivot Table.
 
Try instead to add a new column to the main table called Total_Value with formula:

=[@Length]*[@UnitRate]

Then Add that column instead to the Pivot Table.


Hi NBVC,

That is working great as a temporary measure, but this original table that the pivot is querying is a live feed from an SQL Database... and something I read yesterday made complete sense (i did not even think of doing this) but I added a new SELECT line... see below. This way every time new data is read into the table it is always current and up to date!

Code:
SELECT 
   tblAsset.AssetID AS [AssetID],
   tblAsset.AssetDescription AS [AssetDesc],
   tblasset.dimension1 AS [Length],
   tblTypes.TypeID AS [TypeID],
   tblTypes.TypeCode AS [TypeCode],
   tblTypes.TypeDescription AS [AssetType],
   tblTypes.RepCost AS [UnitRate],
[COLOR=#ff0000][FONT=arial black]   tblAsset.dimension1 * tblTypes.RepCost AS [Total_Price]           [FONT=arial]<---- this was the winning line that helped me out![/FONT][/FONT][/COLOR]
FROM
   tblAsset Left JOIN 
   tblTypes ON tblAsset.TypeID = tblTypes.TypeID
WHERE
 tblTypes.TypeCode Like '05.05%' And
 tblAsset.ValuationAsset = 1
Thanks heaps for your help.
 
Back
Top