Welcome Guest
You last visited May 20, 2018, 1:27 pm
All times shown are
Eastern Time (GMT-5:00)

# can someone help me with formula for excel?

Topic closed. 13 replies. Last post 9 years ago by jester001.

 Page 1 of 1

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 9, 2009, 2:02 pm - IP Logged

hello sir/mam

can someone please help me for some excel formula. for example i have rows A1toA30 and i wanted to calculate the number of times that each number has appeared in the cell that i have selected. the numbers are from 0 to 9 and i just wanted to know how many times each number has appeared in the cells that i have mentioned.

also is it possible to calculate in excel the number of times a certain number is followed by a certain number for example in cell A1toA30 there are numbers ranging from 0 to 9 and in A30 the number 8 appeared i just wanted to know how many times each number from 0 to 9 followed from the vells A1toA30.

ex. from A1toA30 we can calculate this

a30 = 8

0 = 1

1 = 5

2 = 7

3 = 5

4 = 0

etc...

tnx for anyone who will kindly reply to my post also sorry for my english it is not my native language. tnx again in advance. :)

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: July 9, 2009, 3:38 pm - IP Logged

I believe what you need is something like:

=COUNTIF(A1...A30,"=0")    >change the "0" to what ever number you want to count<

That formula should be placed in each cell that you want the "counted" numbers to be in.

0=how many 0's

1=how many 1's

2=how many 2's

etc. thru 9

You can change the "range" (A1...A30) that you want to count.

Hope that's what you were looking for...

The only DUMB question is the one question you DID NOT ask...

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 9, 2009, 6:36 pm - IP Logged

ok sir tnx alot. will try it out. :) tnx again sir. :)

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6332 Posts
Offline
 Posted: July 9, 2009, 6:58 pm - IP Logged

I believe what you need is something like:

=COUNTIF(A1...A30,"=0")    >change the "0" to what ever number you want to count<

That formula should be placed in each cell that you want the "counted" numbers to be in.

0=how many 0's

1=how many 1's

2=how many 2's

etc. thru 9

You can change the "range" (A1...A30) that you want to count.

Hope that's what you were looking for...

If I understand what they are looking for a formula to count the number of times a digit has followed another digit. If so, this formula will work  =SUMPRODUCT(--(\$U\$7:\$U\$17=\$X7),(--(\$U\$8:\$U\$18=Y\$6)))

Make sure to use Ctrl-Shift-Enter to enter the formula.

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 10, 2009, 12:22 am - IP Logged

tnx alot carbob. :) can this be done individually for example only the numbers in the a coulmn are sorted then seperate for the b coulmn and c column? so there will be three of them tnx agan sir. :)

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 10, 2009, 12:54 am - IP Logged

hello again sir carbob i just have a question again can it also be done automatically for example we just put a number on a certain row and then it automatically updates the numbes? lso can this be done individually like in pick 3 we have p1 p2 p3 i wanted to analyze each one of them and their following numbers tnx again sir. :)

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6332 Posts
Offline
 Posted: July 10, 2009, 5:49 am - IP Logged

hello again sir carbob i just have a question again can it also be done automatically for example we just put a number on a certain row and then it automatically updates the numbes? lso can this be done individually like in pick 3 we have p1 p2 p3 i wanted to analyze each one of them and their following numbers tnx again sir. :)

It would take a macro to do that. The example I posted is for one column only. You created 2 more tables like the example, change the ranges, then you have all 3 positions. When you add a draw, do a a replace to current row.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: July 10, 2009, 8:37 am - IP Logged

Sorry, I misunderstood the question...

The only DUMB question is the one question you DID NOT ask...

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 10, 2009, 9:36 am - IP Logged

to knucklehead the one that you gave me worked. :) tnx alot.

carbob was teaching me the answer to my sexond question.

to carbob: ok tnx alot sir. will do just that.

tnx again to both of you for the help. :)

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 10, 2009, 12:15 pm - IP Logged

=SUMPRODUCT(--(\$B\$2:\$B\$2556=\$AB2),(--(\$B\$3:\$B\$2557=\$AC1)))

hello carbob? is the formula right? im getting only zero's tnx again. btw do you know how to convert it to macro? tnx again sir. :)

United States
Member #41846
June 23, 2006
480 Posts
Offline
 Posted: July 10, 2009, 9:40 pm - IP Logged

=SUMPRODUCT(--(\$B\$2:\$B\$2556=\$AB2),(--(\$B\$3:\$B\$2557=\$AC1)))

hello carbob? is the formula right? im getting only zero's tnx again. btw do you know how to convert it to macro? tnx again sir. :)

Did you remember to start with ctrl shift enter  to let excel know that it was an array formula?

Macro    on tool bar goto tools macro record new macro  use default name or enter one,  select cell you want result displayed in, enter your formula.   stop macro  you now have a macro you can use by pressing alt f8 and selecting

good luck

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 10, 2009, 11:26 pm - IP Logged

ok tnx alot sir will try it out. :) tnx again sir.

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
 Posted: July 11, 2009, 12:04 am - IP Logged

hello again sir i just did what you told me what i get are all zero's. :(