=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
SalesAid Software
dguil...@austin.rr.com
"Robert H" <robert....@l-3com.com> wrote in message
news:1175004068.5...@l77g2000hsb.googlegroups.com...
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.
=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
> > Robert- Hide quoted text -
>
> - Show quoted text -
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.
=SUMPRODUCT(--(LL3:LL15<=G4),--(UL3:UL15>=G4),(WT3:WT15))
and it works fine
Pete