test if data fits between two columns

R

Robert H

have a table that I insert measured data into. Based on the value
of the measured data, a formula returns a value in an adjacent cell.
Usualy this would be easy for me just using a simple IF function But
my method creates too many ifs and I get an error:

=IF((G$2>=LL3)*(G$2<=UL3),3,IF((G$2>=LL4)*(G$2<=UL4),4,IF((G
$2>=LL5)*(G
$2<=UL5),5,IF((G$2>=LL6)*(G$2<=UL6),6,IF((G$2>=LL7)*(G$2<=UL7),7,IF((G
$2>=LL8)*(G$2<=UL8),8,IF((G$2>=LL9)*(G$2<=UL9),9,FALSE)))))))
It also just seems inefficient to next all those Ifs, and have to do
it for each cell of meaured data...

In this case the data to fill the cell comes from a small table:

WT LL UL
3 1.40 1.60
4 1.70 1.90
5 2.00 2.20
6 2.30 2.60
7 2.70 3.00
8 3.10 3.50
9 3.60 4.15
10 4.10 4.75
11 4.62 5.46
12 5.20 6.25
13 5.83 7.10
14 6.50 8.00
15 7.20 9.00

I neet a formula/Function etc to look through the LL and UL columns
nad when the value fits, return the adjacent WT.

So If I enter 2.8 as measured data the result in the target cell will
be "7".

Thanks
Robert
 
M

meatshield

have a table that I insert measured data into. Based on the value
of the measured data, a formula returns a value in an adjacent cell.
Usualy this would be easy for me just using a simple IF function But
my method creates too many ifs and I get an error:

=IF((G$2>=LL3)*(G$2<=UL3),3,IF((G$2>=LL4)*(G$2<=UL4),4,IF((G
$2>=LL5)*(G
$2<=UL5),5,IF((G$2>=LL6)*(G$2<=UL6),6,IF((G$2>=LL7)*(G$2<=UL7),7,IF((G
$2>=LL8)*(G$2<=UL8),8,IF((G$2>=LL9)*(G$2<=UL9),9,FALSE)))))))
It also just seems inefficient to next all those Ifs, and have to do
it for each cell of meaured data...

In this case the data to fill the cell comes from a small table:

WT LL UL
3 1.40 1.60
4 1.70 1.90
5 2.00 2.20
6 2.30 2.60
7 2.70 3.00
8 3.10 3.50
9 3.60 4.15
10 4.10 4.75
11 4.62 5.46
12 5.20 6.25
13 5.83 7.10
14 6.50 8.00
15 7.20 9.00

I neet a formula/Function etc to look through the LL and UL columns
nad when the value fits, return the adjacent WT.

So If I enter 2.8 as measured data the result in the target cell will
be "7".

Thanks
Robert
Click to expand...

I think you could use SUMPRODUCT to do this.. I assumed that you are
entering the data in G2, and the ranges your are searching are WT, LL,
and UL. You'll have to expand the ranges if you have more than 10
rows of data.
=SUMPRODUCT(--(LL1:LL10<=G2),--(UL1:UL10>=G2),(WT1:WT10))

However, I might be missing something - what do you want to happen
when the data doesn't fall in ranges you've specified? For example,
if you enter 1.65, what do you want the function to return?

I hope this helps.
 
P

Pete_UK

With your table occupying A3:C15, and using G2 to enter a value like
2.8, use this formula to return the corresponding weight:

=INDEX(A3:A15,MATCH(G3,B3:B15))

It does not check against the upper level, as beyond a weight of 9
your upper level overlaps the next low level - which would you return
if the value was 5.3 ? (the formula returns 12). This variation would
cope with values that are too large:

=IF(G2>MAX(C3:C15),"Too big",INDEX(A3:A15,MATCH(G3,B3:B15)))

Hope this helps.

Pete
 
P

Pete_UK

Sorry, the references to G3 should be to G2 in both formulae (if
that's where you put your data).

Pete
 
R

Robert H

MS, thanks for your help.

as far as your question goes. I will probably set up to round my
inputs so they always fall into the ranges. inputs that exceed the
overall min and max I will probably just have it flag them somehow.
 
R

Robert H

MS, I tried your recommendation and made the following adjustments:
(my data starts at WT3, LL3, UL3.

=SUMPRODUCT(--(LL3:LL15<=G2),--(UL3:UL15>=G2),(WT3:WT15))

It it returnd the same answer (4) requardless of what I enter. If I
use the Evaluatte Formula function I think I see were you are going;
the first two argements --(LL3:LL15<=G2),--(UL3:UL15>=G2), produce two
arrayed of true false conditions (1,0) that single out the row that
both forumula pass:
(1;1;0;0;0;0;0;0;0;0;0;0;0), -- (0;1;1;1;1;1;1;1;1;1;1;1;1), but
the third just take the values of the WT cells
(3;4;5;6;7;8;9;10;11;12;13;14;15). when all three are finaly summed I
always get 4. what I need is to have the corresponing WT displayed,
not added. I assume that is what you are shooting for and my
ignorance of SUMPRODUCT and arrays is hampering my ability to
understand :O
 
R

Robert H

MS I adusted your recommendation to (data starts at ##3)

=SUMPRODUCT(--(LL3:LL15<=G4),--(UL3:UL15>=G4),(WT3:WT15))

and it works fine
 
R

Robert H

Pete, I did not use your solution however thanks for pointing out the
overlap problem because it was an issue with the SUMPRODUCT solution I
used. I just eliminated the overlap which shouldent have been there
in the first place.
Thanks
 
Top