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

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

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

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

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

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

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

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