# Calculated Fields and IF THEN Statements

1. ## Calculated Fields and IF THEN Statements

Hello,
I have a PivotTable with a bunch of prices. I would like to organize the data into bands, so Band A - 'Eveything Below \$10' and Band B - 'Everything above \$10'. In my Calculated Field, how do I do - =IF('Price'<10,"Band A","Band B").

Example Data:

Product|Price
Car|5
House|20

Thanks,
Sharp

2. ## Re: Calculated Fields and IF THEN Statements

Hi,

Calculated Fields are always numeric values. If you want to do banding, you can either add a row field and group it, or it is often easier to use a lookup table and a formula in the source data.

3. ## Re: Calculated Fields and IF THEN Statements

Originally Posted by xlnitwit
Hi,

Calculated Fields are always numeric values. If you want to do banding, you can either add a row field and group it, or it is often easier to use a lookup table and a formula in the source data.
I do have bands in the source data. However, they don't seem to be working.

For example, I have the following:

Industry|Client|Price|Band
Auto|FORD|100|'>100'
Auto|FORD|200|'>100'
Auto|Mazda|500|'>200'

When I apply my filters to the PivotTable, the price converges and groups the information. So, I have:

Industry|Client|Price|Band
Auto|FORD|300|'>100'
Auto|Mazda|500|'>200'

However, this is incorrect for FORD as it should be '>200'.

Thanks,
Sharp

4. ## Re: Calculated Fields and IF THEN Statements

Clearly the banding in the source data you posted is not correct, which is why the pivot table is grouping the way it is. As you have not said how the banding is created, I cannot assist with that.

5. ## Re: Calculated Fields and IF THEN Statements

So, in the source data, I simply use an IF statement to define each individual Price occurrence. The problem is when the PivoTable summarizes the total Price for FORD the Bands don't make sense

6. ## Re: Calculated Fields and IF THEN Statements

Oh I see- you want to band the aggregated data. You cannot do that directly within a pivot table. Do you have Power Query available to you?

7. ## Re: Calculated Fields and IF THEN Statements

Unfortunately, no I don't. I googled around and it seems it's an add in for Excel 2013. Unfortunately, I cannot install anything additional to this computer. Is there a work around we could try?

8. ## Re: Calculated Fields and IF THEN Statements

You would have to either use formulas adjacent to the pivot table, or perform the aggregate calculation in the source data. Both have drawbacks, depending on what exactly you are doing with your pivot table and how you plan to use the bands.

9. ## Re: Calculated Fields and IF THEN Statements

Hmm, ideally I wanted to be able to use the Bands as a filter whereby I could display the price range needed. I guess there's no way around it...I could add it as a 'Row Field' and do 'Group By' but I actually have a bunch of bands to be applied as filters and not just the price one.

10. ## Re: Calculated Fields and IF THEN Statements

I think you would have to do the banding in the source data then, or build a pivot from your pivot table.

11. ## Re: Calculated Fields and IF THEN Statements

I was thinking about that but because my report has so many filters and so many options that the client can pick from..I'm not sure what to calculate the band on. For example, there are Open and Closed Accounts. Should I be applying the aggregate calculation on Open Only or Closed Only?

There are currently 1 users browsing this thread. (0 members and 1 guests)