test if data fits between two columns

Robert H

unread,

Mar 27, 2007, 10:01:08 AM3/27/07

to

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

Don Guillett

unread,

Mar 27, 2007, 10:39:29 AM3/27/07

to

try
=match(g2,ll3:ul3)
then adjust with -1 or -2 or ?

--
Don Guillett
SalesAid Software
dguil...@austin.rr.com
"Robert H" <robert....@l-3com.com> wrote in message
news:1175004068.5...@l77g2000hsb.googlegroups.com...

meatshield

unread,

Mar 27, 2007, 10:43:47 AM3/27/07

to

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.

Pete_UK

unread,

Mar 27, 2007, 10:47:11 AM3/27/07

to

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

Pete_UK

unread,

Mar 27, 2007, 11:23:21 AM3/27/07

to

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

Pete

> > Robert- Hide quoted text -
>
> - Show quoted text -

Robert H

unread,

Mar 27, 2007, 2:14:10 PM3/27/07

to

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.

Message has been deleted

Robert H

unread,

Mar 27, 2007, 3:54:20 PM3/27/07

to

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

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

and it works fine

Robert H

unread,

Mar 27, 2007, 3:58:45 PM3/27/07

to

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

Pete_UK

unread,

Mar 28, 2007, 4:41:50 AM3/28/07

to

You're welcome - thanks for feeding back.

Pete