Excel Help needed

Postby sysp34 » Sun Jan 13, 2008 9:29 pm

[quote=Red Devil]
Yes I formatted exactly like that but it did not work, I have had this in the past with other sheets, the only way I could fix them was to rebuild them from scratch.
[/quote]

RD

what kind formula to get result in column Rpts (col. V) ?
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Postby Red Devil » Sun Jan 13, 2008 9:35 pm

I "pinched" it from Carbob's sheet

=SUMPRODUCT(--(B2:L2=B3:L3))

As I said in an earlier reply to Bob, when you use the help file in Excel it just shows:

=SUMPRODUCT(Array1,Array2) however Bob's introduction of the -- and = makes the function look for repeats. What I would like to know is what does the -- do?
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby sysp34 » Sun Jan 13, 2008 9:42 pm

[quote=Red Devil]
I "pinched" it from Carbob's sheet

=SUMPRODUCT(--(B2:L2=B3:L3))

As I said in an earlier reply to Bob, when you use the help file in Excel it just shows:

=SUMPRODUCT(Array1,Array2) however Bob's introduction of the -- and = makes the function look for repeats. What I would like to know is what does the -- do?
[/quote]

well i dunno if this help, a short review i think -- to eliminate error value

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Postby Red Devil » Sun Jan 13, 2008 9:47 pm

Thanks,

I'll go and have a look
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby CARBOB » Sun Jan 13, 2008 11:48 pm

[quote=Red Devil]
I "pinched" it from Carbob's sheet

=SUMPRODUCT(--(B2:L2=B3:L3))

As I said in an earlier reply to Bob, when you use the help file in Excel it just shows:

=SUMPRODUCT(Array1,Array2) however Bob's introduction of the -- and = makes the function look for repeats. What I would like to know is what does the -- do?
[/quote]

RD, I asked this same question on WWW.Mrexcel.com, this is the response I got.

ANSWER........

It's called a double unary minus. Minus minus is the same as plus.
It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved
by multiplying by 1 or adding zero. But -- is in vogue at the moment because
apparently it is a nanosecond or two quicker than the alternatives.

It would more likely be written:

=SUMPRODUCT(($B$2:$B$8=F2)*($D$2:$D$8="X"))

It compares each cell in B2:B8 with F2 and returns an array of TRUE/FALSE values.
Then it compares each cell in D2:D8 with "X" and returns another (equally sized) array
of TRUE/FALSE values. The 2 arrays are multiplied together, and in the process TRUE is
coerced to 1 and FALSE to zero. That results in an array of 1/0 values
(1*1=1, 1*0=0, 0*1=0, 0*0=0). This array is then summed. The effect is a
count of the two conditions being TRUE.
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby Red Devil » Sun Jan 13, 2008 11:54 pm

Ragwi,

Thanks for the link, very informative and I have used links from that page to find further info. I already have some links for Excel so I've added this page to my bookmarks.

Sorry, I have another question for you if you don't mind

I can use the MATCH function to find the last occurrence in a list of numbers do you know how to find the previous match and maybe the one before that.
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby CARBOB » Mon Jan 14, 2008 12:10 am

[quote=Red Devil]
Ragwi,

Thanks for the link, very informative and I have used links from that page to find further info. I already have some links for Excel so I've added this page to my bookmarks.

Sorry, I have another question for you if you don't mind

I can use the MATCH function to find the last occurrence in a list of numbers do you know how to find the previous match and maybe the one before that.
[/quote]

RD, the formulas for the skips show current skip, then the next skip, etc,etc,etc in my worksheet in columns AO34&AP34
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby sysp34 » Mon Jan 14, 2008 12:13 am

[quote=Red Devil]
Ragwi,

Thanks for the link, very informative and I have used links from that page to find further info. I already have some links for Excel so I've added this page to my bookmarks.

Sorry, I have another question for you if you don't mind

I can use the MATCH function to find the last occurrence in a list of numbers do you know how to find the previous match and maybe the one before that.
[/quote]

RD

i think CARBOB spreadsheet shown excellent skip history ;-) also i'm forget some thread link that shown little bit information (contains spreadsheet by EL members) about skip history like the nature of WNH-SEGMENT

gl
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Postby Red Devil » Mon Jan 14, 2008 12:37 am

[quote=CARBOB]
[quote=Red Devil]
Ragwi,

Thanks for the link, very informative and I have used links from that page to find further info. I already have some links for Excel so I've added this page to my bookmarks.

Sorry, I have another question for you if you don't mind

I can use the MATCH function to find the last occurrence in a list of numbers do you know how to find the previous match and maybe the one before that.
[/quote]

RD, the formulas for the skips show current skip, then the next skip, etc,etc,etc in my worksheet in columns AO34&AP34
[/quote]

OK,

Thanks I'll go and have another look, it's been a good night for Excel tuition thanks to you and Ragwi
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby Red Devil » Mon Jan 14, 2008 12:57 am

[quote=CARBOB]
[quote=Red Devil]
Carbob,

Yes it's strange, I find sometimes that the match function in Excel misses or disregards some matches but, normally it is due to the column that you are referring to is the result of another function or formula, but in this case you can see that I have actually inputted the "value" manually. A friend of mine who is a bit of a whizz with Excel says that he has seen this before and has no answer for it??? :wand:

Sysp34 you are a bit of an Excel whizz, have you any ideas??

Red Devil
[/quote]

He's a lot more knowledgeable than I!!! I've attached the spreadsheet I use to track Segment filters. Feel free to ask questions.
[/quote]

Bob,

Any chance you walk me through this statement from your sheet, I am lost, I get so far and lose myself

{=IF(COLUMNS($AO7:AQ7)<=COUNTIF($AF$15:$AF$1506,$AJ7),SMALL(IF($AF$15:$AF$1506=$AJ7,ROW($AF$15:$AF$1506)-ROW($AF$15)+1),COLUMNS($AO7:AQ7))-SUM($AO7:AP7)-1,"")}
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby CARBOB » Mon Jan 14, 2008 2:33 am

[quote=Red Devil]
[quote=CARBOB]
[quote=Red Devil]
Carbob,

Yes it's strange, I find sometimes that the match function in Excel misses or disregards some matches but, normally it is due to the column that you are referring to is the result of another function or formula, but in this case you can see that I have actually inputted the "value" manually. A friend of mine who is a bit of a whizz with Excel says that he has seen this before and has no answer for it??? :wand:

Sysp34 you are a bit of an Excel whizz, have you any ideas??

Red Devil
[/quote]

He's a lot more knowledgeable than I!!! I've attached the spreadsheet I use to track Segment filters. Feel free to ask questions.
[/quote]

Bob,

Any chance you walk me through this statement from your sheet, I am lost, I get so far and lose myself

{=IF(COLUMNS($AO7:AQ7)<=COUNTIF($AF$15:$AF$1506,$AJ7),SMALL(IF($AF$15:$AF$1506=$AJ7,ROW($AF$15:$AF$1506)-ROW($AF$15)+1),COLUMNS($AO7:AQ7))-SUM($AO7:AP7)-1,"")}
[/quote]

The formula is counting the number of skips starting at current draw. It then adds the previous skip or skips to find the next skip. In this case the number 8. Look at column AF, 12 is current draw, 8 is 1 draw back, first skip; 8 is 2 draws from 1st 8, second skip; 8 is 7 draws from the 2nd skip, 3rd skip. The -SUM($AO7:AP7)-1,"") is taking the 1st skip + the 2nd skip to get the 3rd skip. It counts the skips in-between the hits
ROW COL AF
15 12 8 1 2 7
16 8 9 11 2 10
17 7 7 2 25 3
18 8 10 5 2 5
19 11
20 10
21 11
22 10
23 5
24 6
25 8

If this confuses you even more, my apology. I will break the formula down step-by-step tommorrow.
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby Red Devil » Mon Jan 14, 2008 8:29 pm

Bob,

I'd appreciate that, I can use most functions on their own and mostly in standard form and then some in compound statements however, this is quite hard to follow. I think I've got it but better to be sure :vogel:
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Previous

Return to Tips + Tricks

Who is online

Users browsing this forum: No registered users and 3 guests

cron