Question for Excel users.

Thread View: comp.apps.spreadsheets

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Kalpit Desai P265 Thu, 13 Aug 1992 13:22

#105

Author: Kalpit Desai P265
Date: Thu, 13 Aug 1992 13:22

31 lines
2195 bytes

I am new to Excel and have a problem. The problem is that I have 
data that is laid across rows of the spreadsheet. i.e

+=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|     | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R
+=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
| DA1 | 4 | 3 | 2 | 6 | 8 | 5 | 8 | 9 | 4 | 3 | 5 | 7 | 4 | 2 | 5 | 6 | 7 | 3
+=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
| DA2 | 3 | 4 | 2 | 3 | 4 | 5 | 7 | 9 | 8 | 9 | 4 | 9 | 0 | 3 | 2 | 5 | 7 | 3
+=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
| DA3 | 4 | 4 | 4 | 4 | 6 | 6 | 6 | 6 | 3 | 3 | 3 | 1 | 1 | 1 | 4 | 4 | 4 | 5
+=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==

What I need to know is how can I find out how many '5's I have in a
row, or how many '7's I have in a row. I tried DCOUNT but only seems to
work on columns and not rows. I tried to TRANSLATE the database and then
use the out put to do a DCOUNT. This method fails if the numbers in the
database change. 

What I need is a mechanism to count the number of occurances in a row of
a particular number. The HISTOGRAM plots the data nicely but what I want
is to put the count into another cell.

Appreciate any help.

-- 
+----------------+------------------------------+------------------------+
| Kalpit Desai   |          BNR Inc.,           |  Voice: (919) 991 7902 |
| kdesai@bnr.ca  |     Research Triangle Park   |  FAX:   (919) 991 7001 |
|                |  North Carolina 27709, U.S.A |  ESN:         294 7902 |
+----------------+------------------------------+------------------------+

Re: Question for Excel users.

#106

Author: Tim Tow
Date: Fri, 14 Aug 1992 01:22

40 lines
2859 bytes

In article <1992Aug13.132205.26624@bmerh85.bnr.ca>, kdesai@brtph490.NoSubdomain.NoDomain (Kalpit Desai P265) writes:
|> I am new to Excel and have a problem. The problem is that I have 
|> data that is laid across rows of the spreadsheet. i.e
|> 
|> +=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|> |     | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R
|> +=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|> | DA1 | 4 | 3 | 2 | 6 | 8 | 5 | 8 | 9 | 4 | 3 | 5 | 7 | 4 | 2 | 5 | 6 | 7 | 3
|> +=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|> | DA2 | 3 | 4 | 2 | 3 | 4 | 5 | 7 | 9 | 8 | 9 | 4 | 9 | 0 | 3 | 2 | 5 | 7 | 3
|> +=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|> | DA3 | 4 | 4 | 4 | 4 | 6 | 6 | 6 | 6 | 3 | 3 | 3 | 1 | 1 | 1 | 4 | 4 | 4 | 5
|> +=====+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+===+==
|> 
|> What I need to know is how can I find out how many '5's I have in a
|> row, or how many '7's I have in a row. I tried DCOUNT but only seems to
|> work on columns and not rows. I tried to TRANSLATE the database and then
|> use the out put to do a DCOUNT. This method fails if the numbers in the
|> database change. 

Use an array formula:

Assume your spreadsheet above shows the data from rows 1 to 3 and columns
a to r and temp is a named range where you store the number that you want
the distribution for.  Use an array formula;

=sum(if(A1:R1=temp,1,0)) and enter as an array formula ( Ctrl-Shift-Enter )

Then just plug the number you want counted into "temp" and it works!  

Boy is this stuff better than Lotus, Supercalc, 20/20 and all those other 
no name spreadsheets I've used.....

 ----------------------------------------------------------------------------
 Tim Tow                              |                                      
 Intergraph Corporation               | "Injustice anywhere is a             
 Huntsville, AL                       |      threat to justice everywhere"    
 Mailpath:..timtow@timtow.b1.ingr.com |                                      
 Voice:                  205-730-2117 |         - Dr Martin Luther King, Jr
 ----------------------------------------------------------------------------

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads