Closed Thread
Results 1 to 16 of 16

Plotting Pareto Frontier

  1. #1
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    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
    pareto frontier Google SearchSearch completed.png

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,977

    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. #3
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,977

    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. #5
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    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.
    Happy with my advice? Click on the * reputation button below

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Plotting Pareto Frontier

    Crooza,

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

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Plotting Pareto Frontier

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

  9. #9
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    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?
    Thanks in advance
    RaniaConfig..xlsx
    Last edited by raniat123; 05-14-2015 at 04:26 AM.

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    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. #11
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.
    Thanks in advance
    Attached Files Attached Files

  13. #13
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    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. #14
    Registered User
    Join Date
    03-22-2015
    Location
    Houston, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Plotting Pareto Frontier

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

  15. #15
    Registered User
    Join Date
    03-27-2021
    Location
    Philippines
    MS-Off Ver
    Office 360
    Posts
    2

    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. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: Plotting Pareto Frontier

    Administrative Note:

    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.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 11-24-2014, 10:45 AM
  2. Help with efficient frontier in excel
    By Saadi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2012, 07:05 PM
  3. Efficient Frontier/Mean-Variance Optimization
    By Stratuser in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2011, 09:28 AM
  4. Markowitz efficient frontier
    By thedon_1 in forum Excel General
    Replies: 7
    Last Post: 02-10-2010, 06:53 AM
  5. production frontier
    By kckar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-15-2006, 05:34 PM

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