checking certain cells and returning answer +

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
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
 
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.
 
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
 

Attachments

  • league 1.xlsx
    13.4 KB · Views: 11
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
 
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
 
that works but does not show penalty points in the box I did upload a spread sheet above a sheet above
 
I used your spreadsheet and got 165 as you said it should.
 
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
 
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.
 

Attachments

  • league 1.xlsx
    16 KB · Views: 14
Many thanks that works a treat thanks again hoping this is last problem
 
Back
Top