# Thread: Formula (xp)

1. ## 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. ## 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. ## Re: Formula (xp)

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

4. ## 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
•