1

A field I use for columns in a pivot table has a large number of items. Only a handful of these items are significant. The rest I group into Others. This I know how to do. The issue I'm trying to solve is that every few weeks a new item is added to the data. So when this happens I have to redefine the formula (Others := item1+item2+item3+item3+...+itemN). Hoever, the set of significant items never changes.

Is there a way I can calculate Others as <FIELD_TOTAL> - (itemA + itemB + itemC+itemD)?

Improve this question
2
  • So basically, you want to calculate the sum of a set of data that gets a new element every few weeks, witout having to write the formula?
    – ecube
    Feb 10, 2016 at 0:10
  • Yes, that's right.
    – Dmitry B.
    Feb 11, 2016 at 1:24

1 Answer 1

Reset to default
0

Add a column to your data with this formula:

=IF(ISERROR(MATCH(B1,{"itemA","itemB","itemC","itemD"},0)),"other",B1)

If B1 is in the list between the curly braces, it returns the value in B1, otherwise it returns "other".

Improve this answer
1
  • I want to do this using the calculated item feature if possible.
    – Dmitry B.
    Feb 11, 2016 at 1:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .