Help Me PLEASE !!!!

X

xtreme barton

ok i have to get this figured out by monday (crossing fingers)...

what i need to accomplish is this.. an im a beginner at excel.
somebody reccomended me to it but doesnt know how to use it.


Example:
column --> A.....B.....C......D..... E......F

row1 01 23 33 44 51 12
row2 55 32 27 17 42 20
row3 01 29 47 18 51 12

if i place double digit numbers under each column. and repeating this
about 300 times.. is there a way to tell say how many times the number
01 shows up in column A? also to figure out the rest of the columns ??


what about it telling you which number shows up the most ?
 
A

Art

I'm not 100% sure of what you want, but here goes.

Assume that your numbers begin in column B and are in the first 1,000 rows.
Further that your numbers are 00 - 99.

Below row 1,000 in Column A, put the numbers 00 -- 99. That might be in
A1002:A1102 for example.

B1002: =COUNTIF(B$1:B$1000,$A1002)
B1003: =COUNTIF(B$1:B$1000,$A1003)
C1002: =COUNTIF(C$1:C$1000,$A1002)

and so on. This should give you the counts you want.

Now, to get a number with the highest count, put this in B1104:
=OFFSET(A1002,MATCH(MAX(B1002:B1102),B1002:B1102)-1,0)

Repeat this for the other columns.

Keep in mind there may be more than one number that shows up the most.

Art
 
D

David McRitchie

Hi Barton,
You can use COUNTIF look it up in your HELP file, John Walkenbach
has a nice page on this and similar functions.
Excel Developer Tip: Summing and Counting Using Multiple Criteria
http://www.j-walk.com/ss/excel/tips/tip74.htm

You can use another column for the count
G1: =COUNTIF(A:A,A1)
You can fill down using the fill handle or Ctrl+D with a selected range, see
http://www.mvps.org/dmcritchie/excel/fillhand.htm

The function will not care if 01 is text or number it will be treated as a
number.

If you want to make a list of specific values you can code below your
range.

for just one value -- not set up to use fill handle
A31: =COUNTIF(A$1:OFFSET(A31,-1,0),1)

Next time you post a new question, please use a subject that
defines the nature of the question as close as you can.
 
D

David McRitchie

Hi Barton,
I missed that you had a second part which Art noted and gave you
as solution. Since my solution is a bit different in adding columns
instead of rows to the spreadsheet, I'll supply the second part as well
to find the value that has the maximum occurence in case of a tie
the first one will be shown.

If you used the formulas down another column as in my solution,
the formula after the last of those formulas in column F might be

F7: =INDEX(A$1:OFFSET(F7,-1,0),MATCH(MAX(F$1:OFFSET(F7,-1,0)),$F1:OFFSET(F7,-1,0),0))

which allows you to insert more rows above F7, conceptually it is modified from
F7: =INDEX(A1:A6,MATCH(MAX(F1:F6),F1:F6,0))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

David McRitchie said:
Hi Barton,
You can use COUNTIF look it up in your HELP file, John Walkenbach
has a nice page on this and similar functions.
Excel Developer Tip: Summing and Counting Using Multiple Criteria
http://www.j-walk.com/ss/excel/tips/tip74.htm

You can use another column for the count
G1: =COUNTIF(A:A,A1)
You can fill down using the fill handle or Ctrl+D with a selected range, see
http://www.mvps.org/dmcritchie/excel/fillhand.htm

The function will not care if 01 is text or number it will be treated as a
number.

If you want to make a list of specific values you can code below your
range.

for just one value -- not set up to use fill handle
A31: =COUNTIF(A$1:OFFSET(A31,-1,0),1)

Next time you post a new question, please use a subject that
defines the nature of the question as close as you can.
Click to expand...
 
X

xtreme barton

sorry guys. i used excel about 2 years ago and it was all beginner
stuff. i havent used it since so its all new to me.

and yes what i was trying to figure out was what number appeared the
most in column A.

it seems i need to read alot before i can even attempt this simple
task cause it is all to much for me. i have no idea where to begin.
 
D

David McRitchie

Hi Barton,
I have emailed you an example in HTML format, which would
not be appreciated if posted to text only newsgroups.

You do not necessarily have to understand something to use it.

I did not include the equivalent OFFSET formulas, but they would
make the sheet work better if you inserted rows immediately above
the row with INDEX and MATCH formula.

As indicated in the email :
Everybody started as a beginner.
Having access to newsgroups and websites you have a distinct advantage.

You received answers that should work so what are you having difficulty with.

Please read about the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Help with OFFSET and the need for it can be found at
http://www.mvps.org/dmcritchie/excel/offset.htm
 
Top