Excel 2000 lookup question

[vestax22]

Hi, I want to perform calculations on data based on the contents of two fields.


LOWER UPPER OCCUR.

IRON 65.299 99999.990 20
SILICA 2.849 3.651 20
MANGA 0.999 1.401 20
DEMI 0.000 23.001 13
IDEAL 72.999 99999.990 13
-1/4" 0.000 0.501 23
1/4AT" 94.499 99999.990 22
COMPRESSION 529.999 99999.000 20



The Lower and Upper columns contain the Max Min values that data inside another column could contain. Each field example Iron contains data for the month. I need a formula that will count the number entries that fall inside the Upper and Lower interval and places the result inside the OCCUR field.

Hope this is clear enough.
Thx

 

[fkegley]

I think I would use the Database functions to do this. Place all the columns involved into the same list.
Give each column a different column heading. Use Database functions to find MAX and MIN values of column of interest for each IRON, SILICA, etc. Once you get those built, use DCOUNT to count values in the column of interest for each set of IRON, SILICA, Max and Min values.


Frank kegley
fkegley@hotmail.com

 

[NeilRP]

Use the COUNTIF function in your occur column

eg =COUNTIF(nrLookup,">=" & B3) - COUNTIF(nrLookup,">" & C3)

where nrLookup is your lookup range
B3 contains your lower range
C3 contains your upper value

 

[KenWright]

Assuming your data is in the following range (Data in A2:A200 - Just not shown):-


A B C D E F
4 LOWER UPPER OCCUR.
5 IRON 65.299 99999.99 FORMULA
6 SILICA 2.849 3.651
7 MANGA 0.999 1.401
8 DEMI 0 23.001 13
9 IDEAL 72.999 99999.99
10 -1/4" 0 0.501
11 1/4AT" 94.499 99999.99
12 COMPRESSION 529.999 99999
13
14
15
16
17
18

FORMULA in F5 =:-

=SUMPRODUCT(($A$2:$A$200>=D5)*($A$2:$A$200<=E5))

Copy down to F12

Adjust references to suit

Regards
Ken.................


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[vestax22]

hey NeilRP

Thx for the reply. Your on the right track but i want to compare only the non blank cells to the interval.