Determine sums based on value possibilities
 

Ok heres the easiest way to explain this.

I have bundles with either 20 or 22 units in them.
They come off an assembly line 6 units of 22, then 3 units
of 20, then they repeat. It is sold to the custumer in 64
unit increments (2 of 22 and 1 of 20) so we measure the
weights by the 64 unit measurements. They weights
fluctuate slightly by 1-2 pounds. How can I determine by
placing 3 blind values in consecutive cells of a row, when
to convert and when not to convert automatically. I
believe I have 8 possible combinations so I can't use IF
statements. Should I use a table with the CHOOSE
statement? use LOOKUP? (which I can handle) or is there an
easier way? Suppose I have 3 weights of 75.2lbs,75.4lbs
and 84.3lbs. I need to convert one of the smaller weights
to a 22 unit equivilent then add them all together. If all
three were high, one would need down converted. Ideally an
operator could plug them in consective cells and the
formula would figure out how to up/down/leave alone the
correct values to achieve 2 large and one small. I didn't
mean to go this long but I wanted to be clear.

Thanks,
Mike

Since the weight of a bundle is not exactly predictable ("weights
fluctuate slightly by 1-2 pounds") you have far more than 8 combinations
if you are putting weights in the cells.

A straightford would be to use a single IF statement in each of 3 other
cells to decide whether the corresponding weight cells refer to 20 or 22
unit bundles. Then simply sum the total number of units.

Jerry

Mike K wrote:

Ok heres the easiest way to explain this.

I have bundles with either 20 or 22 units in them.
They come off an assembly line 6 units of 22, then 3 units
of 20, then they repeat. It is sold to the custumer in 64
unit increments (2 of 22 and 1 of 20) so we measure the
weights by the 64 unit measurements. They weights
fluctuate slightly by 1-2 pounds. How can I determine by
placing 3 blind values in consecutive cells of a row, when
to convert and when not to convert automatically. I
believe I have 8 possible combinations so I can't use IF
statements. Should I use a table with the CHOOSE
statement? use LOOKUP? (which I can handle) or is there an
easier way? Suppose I have 3 weights of 75.2lbs,75.4lbs
and 84.3lbs. I need to convert one of the smaller weights
to a 22 unit equivilent then add them all together. If all
three were high, one would need down converted. Ideally an
operator could plug them in consective cells and the
formula would figure out how to up/down/leave alone the
correct values to achieve 2 large and one small. I didn't
mean to go this long but I wanted to be clear.

Thanks,
Mike

Jerry,
I can reasonably assume if the weight is greater
than 79lbs, it has 22, less than 79, 20. Based on this I
can identify fairly accurately if they have 20 or 22 units.
I based my 8 possiblities from the chart below:

A B C
75 75 75 Conversion of 2 cells needed
75 75 84 Conversion of 1 cell needed
75 84 84 No conversion needed
75 84 75 Conversion of 1 cells needed
84 75 75 Conversion of 1 cells needed
84 84 75 No conversion needed
84 75 84 No conversion needed
84 84 84 Conversion of 1 cells needed

I can LOOK at a row and immediately tell what needs to be
done, but I can't get a formula to easily do it.

Mike

-----Original Message-----
Since the weight of a bundle is not exactly predictable
("weights
fluctuate slightly by 1-2 pounds") you have far more than
8 combinations
if you are putting weights in the cells.

A straightford would be to use a single IF statement in
each of 3 other
cells to decide whether the corresponding weight cells
refer to 20 or 22
unit bundles. Then simply sum the total number of units.

Jerry

Mike K wrote:

Ok heres the easiest way to explain this.

I have bundles with either 20 or 22 units in them.
They come off an assembly line 6 units of 22, then 3
units
of 20, then they repeat. It is sold to the custumer in
64
unit increments (2 of 22 and 1 of 20) so we measure the
weights by the 64 unit measurements. They weights
fluctuate slightly by 1-2 pounds. How can I determine
by
placing 3 blind values in consecutive cells of a row,
when
to convert and when not to convert automatically. I
believe I have 8 possible combinations so I can't use
IF
statements. Should I use a table with the CHOOSE
statement? use LOOKUP? (which I can handle) or is there
an
easier way? Suppose I have 3 weights of 75.2lbs,75.4lbs
and 84.3lbs. I need to convert one of the smaller
weights
to a 22 unit equivilent then add them all together. If
all
three were high, one would need down converted. Ideally
an
operator could plug them in consective cells and the
formula would figure out how to up/down/leave alone the
correct values to achieve 2 large and one small. I
didn't
mean to go this long but I wanted to be clear.

Thanks,
Mike

.

Mike, just a crack at it ..

Assuming you have

In Sheet1
-------------
in cols A to C, data from row2 down

75 75 75
75 75 84
75 84 84
75 84 75
84 75 75
84 84 75
84 75 84
84 84 84
etc

Put a label in D1: # of Cells requiring Conversion (say)

Put in D2:

=INDEX({2;1;0;1},MATCH(SUMPRODUCT(--ISNA(MATCH(A2:C2,{79},1))),{3;2;1;0},0))

Note: Above formula assumes that
weights = 79 lbs will have 22 units
(It's just a fine tweak of your specs, to include
what happens when the weight is exactly "equal to" 79 lbs)

Copy D2 down

Col D will return the desired results,
i.e. the number of cells requiring conversion
within cols A to C

For the sample data,
you'll get (results in col D):

75 75 75 2
75 75 84 1
75 84 84 0
75 84 75 1
84 75 75 1
84 84 75 0
84 75 84 0
84 84 84 1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Mike K" wrote in message
...
Jerry,
I can reasonably assume if the weight is greater
than 79lbs, it has 22, less than 79, 20. Based on this I
can identify fairly accurately if they have 20 or 22 units.
I based my 8 possiblities from the chart below:

A B C
75 75 75 Conversion of 2 cells needed
75 75 84 Conversion of 1 cell needed
75 84 84 No conversion needed
75 84 75 Conversion of 1 cells needed
84 75 75 Conversion of 1 cells needed
84 84 75 No conversion needed
84 75 84 No conversion needed
84 84 84 Conversion of 1 cells needed

I can LOOK at a row and immediately tell what needs to be
done, but I can't get a formula to easily do it.

Mike

-----Original Message-----
Since the weight of a bundle is not exactly predictable
("weights
fluctuate slightly by 1-2 pounds") you have far more than
8 combinations
if you are putting weights in the cells.

A straightford would be to use a single IF statement in
each of 3 other
cells to decide whether the corresponding weight cells
refer to 20 or 22
unit bundles. Then simply sum the total number of units.

Jerry

Mike K wrote:

Ok heres the easiest way to explain this.

I have bundles with either 20 or 22 units in them.
They come off an assembly line 6 units of 22, then 3
units
of 20, then they repeat. It is sold to the custumer in
64
unit increments (2 of 22 and 1 of 20) so we measure the
weights by the 64 unit measurements. They weights
fluctuate slightly by 1-2 pounds. How can I determine
by
placing 3 blind values in consecutive cells of a row,
when
to convert and when not to convert automatically. I
believe I have 8 possible combinations so I can't use
IF
statements. Should I use a table with the CHOOSE
statement? use LOOKUP? (which I can handle) or is there
an
easier way? Suppose I have 3 weights of 75.2lbs,75.4lbs
and 84.3lbs. I need to convert one of the smaller
weights
to a 22 unit equivilent then add them all together. If
all
three were high, one would need down converted. Ideally
an
operator could plug them in consective cells and the
formula would figure out how to up/down/leave alone the
correct values to achieve 2 large and one small. I
didn't
mean to go this long but I wanted to be clear.

Thanks,
Mike

Perhaps better to include some error traps ..

Put instead in D2:=20

=3DIF(COUNTBLANK(A2:C2)=3D3,"",IF(AND(ISNUMBER(A2) ,ISNUMBER
(B2),ISNUMBER(C2)),INDEX({2;1;0;1},MATCH(SUMPRODUC T(--ISNA
(MATCH(A2:C2,{79},1))),{3;2;1;0},0)),"Check Entries"))

Copy D2 down as many rows as data=20
is expected to be input in cols A to C
(can now copy down ahead for expected=20
data input in cols A to C)

Blank rows (in cols A to C) awaiting inputs=20
will return blanks: "" , while the alert phrase=20
"Check Entries" will appear for rows with inputs=20
where cols A to C do not contain only numbers
(helps to alert if text/text numbers were=20
inadvertently entered in cols A to C,=20
or should there be a missing number (input) in
either of cols A to C, etc)

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----

Perhaps better to include some error traps ..

Put instead in D2:

=IF(COUNTBLANK(A2:C2)=3,"",IF(AND(ISNUMBER(A2),ISN UMBER(B2),ISNUMBER(C2)),IN
DEX({2;1;0;1},MATCH(SUMPRODUCT(--ISNA(MATCH(A2:C2,{79},1))),{3;2;1;0},0)),"C
heck Entries"))

Copy D2 down as many rows as data
is expected to be input in cols A to C
(can now copy down ahead for expected
data input in cols A to C)

Blank rows (in cols A to C) awaiting inputs
will return blanks: "" , while the alert phrase
"Check Entries" will appear for rows with inputs
where cols A to C do not contain only numbers
(helps to alert if text/text numbers were
inadvertently entered in cols A to C,
or should there be a missing number (input) in
either of cols A to C, etc)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----