+ Reply to Thread
Results 1 to 11 of 11

Calculated Fields and IF THEN Statements

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    Lightbulb 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. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    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.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    Re: Calculated Fields and IF THEN Statements

    Quote Originally Posted by xlnitwit View Post
    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. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    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. #5
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    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. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    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. #7
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    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. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    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. #9
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    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. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    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. #11
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2010
    Posts
    12

    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?

    How do I go about this in PowerQuery?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculated fields; sum of three fields compared with a 4th
    By jomili in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2017, 09:31 AM
  2. [SOLVED] Pivot calculated fields - dropping needed fields without dropping calculations
    By Aly1978 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-15-2017, 10:25 PM
  3. Replies: 2
    Last Post: 10-26-2015, 06:49 AM
  4. Calculated Fields Based on Running Total Fields?
    By Kruncher in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-12-2006, 01:25 PM
  5. Calculated fields
    By nc in forum Excel General
    Replies: 0
    Last Post: 05-09-2006, 09:50 AM
  6. [SOLVED] If statements in Pivot Table Calculated Fields
    By Carmen in forum Excel General
    Replies: 1
    Last Post: 09-24-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1