• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help calculating + filling different tables

Wilco()

Wilco()

Member
I have a wrestle with this: for a football management game (hattrick.org for the ones who are interested) I am playing with excel to be able to pick my best players.


Now I am at the point where I keep going in circles.

I have added an excel sheet with a small extraction of the data I am using. If I can manage for field position I think I can convert them to others.


Basically I have a couple of sets.

1) Player data

2) The weight of a player depending the position of the player on the pitch

3) The end result (which should calculate the values corresponding to the position selected).


For example, I want to put in N2 the position I want to have this play on.

Say it would be “CD”, then I need to use the values on line (B)12 and calculate with the values (for Player B2).

Ie. 23,59% of E2 must be added to the value on E21 (Midfield).

100% of J2 must be added to the value on E18 (Central defence).


Who can help on which formulas to use (no VBA, not there yet)? I am getting a bit lost..
 

Attachments

  • example.xlsx
    12.8 KB · Views: 9
Dear Wilco()

=SUM((N2:N6="CD")*J2:J6)*E12+SUM((N2:N6="K")*J2:J6)*E11+SUM((N2:N6="IM")*J2:J6)*E13+SUM((N2:N6="K")*H2:H6)*D11

entered using Ctrl+shift+enter

This would work out the correct value for Central defence.

You shoulkd be able to modify for the other 6 end results.
 
Thanks. It does not seem to work by just copying and pasting. But it does give me an idea how to solve this puzzle.

Adding a question; I still seem to think Excel is 'stupid'. As it does seem to accept equations in a sum() function.
How do you look at problems to find a solution? Do you know what I mean? Your solution is very simple, while I am focussing on difficult methods.
 
Hello Wilco,

Try this Array Formula in C18, with CTRL+SHIFT+ENTER, then copy to other blocks.

=SUM(IFERROR(SUMIF($N$2:$N$7,$B$11:$B$15,OFFSET($C$2:$C$7,,SMALL(IF(ISNUMBER(MATCH(LOOKUP("zzzzz",C$17:C17)&"_"&$C$1:$M$1,LOOKUP(COLUMN($C$9:$L$9),COLUMN($C$9:$L$9)/($C$9:$L$9<>""),$C$9:$L$9)&"_"&$C$10:$L$10,0)),COLUMN($C$1:$M$1)-COLUMN($C$1)),COLUMN($C$10:$L$10)-COLUMN($C$10)+1)))*INDEX($C$11:$L$15,1,MATCH(LOOKUP("zzzzz",C$17:C17),$C$9:$L$9,0)):INDEX($C$11:$L$15,,MATCH(2,1/LOOKUP(COLUMN($C$9:$L$9),COLUMN($C$9:$L$9)/($C$9:$L$9<>""),$C$9:$L$9=LOOKUP("zzzzz",C$17:C17)))),0))

I know this is a short formula, hope some will come up with very long formula :)
 
You must log in or register to reply here.
Back
Top