+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : calculate value based on multiple low, medium, high cells

Hybrid View

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    3

    calculate value based on multiple low, medium, high cells

    I'm trying to figure out how to calculate a score based on whether or not the user entered an "x" in various cells. I've got 11 columns in my spreadsheet:

    Column 1 represents a new feature the user wants

    Columns 2 - 4 are used for indicating the significance as low, medium or high (user puts "x" in only one column).
    Columns 5 - 7 are used for indicating the effort as low, medium or high (user puts "x" in only one column).
    Columns 8 -10 are used for indicating the risk as low, medium or high (user puts "x" in only one column).

    In Column 11, I want to calculate a score based on the low, medium and high entries where low = 1, medium = 2 and high = 3. The minimum calculated scored is 3 and the max is 9.

    Suggestions?
    Last edited by lauriec; 01-18-2011 at 01:37 PM.

  2. #2
    Registered User
    Join Date
    01-12-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: calculate value based on multiple low, medium, high cells

    Heres the whole thing
    =COUNTIF(B3,"x")+COUNTIF(C3,"x")*2+COUNTIF(D3,"X")*3+COUNTIF(E3,"x")+COUNTIF(F3,"x")*2+COUNTIF(G3,"X")*3+COUNTIF(H3,"x")+COUNTIF(I3,"x")*2+COUNTIF(J3,"X")*3
    good luck
    Last edited by fiddle2oons; 01-12-2011 at 05:58 PM.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculate value based on multiple low, medium, high cells

    Perhaps:

    =SUM((B1="X")+(E1="X")+(H1="X"),((C1="X")+(F1="X")+(I1="X"))*2,((D1="X")+(G1="X")+(J1="X"))*3)
    or

    =MATCH("X",B1:D1,0)+MATCH("X",E1:G1,0)+MATCH("X",H1:J1,0)
    the latter would fail if you did not have an "X" in each

    another

    =SUMPRODUCT((B1:J1="X")*(MOD(COLUMN(B1:J1)-COLUMN(B1),3)+1))

  4. #4
    Registered User
    Join Date
    01-12-2011
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: calculate value based on multiple low, medium, high cells

    thank you, I will give this a try now.

  5. #5
    Registered User
    Join Date
    01-12-2011
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: calculate value based on multiple low, medium, high cells

    thank you as well, I'm set now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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