+ Reply to Thread
Results 1 to 4 of 4

Formula for Clause Point System - E.g. Clause 1 = 10 points, Clause 2 = 5 Points

  1. #1
    Registered User
    Join Date
    02-04-2019
    Location
    Australia
    MS-Off Ver
    MSOffice 2010
    Posts
    25

    Formula for Clause Point System - E.g. Clause 1 = 10 points, Clause 2 = 5 Points

    Hey Everyone just need a little bit of help, not so good with trying to turn algebra into excel formulas :/

    basically I'm matching a Company Clause to point system, so the amount of breached clauses equals a total of point

    for example Company A has breached clause 1,2 and 3

    Clause 1 = 10 ( High breach level)
    Clause 2 = 5 (Medium breach level)
    Clause 3 = 5 (Medium breach level)

    (note any point breach total over 10 = high risk)

    so therefore Company A - breached Clause 1,2,3 = 20 points = high Risk break

    Any help is really appreciated

    document attached with dummy solution
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula for Clause Point System - E.g. Clause 1 = 10 points, Clause 2 = 5 Points

    Please try at E2 and drag down

    =SUMPRODUCT(ISNUMBER(MATCH(B2:D2,'Clause Rating'!$A$3:$A$65,))*10+ISNUMBER(MATCH(B2:D2,'Clause Rating'!$B$3:$B$16,))*5)

    Use custom format # to hide zero
    or
    =IFERROR(1/(1/SUMPRODUCT(ISNUMBER(MATCH(B2:D2,'Clause Rating'!$A$3:$A$65,))*10+ISNUMBER(MATCH(B2:D2,'Clause Rating'!$B$3:$B$16,))*5)),"")
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,914

    Re: Formula for Clause Point System - E.g. Clause 1 = 10 points, Clause 2 = 5 Points

    Or try:

    =IF(COUNTA(B2:D2)=0,"",SUMPRODUCT(COUNTIF(B2:D2,{"Annex B",">=10","<10"}),{10,10,5}))

  4. #4
    Registered User
    Join Date
    02-04-2019
    Location
    Australia
    MS-Off Ver
    MSOffice 2010
    Posts
    25

    Re: Formula for Clause Point System - E.g. Clause 1 = 10 points, Clause 2 = 5 Points

    Thanks again Bo, that worked really well! really appreciate the help

+ 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. if clause
    By faisal.ta in forum Excel General
    Replies: 5
    Last Post: 09-03-2016, 03:20 PM
  2. combined vlookup/if clause formula?
    By Esther19 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-28-2016, 01:05 PM
  3. IF clause inconsistency
    By gloria in forum Excel General
    Replies: 6
    Last Post: 06-11-2009, 06:03 AM
  4. Array Formula Help -IF / Else clause?
    By Jay in forum Excel General
    Replies: 4
    Last Post: 08-15-2006, 02:30 AM
  5. conversion to formula of string contaning IF clause
    By JeanBQ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2006, 12:55 PM
  6. IF Clause
    By juergenkemeter in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2006, 07:17 PM
  7. Where Clause
    By bint in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2005, 10:31 AM

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