+ Reply to Thread
Results 1 to 7 of 7

Plotting all (x,y) points that make a condition dependent on x and y return TRUE

  1. #1
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    Hello!

    Consider that I have a final score (SF), which is calculated by the weighted average of score 1 (S1 - weight 4) and score 2 (S2 - weight 6)
    Both scores vary between 0 and 10.

    I am trying to plot a graph where the X axis represents S1, the Y axis represents S2, and I need to show on this graph the area which SF>=7.

    I cant seem to find a proper graph type to this situation. I慨ot even sure excel can make such a graph.

    I tried combining all possibilities of S1 and S2 in 0,1 increments (resulting in 10.000 lines) and plotting a dispersion graph with the results, but that didnt work as the dispersion graph is limited to 255 points.

    Any insights on how to tacke this would be greatly appreciated.

    Thanks in advance!

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,650

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    Are you trying to plot all possibilities? Or actual scores?

    Because of your weights, there are very few possibilities that will give a score <7 (114 out of 10.201). I'm wondering how you will use this chart.

    In your example, you used 0,1 increments. Here is the plot I did. I am getting an error and a gap between the two areas, but you can get the idea. I am not sure what you mean by dispersion graph. I am using a scatter chart, which is limited to something like 256.000 points.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    6StringJazzer, TYSM for your reply.

    First of all, let me apologize for my english. I use excel in my native language (portuguese) and when I said "dispersion graph", I meant exactly "scatter chart". I plan to use the chart for a powerpoint presentation where I show the "approved" zone and the "not approved" zone. For some weird reason i am getting an error message saying that my scatter charts are limited to 255 points.

    Quote Originally Posted by 6StringJazzer View Post
    Because of your weights, there are very few possibilities that will give a score <7 (114 out of 10.201). I'm wondering how you will use this chart.
    Regarding the above quote, maybe I wasn't clear also. I am aiming to calculate the weighted average of 2 scores. Both scores (S1 and S2) vary between 0 and 10, which also limits SF to 0<SF<10.

    Assuming W stands for weight, the formula for the weighted average should be: ((S1*W1)+(S2*W2))/(W1+W2).

    The chart should look like this:
    Attachment 741734

    It seems that on your workbook, you multiplied the scores by their weights, but didnt divide by the sum of the weights afterwards.

    The point to doing this on excel is that the parameters are not defined yet, and I want the chart to update if I update the weights or the minimum SF for approval.

    Any further assistance would be greatly appreciated!

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,650

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    Yes, I made an error in calculating the weighted average. I did not calculate an average. I will have a second look. If you have a file that shows the error you are getting, that would be helpful.

    Your attachment is invalid. If you are attaching an image, I suggest that you first click on Go Advanced under the text area, then attach the image. If you are trying to attach a file:
    1. Under the text box where you type your reply click the Go Advanced button.
    2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
    3. Click the Choose File button to select a file to attached.
    4. Click the Upload button to upload the file.
    5. Then click the Close This Window button. Your file is now attached to your post.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,650

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    Here is a revised file. I don't have much intuition about what this chart should look like. I will try to do some validation.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    Oh, sorry for the bad link! I guess I messed up somewhere

    Sem t癃ulo.png
    Attemp 2 to upload
    (if this doesnt work, ill upload the image somewhere and post the link)

  7. #7
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Plotting all (x,y) points that make a condition dependent on x and y return TRUE

    It seems to have worked now! This is a sketch of the graph im trying to do. The closer from (0,0), the farther into the not approved zone you are. The closer to (10,10), the farther into the approved zone you are.and apart from (10,10), the other 2 vertices from the green triangle should show the S1 you need in case your S2 was 10, and vice versa

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 08-19-2019, 08:55 AM
  2. Return False or True with condition
    By stev_2109 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2019, 09:26 AM
  3. VBA for Make a cell blink if Condition True
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2012, 03:22 AM
  4. make OR() return 0 or 1 instead of true and false
    By boarders paradise in forum Excel General
    Replies: 16
    Last Post: 10-19-2009, 01:06 PM
  5. [SOLVED] How do I make the true return a drop down list in the IF function?
    By Brian in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-13-2006, 04:45 PM
  6. [SOLVED] RE: If condition is true return sumproduct of two arrays
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-14-2005, 01:05 AM
  7. If condition is true return sumproduct of two arrays
    By Felipe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-14-2005, 01:05 AM

Tags for this Thread

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