+ Reply to Thread
Results 1 to 6 of 6

Scoring the cells

  1. #1
    Registered User
    Join Date
    10-02-2006
    Posts
    12

    Post Scoring the cells

    hi there experts,

    I hope you can help me with this problem that I have.

    You'll see in the attachment that there are a range of behaviours D3 - D13.
    The behaviours have a weighting, which is dependant on their level (found in C3 - C13.

    When completing the form, the user will only enter data into the range E3-E13, where they will enter 0 for false and 1 for true

    These are then added in ranges G3 to H13, and the sum is worked out at the bottom of each column

    This is what I need to happen in the cells I14, J14, K14 and L14.

    1. When F14, G14 and H14 all equal 0 (I14 should equal 1)

    2. When F14 equals 3 and G14 equals smaller than 2 (J14 should equal 1)

    3. When F14 equals 3 and G14 equals more than 2 and H14 equals less than 2 (K14 should equal 1)

    4. When F14 equals 3 and G14 equals 4 and H14 equals more than 2 (L14 should equal 1)

    I hope you can help me make the right formula, or something similar that will help me achieve a similar outcome.

    Thanks in advanced.

    Ravi
    Attached Files Attached Files
    Last edited by ravisandhu; 04-20-2009 at 06:36 AM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Scoring the cells

    i14=IF(SUM(F14:H14)=0,1,"")
    j14=IF(AND(F14=3,G14<2),1,"")

    k14=IF(AND(F14=3,G14>2,H14<2),1,"")

    l14=IF(AND(F14=3,G14=4,H14>2),1,"")

    cant see what happens if g14=2 though
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Scoring the cells

    Good morning ravisandhu

    Are you aware that there is a gap between conditions 2 and 3? (ie if G14=2).
    Also, what to do if the conditions aren't true? The formulae below will return nothing.
    Quote Originally Posted by ravisandhu View Post
    1. When F14, G14 and H14 all equal 0 (I14 should equal 1)
    =IF(AND(F14=0,G14=0,H14=0)=TRUE,1,"")

    Quote Originally Posted by ravisandhu View Post
    2. When F14 equals 3 and G14 equals smaller than 2 (J14 should equal 1)
    =IF(AND(F14=3,G14<2)=TRUE,1,"")

    Quote Originally Posted by ravisandhu View Post
    3. When F14 equals 3 and G14 equals more than 2 and H14 equals less than 2 (K14 should equal 1)
    =IF(AND(F16=3,G16>2)=TRUE,1,"")

    Quote Originally Posted by ravisandhu View Post
    4. When F14 equals 3 and G14 equals 4 and H14 equals more than 2 (L14 should equal 1)
    =IF(AND(F17=3,G17=4,H17>2)=TRUE,1,"")

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  4. #4
    Registered User
    Join Date
    10-02-2006
    Posts
    12

    Re: Scoring the cells

    hi Martin and Dominic,

    Thanks so much for your response. Your suggestions have helped me move closer to what I am trying to achieve... but it's not where I want it to be yet... so i am hoping you can help me further.

    I have attached an updated Excel sheet.

    On the attachment, I have included a new cell M14.
    M14 is the total rating that needs to be displayed, between 1 to 4.

    if you try entering 1s in the E3-E13 range, as you work your way down, the ideal output is where the rating (M14) increases based on the criteria, i.e. if all the E3-E13 range are 1s, then the M14 rating should display 4.

    I need the formulas in J14, K14 and L14 to work in such a way that when they have displayed a score from data in E3-E15 range, they do not become blank again.

    I hope you have an idea (or ideas) of how to make this work.

    I look forward to hearing from you.

    Ravi
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-02-2006
    Posts
    12

    Post Re: Scoring the cells -

    I think I have an idea...


    The formula in J14 is the only one that's not working for me, it is:

    =IF(AND(F14=3,G14<2)=TRUE,1,"")

    Is there a away of keeping this formula but including an OR IF statement, that if cell K14=1, then J14 should output 1 as well.

    I hope this is possible, I just don't know how to do it.

    I hope you can help.

    many thanks.

    Ravi

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Scoring the cells

    Perhaps:
    =IF(OR(K14=1,AND(F14=3,G14<2)),1,"")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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