1. ## checking certain cells and returning answer +

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  Reply With Quote

2. 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.  Reply With Quote

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  Reply With Quote

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  Reply With Quote

5. 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  Reply With Quote

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

7. I used your spreadsheet and got 165 as you said it should.  Reply With Quote

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  Reply With Quote

9. 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).

=MAX(C4:C23)

copied across.

=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.

=SUM(C4:N4,P4)

copied down.  Reply With Quote

10. Many thanks that works a treat thanks again hoping this is last problem  Reply With Quote

#### Posting Permissions

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