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

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Robert H" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Mar 27, 10:01 am, "Robert H" <robert.hatc...@l-3com.com> wrote:
> 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

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.

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


On Mar 27, 3:01 pm, "Robert H" <robert.hatc...@l-3com.com> wrote:
> 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

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

Pete

On Mar 27, 3:47 pm, "Pete_UK" <pashu...@auditel.net> wrote:
> 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
>
> On Mar 27, 3:01 pm, "Robert H" <robert.hatc...@l-3com.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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.


On Mar 27, 9:43 am, "meatshield" <komeatshi...@yahoo.com> wrote:
> On Mar 27, 10:01 am, "Robert H" <robert.hatc...@l-3com.com> wrote:
>
>
>
> > 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
>
> 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.

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

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

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

and it works fine

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

You're welcome - thanks for feeding back.

Pete

On Mar 27, 8:58 pm, "Robert H" <robert.hatc...@l-3com.com> wrote:
> 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