CODEWITHSUNDEEP
google-sheetserror-handlingpivot-tableformula
PiKs
PiKs

Reputation: 11

#DIV error in Google Sheet related to Pivot table

I am trying to fix an error in googlesheet especially related to Pivot table. Especially when I make extra calculations from the Pivot table I get the #Div error when I filter on basis of few parameters. But if I make it without any filters I do get proper results.

I am looking for a solution where the calculations can take particular cell values with respect to cell column name only. So I can make a better calculations without any type of filters or shiftings. This is a dynamic case.

Can anyone suggest any better answer?

https://docs.google.com/spreadsheets/d/1h1TdFLj3JyK6-oxOOswuWDx4YixzG51H2rWgK0BbD2s/edit#gid=925622507

Upvotes: 1

Views: 3157

Answers (1)

player0
player0

Reputation: 1

#DIV error is the result of trying to divide an empty cell with another empty cell

enter image description here

It's a standard issue equivalent to dividing 0 with 0. such math is not logical and google sheets will flag it as an error to alert you.

however, the true culprit is that #REF error:

enter image description here

Filters of pivot table can't produce/create additional columns. pivot table will just roll out data wherever it needs. in your scenario pivot table attempts to roll out date into 3 columns but only two columns there are empty. Third column (C) is filled with data/formulae and pivot table cannot move data or delete data so if there is no space to roll out it will #REF error to alert you that something went wrong.

Possible solution: move C, D columns to the right. If you are not able to predict how many columns will filter of pivot data roll out into then have some extra space between and for the visuals you can hide those empty columns.

Upvotes: 1

Related Questions