• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a dashboard to get travel rates


I am working on creating a dashboard for travel rates received from 2 vendors for adults and child for around 400 airports spread around the world. The information is sent to us on a quarterly basis.

I need to come up with a useful automated dashboard which can give me the

1. Average rate for the full year based on 4 quarters - for a city (selected from drop down) - for both vendors to be displayed - I was thinking of displaying rates for each quarter and then give the average price

2. Highest rate for a city for both vendors - by quarter, by year

3. Lowest rate for a city for both vendors - by quarter, by year

Which is the best approach keeping in mind that the raw data is added/changed every quarter. Any idea for the dashboard screen itself would be great help. This will be my first dashboard. Thank you in advance.

That you Vietm, appreciate it. But I did the pivot table, but the issue is the user group may not be that savvy to use the pivot every time the data changes. Thats why I'm thinking of creating a dashboard sort of for them to keep adding the data. I'm working on it. will post when done so I can have your feedback.


Excel Ninja
... You can add data to source of pivot table!
... and You can 'easy' to find many kind of values quickly
without any massive set of formulas

I have done the Pivot part of it. Now working on the dashboard but first , trying to get the formulas to work. I am attaching the work file here for you to see what I have done so far using formulas.

Appreciate help in figuring out how to go about using a formula to calculate the average IATA rate for a city for the year and Average market rate for the same city. Please also review what I have done so far and provide any feedback on improving it. Thanks in advance.



Excel Ninja
... You want to have one file for pivot to collect datas >
copy that result to next file
> have many formulas
just one file with pivot, without formulas
... I add that Your 'select' town option for use too.
(and compare to file sizes too...)