# Plotting Pareto Frontier

1. ## Plotting Pareto Frontier

Hi everyone,
I work in the Architectural design field, and I'm testing about 30 designs/ configurations to optimize the building's thermal, and lighting performance. I have an Excel sheet with three columns, the first column has a configuration name (text), the second column has each configuration's temperature value (number between 30 to 45 C degree, this number should be minimized) and the third column has each configuration's lighting value (number between 0 to 100 lux, which should be maximized ). Is it possible to create a Pareto frontier in Excel (I attached an example image) to pick the most optimized configuration. The ultimate solution should have the lowest temperature and highest lighting value. Here is a quick example

Configuration 1, 30, 80
Configuration 2, 35, 60
configuration 3, 45, 95

Thanks

2. ## Re: Plotting Pareto Frontier

Many contributors cannot view .png files, due to a fault in the forum software. Attach an Excel workbook, or if it has to be an image make it a .jpeg or .pdf file.

Pete

3. ## Re: Plotting Pareto Frontier

Hi Pete,

The image was for a generic pareto frontier chart, if you search on Google images for " Pareto frontier" you'll find may of them.

Rania

4. ## Re: Plotting Pareto Frontier

If you are looking for help, then you should provide potential helpers with as much information as you can.

Pete

5. ## Re: Plotting Pareto Frontier

efficient frontier 1.xlsx
Here's one I did a number of years ago (I think). It will probably do what you want it to do. Follow the general logic in the spreadsheet and you'll work it out.

6. ## Re: Plotting Pareto Frontier

How much of your question is the charting aspect, and how much of it is the optimization aspect? I guess I ask, because I see them as two distinctly separate operations. First, one would analyze the data in the spreadsheet to find the optimum values. I have no knowledge of architecture or similar engineering, but I expect the analysis involves:
1) Selecting an appropriate "objective function" to be optimized that will involve temperature and lighting values and any other variables that are part of the analysis.
2) Numerical algorithms that will optimize that objective function. If done in Excel, I would expect these algorithms to involve the Solver add-in in some way.

Once the analysis is complete, then one would generate the chart. At first glance, the sample chart looks to me like a basic XY scatter chart with 2 to 4 series formatted as square markers without lines and the "pareto frontier" is plotted as a red line without markers. If you are not familiar with the process of creating scatter charts in Excel, I might suggest you start with this help file: https://support.office.com/en-us/art...5-104A9018B86E

In a divide and conquer sense, I think I would suggest that you start with the analysis of temperature and lighting. How are these values determined and how are they interrelated?

7. ## Re: Plotting Pareto Frontier

Crooza,

Thank you so much, that's exactly what I wanted.

8. ## Re: Plotting Pareto Frontier

Great. Glad it worked. You should mark the thread as solved now

9. ## Re: Plotting Pareto Frontier

Hi Crooza,
I have a quick question about the Pareto frontier excel sheet you sent me. I'm assuming that the this sheet calculates Pareto solution based on optimizing both objectives ( column X and Y). What if I want to maximize one objective and minimize the other ?
I've attached an example file, I would like to determine the best configuration based on the highest numbers in column B and the lowest numbers in column C.
Also, do you have an idea how to plot a Pareto Frontier based on three objectives?
RaniaConfig..xlsx

10. ## Re: Plotting Pareto Frontier

Hi. I'll look at it tomorrow for you.

In regards to 3 frontiers - well I've never considered it but let me think about it and I'll let you know.

11. ## Re: Plotting Pareto Frontier

It looks like your sheet works for me if I sort Y from largest to smallest. When I sorted it this way the effecicmt solutions looked very reasonable to (maximum daylight (y) and lowest temp (X). However, the graph looks weird to me. I attached it to this post.

12. ## Re: Plotting Pareto Frontier

I fixed the graph min and max threshold of the graph and I think it looks fine . Could someone confirm that this pareto front looks ok, because it's going to be a part of a published paper.

13. ## Re: Plotting Pareto Frontier

I've prepared a solution (I think) but have been trying to upload files for two days now and am having real trouble with the website and the file upload manager module. I'll keep trying

14. ## Re: Plotting Pareto Frontier

Hi Crooza, if you'd rather email the file , here is my email rn.labib AT gmail. Thanks again.

15. ## Re: Plotting Pareto Frontier

Good day,

I am also trying to make a pareto frontier for two variables just like what is in the thread. Can I also get a copy of the pareto frontier solution you have?
Thank you!

16. ## Re: Plotting Pareto Frontier

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

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

#### 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