Excel help

2010-01-21 09:19:57

#1

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

Right, this is a long shot, but who knows, maybe we have some excel gods in here.

This is going to be extra hard, as my excel is in Norwegian, but I'll try my best to describe it;

I am currently using the SUM-IF command (probably not the actual name, but that's how it would translate.) Basically, it works like so;

=SUM-IF(Area; Criteria; Sum-Area)

Area = Area for evaluation
Criteria = Criteria a cell has to meet in the area
Sum-Area = Area in which the cells to be summed lie.

Sooo....

=SUM-IF(A1:A10; 12; D1:D10)

Would check the area A1:A10 for cells with the value of twelve. If Twelve is found, it will sum up the corresponding cells from the Sum-Area.

If A2, A3 and A7 had 12, the cell with said formula would be 2+D3+D7

yeah.....

anyway, what I want to do is limit the selection to the LAST SIX instances where the criteria is met.

If A2, A3, A4, A6, A7, A8, A9 and A10 had the value "12", I'd only want A4, A6, A7, A8, A9 and A10 (and thus their D counterparts) considered.

lulz. Any idea?

https://static.bf2s.com/files/user/26774/flickricon.png

2010-01-21 09:42:34

#2

SEREMAKER: BABYMAKIN EXPERT √; +2,187|6742|Mountains of NC

https://farm4.static.flickr.com/3166/2429948367_345483ea4c.jpg

https://static.bf2s.com/files/user/17445/carhartt.jpg

2010-01-21 09:45:04

#3

Kimmmmmmmmmmmm: ©; +1,811|6170

code box

hmm excel is so 1995 for me, sorry bud it's gone from the memory banks

2010-01-21 10:31:39

#4

Ilocano: buuuurrrrrrppppp.......; +341|6841

If not for "limit the selection to the LAST SIX instances where the criteria is met", this would be easy.

2010-01-21 10:36:30

#5

King_County_Downy: shitfaced; +2,791|6771|Seattle

Ken Jennings can help... PM him a link to this thread.

Sober enough to know what I'm doing, drunk enough to really enjoy doing it

2010-01-21 10:58:48

#6

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

Ilocano wrote:
If not for "limit the selection to the LAST SIX instances where the criteria is met", this would be easy.

well yeah, that's the point.

@Downy ~ right, thanks I have an idea though, I'll test it out and if that fails, I'll PM him

2010-01-21 11:39:01

#7

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

SUM-IF is the right function. Is the data in the 'criteria' in another sheet or tab or on the same sheet? You just want to limit the selection to A4:A10. <-----plug that in for the 'range'. Post screenies if you can

2010-01-21 11:43:49

#8

Ilocano: buuuurrrrrrppppp.......; +341|6841

KEN-JENNINGS wrote:
SUM-IF is the right function. Is the data in the 'criteria' in another sheet or tab or on the same sheet? You just want to limit the selection to A4:A10. <-----plug that in for the 'range'. Post screenies if you can

But it's not just the last 6, but the last 6 that meet the criteria.

2010-01-21 11:49:22

#9

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

What Ilocano said. That's why it's so hard

Here's a screen. It's a betting system really. Home Rating is supposed to go find the last three home and last three away games for the home team, and use the numbers from A/H weighted to make a home team rating for the match.

The problem is finding the last three home and away games.

http://static.bf2s.com/files/user/6922/bet%20system.PNG

Last edited by Jenspm (2010-01-21 11:49:38)

2010-01-21 11:58:02

#10

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

send me the file if you can.

2010-01-21 12:01:22

#11

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

PM me your e-mail?

2010-01-21 12:40:11

#12

Ilocano: buuuurrrrrrppppp.......; +341|6841

Brute force:

D E F

2 =+IF(D1>0,1,0) =+(SUM(E1:E10))*E1
3 =+IF(D2>0,1,0) =+(SUM(E2:E10))*E2
4 =+IF(D3>0,1,0) =+(SUM(E3:E10))*E3
1 =+IF(D4>0,1,0) =+(SUM(E4:E10))*E4
0 =+IF(D5>0,1,0) =+(SUM(E5:E10))*E5
1 =+IF(D6>0,1,0) =+(SUM(E6:E10))*E6
0 =+IF(D7>0,1,0) =+(SUM(E7:E10))*E7
1 =+IF(D8>0,1,0) =+(SUM(E8:E10))*E8
1 =+IF(D9>0,1,0) =+(SUM(E9:E10))*E9
0 =+IF(D10>0,1,0) =+E10
=SUMIF(F1:F10,"<7",D1:D10)

Column D is the amounts to sum, zeroed if not meeting the criteria.

I'm sure I can compress it, but I don't have the time right now.

2010-01-21 14:24:53

#13

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

The problem is that there will be multiple qualifiers. Each separate teams needs it's own formula. I should have the file back to Jens shortly.

Actually it's a bit more complicated. Lots of formulas here buddy. I'll see if I can spend some time on it.

2010-01-22 14:20:25

#14

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

KEN: I don't know how far you've come with this, but I just want to shoot in that it'd be best if it was automatic, and I didn't have to enter the formulas manually depending on the teams.. Makes back-testing a lot easier.

I got an idea though;

What if one made a column for each team further out to the right, with this command on every row;

=IF(B4=Liverpool;row();"")

"Liverpool" would be swapped out with the cell-ID of the header of the column, which is the team name.. Liverpool is just there for ilustration purpases.

Anyways, that'd give you the row number of each Liverpool game. You could then make a new column, and rank the row numbers in descending order (last game is 1). Then you could do a SUM-IF with the additional criteria of "F6<4", to get the last 3 games.

This should work..

HOWEVER, how would one get the cell to identify the correct column that corresponds to the playing team?

2010-01-22 14:23:37

#15

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

yes, I already made another sheet with just the team names for reference. The key for me is deciphering your formulas for the home/away weighted scores. If you could just tell me instead of me working backwards it will help.

2010-01-22 14:27:43

#16

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

KEN-JENNINGS wrote:
Jens your excel file is serious business. I don't have the time to go through the cells, can you just tell me the formulas for all your columns (home weighted, away weighted)?

H Weighted

Code:

=HVIS(SUMMERHVIS($F$2:F193;F194;$D$2:D193)=0;0;D194/((SUMMERHVIS($F$2:F193;F194;$D$2:D193))/(ANTALL.HVIS($F$2:F193;F194))))

Code:

=HVIS(SUMMERHVIS($F$2:F193;F194;$E$2:E193)=0;0;E194/((SUMMERHVIS($F$2:F193;F194;$E$2:E193)/ANTALL.HVIS($F$2:F193;F194))))

A Weighted

Code:

=HVIS(SUMMERHVIS($C$2:$C193;$C194;$E$2:$E193)=0;0;$E194/((SUMMERHVIS($C$2:$C193;$C194;$E$2:$E193))/(ANTALL.HVIS($C$2:$C193;$C194))))

Code:

=HVIS(SUMMERHVIS($C$2:$C193;$C194;$D$2:$D193)=0;0;$D194/((SUMMERHVIS($C$2:$C193;$C194;$D$2:$D193))/(ANTALL.HVIS($C$2:$C193;$C194))))

Is that what you want? Or in words? heh.

Translations;
HVIS = IF
SUMMERHVIS = SUM-IF
ANTALL.HVIS = Counts the number of "hits"

2010-01-22 14:32:25

#17

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

I just need the formula. Whats HVIS? SUMMERHVIS? ANTALL.HVIS?

What is the formula for the home weighted? All home games / something? Just tell me what columns/rows you are dividing/multiplying/adding. I can set up a one formula that will bring back results for each team...

2010-01-22 14:36:51

#18

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

oh, I assume words.. You already have the formulas..

I'll start with the first column in H Weighted. In words, this is:

(Home team's goals in the game)/((Away team's total goals allowed away)/(Away team's total away games))

Sooo... How many goals the home team scores compared to the away team's average.

Second column in H weighted:

(Away team's goals)/((Away team's total goals away for the season)/(Away team's total away games))

First column A weighted:

(Away team's goals)/((Home team's total goals allowed at home that season)/(Home team's total home games))

Second column A weighted:

(Home team's goals)/((Home team's total goals scored at home)/(Home team's total home games))

Do you need decifering of the formula as well - ie what is what?

2010-01-22 14:38:16

#19

Jenspm: penis; +1,716|6906|St. Andrews / Oslo

Ok, we're talking past each-other, lol.

What exactly do you want?

2010-01-22 14:38:41

#20

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

no that should do hopefully. Let me see what I can do.

2010-01-22 15:01:20

#21

Dauntless: Admin; +2,249|6916|London

KEN-JENNINGS wrote:
Let me see what I can do.

nothing

2010-01-22 16:42:15

#22

Beduin: Compensation of Reactive Power in the grid; +510|5924|شمال

hvis og hvis min røv er spids!

الشعب يريد اسقاط النظام
...show me the schematic

2010-01-22 17:00:04

#23

jsnipy: ...; +3,276|6696|...

write a macro

2010-01-22 17:22:28

#24

KEN-JENNINGS: I am all that is MOD!; +2,977|6805|949

not that easy. It's formulas for formulas, with conditions and qualifiers

2010-01-28 07:20:29

#25

__M@ver1ck__: Member; +2|5463

jsnipy wrote:
write a macro

What he said.

Ive forced Excel to do a lot over the years, and there isnt much you cant do with advanced filters, criteria, lookups and complex calculations. BUT there is a point when you have to think its going to be easier to just write a loop in VBA to do what you want and execute on value entry or assign it a button click.

Write the VBA.

If i had time id do it, but right now im snowed.

R.

BF2S Forums Excel help

Ilocano wrote:

KEN-JENNINGS wrote:

KEN-JENNINGS wrote:

Code:

Code:

Code:

Code:

KEN-JENNINGS wrote:

jsnipy wrote:

Board footer