Formula Question
 

I have 2 columns, column A for my quote # and column B for the quantity. What
I want to do is find the largest qty in column B for a particular quote # in
column A. The quote # will be listed more than once.

Column A Column B
12345 1000
12345 1500
12345 2000

So in column C I want to put a "1" where it finds the largest quantity for
that quote #. So in the above example there would be a "1" in column C, Row 3.

How would I write this formula?

Formula Question
 

You can use this array* formula

=IF(B1=MAX(IF(A:A=A1,B:B)),1,"")

Copy down column C as needed.
*Use Ctrl+Shift+Enter to confirm formula, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Secret Squirrel" wrote:

I have 2 columns, column A for my quote # and column B for the quantity. What
I want to do is find the largest qty in column B for a particular quote # in
column A. The quote # will be listed more than once.

Column A Column B
12345 1000
12345 1500
12345 2000

So in column C I want to put a "1" where it finds the largest quantity for
that quote #. So in the above example there would be a "1" in column C, Row 3.

How would I write this formula?

Formula Question
 

Try this array formula** :

=IF(B1=MAX(IF(A$1:A$5=A1,B$1:B$5)),1,"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I have 2 columns, column A for my quote # and column B for the quantity.
What
I want to do is find the largest qty in column B for a particular quote #
in
column A. The quote # will be listed more than once.

Column A Column B
12345 1000
12345 1500
12345 2000

So in column C I want to put a "1" where it finds the largest quantity for
that quote #. So in the above example there would be a "1" in column C,
Row 3.

How would I write this formula?