Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AB or BA
Need a way of selecting the smallest stock glass size needed. Columns A and B
represent the stock glass sizes (A4:B4=16x20). Here is the trick my work worder could be either 20x16 or 16x20 or any size at all. So I need it to be able compare both dimensions. I also have macro buttons for each glass type that my customer might select so if I press the RG button I want it to run through the sizes, selecting the smallest possible piece "Sheet 1" E15 E16 are where the work order dimensions are "Sheet 2" A B C E G I K RG: CC: MG: CN: RN: 4 16 20 $1.73 $3.16 $23.00 $6.43 $3.47 5 18 24 $4.28 $4.69 6 20 24 $4.85 $5.32 7 22 28 $3.32 $6.06 $12.33 $6.65 8 24 30 $3.99 $7.28 $7.98 9 24 36 $4.99 $9.09 $50.50 $18.49 $9.98 10 26 32 $4.43 $8.08 $8.87 11 32 40 $6.65 $12.13 $24.65 $13.30 12 36 48 $9.97 $18.19 $101.00 $36.98 $19.95 13 40 60 $16.53 $44.95 $167.00 $29.60 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AB or BA
Array enter (enter using Ctrl-Shift-Enter) the formula
=MIN(IF((A4:A13=MIN(E15:E16))*(B4:B13=MAX(E15:E1 6)),ROW(A4:A13))) The formula will return the row number of your smallest stock size. HTH, Bernie MS Excel MVP "fail2excel" wrote in message ... Need a way of selecting the smallest stock glass size needed. Columns A and B represent the stock glass sizes (A4:B4=16x20). Here is the trick my work worder could be either 20x16 or 16x20 or any size at all. So I need it to be able compare both dimensions. I also have macro buttons for each glass type that my customer might select so if I press the RG button I want it to run through the sizes, selecting the smallest possible piece "Sheet 1" E15 E16 are where the work order dimensions are "Sheet 2" A B C E G I K RG: CC: MG: CN: RN: 4 16 20 $1.73 $3.16 $23.00 $6.43 $3.47 5 18 24 $4.28 $4.69 6 20 24 $4.85 $5.32 7 22 28 $3.32 $6.06 $12.33 $6.65 8 24 30 $3.99 $7.28 $7.98 9 24 36 $4.99 $9.09 $50.50 $18.49 $9.98 10 26 32 $4.43 $8.08 $8.87 11 32 40 $6.65 $12.13 $24.65 $13.30 12 36 48 $9.97 $18.19 $101.00 $36.98 $19.95 13 40 60 $16.53 $44.95 $167.00 $29.60 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|