Teema: Tools

I'm working on a spreadsheet to do some player analysis and its going pretty well, but I've run into a difficult problem and would like so help:

I have a row of data and every ninth cell contains a number representing how far from the ideal the player is currently. This is repeated 7 times. I want to find the minimum value in each of these cells but I want to ignore the cell if another reference cell is zero. Worse yet, then I want to return a value located somewhere to the left of the cell with the minimum value.

Any ideas? Please PM me for more details if necessary.

And yes, once its complete I will share it.

use an if statement. if cell x is blank or is zero then don't calculate cell y

XOR!

what legg said, but you can get really complicated and use nested if statements, like =if(A1=0,0,if(B1="W",A1*.85, if(B1="D",A1*.5,1)))

That would check and if A1 was 0, it would just do zero, but say B1 was the position, if A1 is not zero, it would then see if B1 is "W" and calculate A1*.85, and if it's not W it will check to see if it's "D" and then do A1*.5, and then if all of that is false, just 1.

actually I am using a really nasty if statement, but the fix I made that made it much easier was tossing a high number 999 into the lookup value if the reference cell is 0. Its working great. 2/10 tables completed, the last 8 shouldn't take too long. Then I need to work on presentation.

Wheres the then and else!

after the commas

should be after ";"s not ","s but your heart is in the right place!

=IF($J128<MIN($S128;$AB128;$AK 128;$AT128;$BC128;$BL128);B128 ;IF($S128<MIN($AB128;$AK128;$A T128;$BC128;$BL128);K128;IF($A B128<MIN($AK128;$AT128;$BC128; $BL128);T128;IF($AK128<MIN($AT 128;$BC128;$BL128);AC128;IF($A T128<MIN($BC128;$BL128);AL128; IF($BC128<$BL128;AU128;BD128)) ))))

That is what i ended up with, fun?

Brilliant! I'm glad some people out there really do understand the absolute referencing that you can do

I've had one before that wrapped 5 times down in the formula bar. That was a pain in the ass, because then I couldn't get to the top rows of my spreadsheet

Ok people, I've thrown together a first pass at a tool for analyzing ideal player positions. On the main tab you have to manually enter roster information, attributes and Q for each of the seven attributes (sadly in the Google docs version you can't see the Q effect).

Then specify ratios for each of the attributes for up to five player types (I have two examples).

Then flip to the report tab to see the attributes for each player for each player type (Google docs does not handle conditional formatting so color coding based on Q is not visible)

The spreadsheet can be seen on Google docs. I would suggest saving a copy and altering it for yourself. Please provide any feedback and recommendations for other improvements/additions.

Btw, I would suggest defining the ratios for five player types and then entering data for only a couple players. That way you can see if you like what you see before entering a whole bunch of data.

Also it only supports 50 players at this time.

And finally the spreadsheet can be found here:
spreadsheets0.google.com/...

cannot make the connection keep getting an error message
that says it cant find the https