Welcome Guest
You last visited January 21, 2017, 2:30 am
All times shown are
Eastern Time (GMT-5:00)

# Excel FYI

Topic closed. 33 replies. Last post 11 years ago by hypersoniq.

 Page 1 of 3

United States
Member #5599
July 13, 2004
1192 Posts
Offline
 Posted: November 13, 2005, 9:16 am - IP Logged

If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

=SUMPRODUCT(--(\$A\$2:\$A\$9=G2),--(\$B\$2:\$B\$9=H2)) Microsoft Excel

 a b c d e f g h i 1 L1 L2 L3 L4 L5 COL L1 COL L2 NO. of Times 2 2 2 2 3 7 1 2 3 3 1 2 3 4 6 4 1 1 1 4 5 5 1 1 4 5 6 6 3 3 4 5 6 7 1 2 2 3 5 8 2 2 3 4 5 9 1 2 3 4 5

Using this, you can obvious find the most reoccurring pairs between columns in your data.

Cool!! Hope someone else finds this info as useful to them as it is to me.

You are a slave to the choices you have made.  jk

Even a blind squirrel will occasioanlly find an acorn.

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: November 13, 2005, 2:26 pm - IP Logged

Thanks for sharing

I am like a sponge when it comes to new excel formulas

I can find a use for any formula.

going to try inserting it into my spreadsheet now.

L ttaL   T

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: November 13, 2005, 2:34 pm - IP Logged

It works wonderfully!  Just incorporated it into my OK pick 3 sheet.

If you have any other excel FYI--please know that I would appreciate your posts.

L ttaL   T

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: November 14, 2005, 12:15 am - IP Logged

Thanks for sharing, this formula is very useful!

United States
Member #12618
March 18, 2005
2060 Posts
Offline
 Posted: November 14, 2005, 12:33 am - IP Logged

That's interesting JKing. Thanks. =)

Dance like no one is watching.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5972 Posts
Offline
 Posted: November 15, 2005, 7:43 pm - IP Logged

If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

=SUMPRODUCT(--(\$A\$2:\$A\$9=G2),--(\$B\$2:\$B\$9=H2)) Microsoft Excel

 a b c d e f g h i 1 L1 L2 L3 L4 L5 COL L1 COL L2 NO. of Times 2 2 2 2 3 7 1 2 3 3 1 2 3 4 6 4 1 1 1 4 5 5 1 1 4 5 6 6 3 3 4 5 6 7 1 2 2 3 5 8 2 2 3 4 5 9 1 2 3 4 5

Using this, you can obvious find the most reoccurring pairs between columns in your data.

Cool!! Hope someone else finds this info as useful to them as it is to me.

JKing,

Your formula with a slight variation.  I use the formula below to tell me how many times a digit has followed another digit. The first part of the equation going across, the second part down. Winsum and Sir Metro have both helped me a great deal. Many thanks to them.

Carbob

=SUMPRODUCT(--(\$G\$21:\$G\$3062=BS\$21),(--(\$G\$22:\$G\$3063=\$BR22)))

 1 2 3 4 5 1 110 124 121 130 119 2 127 111 118 124 114 3 125 128 112 121 140 4 121 111 147 107 112 5 120 121 128 116 135

BOSTON
United States
Member #48
September 9, 2001
3611 Posts
Offline
 Posted: November 15, 2005, 10:15 pm - IP Logged

question; in a pick 4 lottery if I entered the first 2 in column L1 and the last 2 in column L2 and did it as far back as the last 10 or 20 draws and entered your formula would i get the same resu lts to see what overdue pairs to play? thanks

BOSTON
United States
Member #48
September 9, 2001
3611 Posts
Offline
 Posted: November 15, 2005, 10:17 pm - IP Logged

P.S. can you give an example to me how you would do this formula as I would like to apply it to the pick 4 in Mass.

United States
Member #17555
June 22, 2005
5582 Posts
Offline
 Posted: November 16, 2005, 2:20 am - IP Logged

What does FYI stand for" thanx.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5972 Posts
Offline
 Posted: November 16, 2005, 5:58 am - IP Logged

Carbob

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: November 16, 2005, 7:01 am - IP Logged

If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

=SUMPRODUCT(--(\$A\$2:\$A\$9=G2),--(\$B\$2:\$B\$9=H2)) Microsoft Excel

 a b c d e f g h i 1 L1 L2 L3 L4 L5 COL L1 COL L2 NO. of Times 2 2 2 2 3 7 1 2 3 3 1 2 3 4 6 4 1 1 1 4 5 5 1 1 4 5 6 6 3 3 4 5 6 7 1 2 2 3 5 8 2 2 3 4 5 9 1 2 3 4 5

Using this, you can obvious find the most reoccurring pairs between columns in your data.

Cool!! Hope someone else finds this info as useful to them as it is to me.

JKing,

Your formula with a slight variation.  I use the formula below to tell me how many times a digit has followed another digit. The first part of the equation going across, the second part down. Winsum and Sir Metro have both helped me a great deal. Many thanks to them.

Carbob

=SUMPRODUCT(--(\$G\$21:\$G\$3062=BS\$21),(--(\$G\$22:\$G\$3063=\$BR22)))

 1 2 3 4 5 1 110 124 121 130 119 2 127 111 118 124 114 3 125 128 112 121 140 4 121 111 147 107 112 5 120 121 128 116 135

This is getting better & better by the day.

L ttaL   T

United States
Member #17555
June 22, 2005
5582 Posts
Offline
 Posted: November 16, 2005, 9:32 am - IP Logged

Thanx Carob.

United States
Member #5599
July 13, 2004
1192 Posts
Offline
 Posted: November 16, 2005, 11:45 am - IP Logged

Is anyone familiar with what the"--" is?

=SUMPRODUCT(--(\$A\$2:\$A\$9=G2),--(\$B\$2:\$B\$9=H2))

You are a slave to the choices you have made.  jk

Even a blind squirrel will occasioanlly find an acorn.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5972 Posts
Offline
 Posted: November 17, 2005, 11:02 am - IP Logged

Is anyone familiar with what the"--" is?

=SUMPRODUCT(--(\$A\$2:\$A\$9=G2),--(\$B\$2:\$B\$9=H2))

JKing,

Below is your answer. I asked the "old reliable boys" at Mrexcel.com, since I didn't know either. Now we both will have learned something.

Carbob

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.

United States
Member #5599
July 13, 2004
1192 Posts
Offline
 Posted: November 18, 2005, 8:32 am - IP Logged

Carbob...Thanks.  A well written and informative answer.

You are a slave to the choices you have made.  jk

Even a blind squirrel will occasioanlly find an acorn.

 Page 1 of 3