Results 1 to 4 of 4

Thread: Formula (xp)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula (xp)

    Ok it is 8:30PM and my brain seems to be turned off. I need to create a formula but have no idea how to do it.

    In the template I need to display the Marketpairdepartingcityname and the Marketpairarrivalcityname and then do some math on the flights to those cities.
    The problem is I have no idea how to group those items without manually typing it in.

    In the attached docment you will see a tab called data where it displays all the info. In the template tab or sheet 1 I have a sample of Montreal to Toronto. I would like this to appear automatically in Alphabetical order by Marketpairdepartingcityname. In the data tab you will notice there are about 10 or more of Montreal to Toronto, I would need the formula to display just one of those in the template.

    ANy thoughts

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula (xp)

    Have you tried a Pivot table?

    select a cell in your data
    Data - pivot table report
    Drag Marketpairdepartingcityname filed to row
    DragMarketpairarrivalcityname field to row

    Drag any fields to calc (you can dbl-clcik to change to sum, avg, count etc.)

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (xp)

    I personally hate pivot tables. Can I use an array or formula?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula (xp)

    Pivot table will be faster, and will automatically create the unique list:

    You can do it with an array formula but you will have to list the unique "from" and "to" items:

    I am not usre exactly what numbers you want to get so I will give a typical formula. Change as desired:
    If the summary table has:
    A2 is the first "Marketpairdepartingcityname"
    B2 is the first "Marketpairarrivalcityname"

    C2 can be the (eg) the "average" of the "Average Flight Cost" column with the Array (confirm with ctrl-shift-enter) formula:
    =AVERAGE(IF((Data!$I$2:$I$115=$A2)*(Data!$J$2:$J$1 15=$B2),Data!$O$2:$O$115))

    Fil in A3, B3 with the next from /to pair" and continue in a/b to fill in all the pairs (the pivot table can do these for you). Then you can copy C3 down the rows to get the average for each of the pairs. You can add other sum/average/count info with other columns as desired.

    If you add new from/to pairs to the table you must add them to manually to the list in the summary table. As your data set gets larger and larger, it might get sluggish due to the array formulas.

    Steve

    You can add assitional columns change

Posting Permissions

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