Results 1 to 10 of 10

Thread: checking certain cells and returning answer +

  1. #1

    checking certain cells and returning answer +



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I want to be able to enter "wd into a cell (C4) and want it to check say C4 to C23 look for the highest and return it +100 to cell Q4

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Can you give a better example. What exactly are you entering in C4? Not clear. And why then are you including C4 in your check range? Show a sample of your data and what the expected result would be for some random sample entries in C4.


  3. #3
    OK if you see player 8 has WD (withdrawn) for his game and his penalty for withdrawing from the game is the highest score of all players that game (which is player 15 score of 65) +100
    But if you look at the sheet it only gives him 100 not 165 like it should be
    Attached Files Attached Files

  4. #4
    so what i am after is a formula that looks at all the scores returns the highest score + 100 and enters that as his score for that game also it needs to add the 100 to the penalty box now just to add more to it if he wd again i want it to do the same but add 200 to his score and so on

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Maybe:

    =IF(C4="wd",MAX($C$4:$C$23),C4)+IF(D4="wd",MAX($D$4:$D$23),D4)+IF(E4="wd",MAX($E$4:$E$23),E4)+IF(F4="wd",MAX($F$4:$F$23),F4)+IF(G4="wd",MAX($G$4:$G$23),G4)+IF(H4="wd",MAX($H$4:$H$23),H4)+IF(I4="wd",MAX($I$4:$I$23),I4)+IF(J4="wd",MAX($J$4:$J$23),J4)+IF(K4="wd",MAX($K$4:$K$23),K4)+IF(L4="wd",MAX($L$4:$L$23),L4)+IF(M4="wd",MAX($M$4:$M$23),M4)+IF(N4="wd",MAX($N$4:$N$23),N4)+P4


  6. #6
    that works but does not show penalty points in the box I did upload a spread sheet above a sheet above

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    I used your spreadsheet and got 165 as you said it should.


  8. #8
    Ok not explaining myself here very well the rules are if you withdraw once you get the highest score that week plus 100.If you withdraw a second time you get highest points that week plus 200 points. IF you withdraw a third week they highest score that week +300 points and so on but I want the penalty points to be shown in column so they can see what penalty points they have

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Ok,

    Let's try this.

    First let's add a helper row to the bottom that get's the max for each game. You can hide these numbers, by colouring the font to match the background colour (as I did).

    So in C24 add formula:

    =MAX(C4:C23)

    copied across.

    Now in P4 add formula:

    =IFERROR(SUMPRODUCT((SMALL(IF(C4:N4="wd",C$24:N$24),ROW(INDIRECT("1:"&COUNTIF(C4:N4,"wd")))))+(100*ROW(INDIRECT("1:"&COUNTIF(C4:N4,"wd"))))),0)

    this formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER. It is an Array Formula. Then copy down.

    Finally in O4 add formula:

    =SUM(C4:N4,P4)

    copied down.
    Attached Files Attached Files


  10. #10
    Many thanks that works a treat thanks again hoping this is last problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •