|
Formula for hiest rate
Hi,
i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
something like this
=IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
Dear Joel,
you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
Since your table range is fixed you need to add dollar signs like below. I
still don't know what was wrong with my formula or what is in column J. My formula used (A$2:A$13=A2) which create a one when the mcode matched and zero when the mcode didn't match. Multiplying by zero gave a zero results for all items that didn't have the same mcode and gave the price for the items (multiply by 1) for the items that hasd the same mcode. Then got the Max values of the items that were multiplied by one. =IF(A5=J5,MAX(H$2:H$5000),"") "vlook fomula" wrote: Dear Joel, you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
Dear Joel,
i have applied doll sign with my formula but result is same, and i also could not found result with your formula, kindly advise its importent to me, result of your given formula is m code description Plant UOM Price m code Hiest Price 101 Pen k1 EA 50.00 101 50 101 Pen k2 EA 30.00 101 Pen k3 EA 25.00 - 102 Pen k1 EA 8.03 102 8.03 102 Reel k2 EA 53.01 - 102 Reel k3 EA 58.06 - 103 Rubber k1 EA 55.16 103 55.16 103 Rubber k2 EA 81.82 - 103 Rubber k3 EA 81.80 - 104 Scale k1 EA 81.80 104 81.8 104 Scale k2 EA 45.75 - 104 Scale k3 EA 45.70 ..=IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") Kind Regards "Joel" wrote: Since your table range is fixed you need to add dollar signs like below. I still don't know what was wrong with my formula or what is in column J. My formula used (A$2:A$13=A2) which create a one when the mcode matched and zero when the mcode didn't match. Multiplying by zero gave a zero results for all items that didn't have the same mcode and gave the price for the items (multiply by 1) for the items that hasd the same mcode. Then got the Max values of the items that were multiplied by one. =IF(A5=J5,MAX(H$2:H$5000),"") "vlook fomula" wrote: Dear Joel, you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
It needs to be an array formula. Enter formula and then type
Shift-Cntl-Enter to get the brackets around the formula. Don't type the curly brackets. {=IF(MAX((A$2:A$5000=A2)*E$2:E$5000)=E2,E2,"")} Where column A is the mcode and column E is the dollar amount. Change columns as required. "vlook fomula" wrote: Dear Joel, i have applied doll sign with my formula but result is same, and i also could not found result with your formula, kindly advise its importent to me, result of your given formula is m code description Plant UOM Price m code Hiest Price 101 Pen k1 EA 50.00 101 50 101 Pen k2 EA 30.00 101 Pen k3 EA 25.00 - 102 Pen k1 EA 8.03 102 8.03 102 Reel k2 EA 53.01 - 102 Reel k3 EA 58.06 - 103 Rubber k1 EA 55.16 103 55.16 103 Rubber k2 EA 81.82 - 103 Rubber k3 EA 81.80 - 104 Scale k1 EA 81.80 104 81.8 104 Scale k2 EA 45.75 - 104 Scale k3 EA 45.70 .=IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") Kind Regards "Joel" wrote: Since your table range is fixed you need to add dollar signs like below. I still don't know what was wrong with my formula or what is in column J. My formula used (A$2:A$13=A2) which create a one when the mcode matched and zero when the mcode didn't match. Multiplying by zero gave a zero results for all items that didn't have the same mcode and gave the price for the items (multiply by 1) for the items that hasd the same mcode. Then got the Max values of the items that were multiplied by one. =IF(A5=J5,MAX(H$2:H$5000),"") "vlook fomula" wrote: Dear Joel, you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
Dear Joel,
i am still waitng for correct formula, please help me, i just need the highest value against the specifit number. shown in example. "Joel" wrote: It needs to be an array formula. Enter formula and then type Shift-Cntl-Enter to get the brackets around the formula. Don't type the curly brackets. {=IF(MAX((A$2:A$5000=A2)*E$2:E$5000)=E2,E2,"")} Where column A is the mcode and column E is the dollar amount. Change columns as required. "vlook fomula" wrote: Dear Joel, i have applied doll sign with my formula but result is same, and i also could not found result with your formula, kindly advise its importent to me, result of your given formula is m code description Plant UOM Price m code Hiest Price 101 Pen k1 EA 50.00 101 50 101 Pen k2 EA 30.00 101 Pen k3 EA 25.00 - 102 Pen k1 EA 8.03 102 8.03 102 Reel k2 EA 53.01 - 102 Reel k3 EA 58.06 - 103 Rubber k1 EA 55.16 103 55.16 103 Rubber k2 EA 81.82 - 103 Rubber k3 EA 81.80 - 104 Scale k1 EA 81.80 104 81.8 104 Scale k2 EA 45.75 - 104 Scale k3 EA 45.70 .=IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") Kind Regards "Joel" wrote: Since your table range is fixed you need to add dollar signs like below. I still don't know what was wrong with my formula or what is in column J. My formula used (A$2:A$13=A2) which create a one when the mcode matched and zero when the mcode didn't match. Multiplying by zero gave a zero results for all items that didn't have the same mcode and gave the price for the items (multiply by 1) for the items that hasd the same mcode. Then got the Max values of the items that were multiplied by one. =IF(A5=J5,MAX(H$2:H$5000),"") "vlook fomula" wrote: Dear Joel, you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
Formula for hiest rate
What is wrong with my response from 7/28/09 at 6:23 PST?
"vlook fomula" wrote: Dear Joel, i am still waitng for correct formula, please help me, i just need the highest value against the specifit number. shown in example. "Joel" wrote: It needs to be an array formula. Enter formula and then type Shift-Cntl-Enter to get the brackets around the formula. Don't type the curly brackets. {=IF(MAX((A$2:A$5000=A2)*E$2:E$5000)=E2,E2,"")} Where column A is the mcode and column E is the dollar amount. Change columns as required. "vlook fomula" wrote: Dear Joel, i have applied doll sign with my formula but result is same, and i also could not found result with your formula, kindly advise its importent to me, result of your given formula is m code description Plant UOM Price m code Hiest Price 101 Pen k1 EA 50.00 101 50 101 Pen k2 EA 30.00 101 Pen k3 EA 25.00 - 102 Pen k1 EA 8.03 102 8.03 102 Reel k2 EA 53.01 - 102 Reel k3 EA 58.06 - 103 Rubber k1 EA 55.16 103 55.16 103 Rubber k2 EA 81.82 - 103 Rubber k3 EA 81.80 - 104 Scale k1 EA 81.80 104 81.8 104 Scale k2 EA 45.75 - 104 Scale k3 EA 45.70 .=IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") Kind Regards "Joel" wrote: Since your table range is fixed you need to add dollar signs like below. I still don't know what was wrong with my formula or what is in column J. My formula used (A$2:A$13=A2) which create a one when the mcode matched and zero when the mcode didn't match. Multiplying by zero gave a zero results for all items that didn't have the same mcode and gave the price for the items (multiply by 1) for the items that hasd the same mcode. Then got the Max values of the items that were multiplied by one. =IF(A5=J5,MAX(H$2:H$5000),"") "vlook fomula" wrote: Dear Joel, you have got my point, but when i applied this formula it has result for E2 but i neet hiest price of material code that have more than 1 diff prices. i also used following formula =IF(A5=J5,MAX(H2:H5000),"") but it gave me highet price of the given range not spesific material regards "Joel" wrote: something like this =IF(MAX((A$2:A$13=A2)*E$2:E$13)=E2,E2,"") I assumed you had a header row and the data started in row 2. Put this formula in G2 and copy down the column. Change the last row 13 to match the last row in your table. "vlook fomula" wrote: Hi, i need help regading formula to find out hiest Price of the material Code if we have 5000 nos of material code and every code have 3 diff prices. M. code description Plant UOM Price M.code Hieghest Price 101 Pen k1 EA 8.05 101 Pen k2 EA 9.00 101 ?? 101 Pen k3 EA 8.05 101 Pen k4 EA 8.03 101 ?? 102 Reel k1 EA 53.01 102 Reel k2 EA 58.06 102 Reel k3 EA 55.16 102 ?? 103 Rubber k1 EA 81.82 103 Rubber k2 EA 84.00 103 Rubber k3 EA 81.80 103 ?? 104 Scale k1 EA 45.75 104 Scale k2 EA 45.70 104 ?? regards Zafar |
| All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com