Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / May 2008

Tip: Looking for answers? Try searching our database.

Filling in a table with changing range values

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bunky2000 - 28 May 2008 02:37 GMT
I am trying to fill in a table that needs to look at two different columns to
see whether to to count the data.  For example, here is some data:
       A       B
1     10       3
2     40       2.5
3     71       4
4     124     3.5

Now to create the table, I want to know how many times something occurs
between values looking at both conditions with column B data across the top
and column A data along the veritcal, as below:

                0-1   1.1-1.5   1.6-2   2.1-2.5   2.6-3  3.1-3.5   4 or more
0-40
41-80
81-120
121-160  

Any ideas?  I've tried erally long arrays but it's getting really comlicated
as I add more data.
Joel - 28 May 2008 12:04 GMT
Here is the data and formulas I used.  I put the results in a table located
at A10:A14.  I also changed the Left column and top row of the table to
include only the lower n umber of the range.

the formula works by looking at the left column and comparing the number in
the cell with the number at the left column and the number in the left column
in the next row.  Again in the top row it compares the cell with the number
in the top row and the number in the top row in the next column.

the formulas can be copied except in the last Row and the Last column which
are missing terms.

Table in A10:H14

    0    1    1.5    2    2.5    3    4
0    0    0    0    0    1    1    0
41    0    0    0    0    0    0    1
81    0    0    0    0    0    0    0
121    0    0    0    0    0    1    0

B11:B1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=B$10),--($B$1:$B$4<C$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=B$10),--($B$1:$B$4<C$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=B$10),--($B$1:$B$4<C$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=B$10),--($B$1:$B$4<C$10))

C11:c1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=C$10),--($B$1:$B$4<D$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=C$10),--($B$1:$B$4<D$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=C$10),--($B$1:$B$4<D$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=C$10),--($B$1:$B$4<D$10))

D11:D1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=D$10),--($B$1:$B$4<E$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=D$10),--($B$1:$B$4<E$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=D$10),--($B$1:$B$4<E$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=D$10),--($B$1:$B$4<E$10))

E11:E1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=E$10),--($B$1:$B$4<F$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=E$10),--($B$1:$B$4<F$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=E$10),--($B$1:$B$4<F$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=E$10),--($B$1:$B$4<F$10))

F11:f1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=F$10),--($B$1:$B$4<G$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=F$10),--($B$1:$B$4<G$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=F$10),--($B$1:$B$4<G$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=F$10),--($B$1:$B$4<G$10))

G11:G1
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=G$10),--($B$1:$B$4<H$10)
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=G$10),--($B$1:$B$4<H$10)
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=G$10),--($B$1:$B$4<H$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=G$10),--($B$1:$B$4<H$10))

H11:H14
=SUMPRODUCT(--($A$1:$A$4>=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4>=H$10))
=SUMPRODUCT(--($A$1:$A$4>=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4>=H$10))
=SUMPRODUCT(--($A$1:$A$4>=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4>=H$10))
=SUMPRODUCT(--($A$1:$A$4>=$A14),--($B$1:$B$4>=H$10))

> I am trying to fill in a table that needs to look at two different columns to
> see whether to to count the data.  For example, here is some data:
[quoted text clipped - 16 lines]
> Any ideas?  I've tried erally long arrays but it's getting really comlicated
> as I add more data.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.