+ Reply to Thread
Results 1 to 11 of 11

Prioritizing weighted data for "sub-priority" then again for Overall Priority??

  1. #1
    Registered User
    Join Date
    01-06-2015
    Location
    Des Moines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    PrioritizationTemplate1.xlsx on the Sheet named Best Option-I have 3 weighted columns(B=risk-60%, C=frequency-20%, D=complexity-20%,) for 5 different departments(A) that are prioritized in a column(E). Column(F) contains 4 options "for additional weighting" BC=(-70% of E), BP=(-30% of E), NA=0%, and BC,BP=((-70% of E)-(-30% of E)).

    First- does any of this make sense? and how can I accomplish this? Any assistance is greatly appreciated!
    Attached Files Attached Files
    Last edited by BrettS; 01-09-2015 at 02:44 PM. Reason: clarifying spreadsheet-sheet

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Column(F) contains 4 options "for additional weighting"
    There's nothing in col F.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-06-2015
    Location
    Des Moines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Sorry, I think I have the correct spreadsheet there now- Column F will have 4 choices (BC),(BP),(NA) and (BC,BP)

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Still nothing in col F; and if there were, what do those mean?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    With VLookup, see the green cells in the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    01-06-2015
    Location
    Des Moines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Awesome!! Almost home, just one glitch- The amount currently in Column G needs to be subtracted from Column E to give me the number they are looking for. For example G2 should be 5 and G3 should be 6...Which I figured out by just adding E2- at the beginning of the equation but the result in G5 should be 5.04(I think) [the total of 14*.7=9.8, then 17-9.8=7.2, then 7.2*.3=2.16, then 7.2-2.16= 5.04] making it the 2nd highest priority due to having the 2nd lowest number. Does any of that make sense?
    Last edited by BrettS; 01-09-2015 at 02:39 PM. Reason: incorrect columns posted in reply

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    g2=E2-VLookup($F2,$J$2:$K$5,2,0)*$E2 and drag down

  8. #8
    Registered User
    Join Date
    01-06-2015
    Location
    Des Moines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Everything is Excellent.. except I think the BC,BP% is incorrect in K5. It would need to 1st take 70% from E, then and additional 30% from that result. So how do I calculate that percentage? If I use 64% it gives me the answer I am looking for but how would I show that that actually means something? where would I get 64% from or would that only work for the 1 exact number of 14 in E5?
    Last edited by BrettS; 01-09-2015 at 03:14 PM. Reason: additional info

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Please Login or Register  to view this content.
    I think that is 70% * 30% = 21%.

    example

    value is 100
    70% * 100 = 70 => after that 30% of 70 = 21.

    That is also the % I used in the file.


    So why do you think it should be another %.

  10. #10
    Registered User
    Join Date
    01-06-2015
    Location
    Des Moines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    Okay I may be doing too much here but here is why I question the percentage(you are correct though 30% of 70% = 21%).
    When I do it manually with 14 in the E column- [the total of 14*.7=9.8, then 14-9.8=4.2, then 4.2*.3=1.26, then 4.2-1.26= 2.94]
    And 21% of 14 is 2.94 Is that wrong? Wait- so if I just leave the E2- off of that equation in G5 then I get the correct result(I think)

    Hey, thank you so very much- this works great and I am extremely grateful to you for your time and effort!!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Prioritizing weighted data for "sub-priority" then again for Overall Priority??

    If you tell me the way the answer is calculate, I will make the formula formula for you.

+ 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. Sorting specified row data on priority
    By saykyoe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2013, 04:54 PM
  2. Return "green", "yellow" or "red" from date/age and priority ranking
    By Cantaloop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 12:12 AM
  3. highest "priority" among all matched instances (multiple conditions)
    By dyuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 03:00 AM
  4. [SOLVED] Displaying the one with the highest "priority" among all matched instances
    By dyuk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-14-2012, 06:36 AM
  5. Ranking a value in a "priority bucket" 1-10
    By PosiJoel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-21-2012, 01:04 PM

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