calculated item for Other

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)?

Dmitry B.

Posted 2016-02-09T23:52:13.587

Reputation: 175

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 – 2016-02-10T00:10:51.203

Yes, that's right. – Dmitry B. – 2016-02-11T01:24:43.557

Answers

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".

Kyle

Posted 2016-02-09T23:52:13.587

Reputation: 2 286

I want to do this using the calculated item feature if possible. – Dmitry B. – 2016-02-11T01:25:22.000

Asked: 2016-02-09T23:52:13.587

Viewed: 26 times

Active: 2016-02-11T01:23:33.013