1

I have a spreadsheet with a very simple formula: =C4/D4 Sometimes, this very simple formula gives the wrong value. Same Formula, Two Answers

  • Cell C4 contains the value 390062 formatted as a number with thousands separator

  • Cell D4 contains a formula which evaluates to the value 444434 formatted as a number with thousands separator.

    • This is a =VLOOKUP() formula and the displayed value is correct.
  • Cell G4 contain the formula =C4/D4 which is displayed as 28%

    • This is INCORRECT! The correct value is 88%
  • If I add the same formula in Cell J4 I get the correct value.

  • Result for =VALUE(G4) is 0.28

  • Result for =VALUE(J4) is 0.88

Furthermore;

  • If I change the formatting on cell G4, it updates to the correct value.

EDIT: I might have figured this out; cell C4 is part of a Pivot Table. Is that why it's not calculating correctly? If so, does anyone have a solve for this?

3
  • Welcome to Web Applications Stack Exchange. One thing to check is that File > Settings > Calculation > Iterative calculation is off. Apr 4, 2023 at 20:10
  • Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Apr 5, 2023 at 8:13
  • Thanks doubleunary! I tried both with Iterative Calculation enabled and disabled - both end with same result. Apr 5, 2023 at 12:36

1 Answer 1

1

The structure of a pivot table may change from time to time depending on things like sort order and labels, causing cell locations to vary.

To get a particular pivot table aggregate value regardless of which cell it happens to be, use the getpivotdata() function.

To get a particular non-aggregate value, query the data table rather than the pivot table, using query(), filter() or vlookup().

1
  • Thanks doubleunary! GetPivotData seems to be what I need! Apr 5, 2023 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.