Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
formula to determine the first column containing any data | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions | |||
Modify Row based upon cell value | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions |