Mode Function with 2 Criteria
 

I have a worksheet that has part number and customer name and the price
paid on each invoice for the last 4 years. One customer may have paid
multiple prices in the last 4 years. I would like to create a formula
to calculate the mode of the price but only if the vendor and part
number are the same. I have tried using
{=if(vendersheet1=vendorsheet2,if(partnumbersheet1 =partnumbersheet2,mode(pricesheet2),0),0)}.
But I just get zero for an answer. I have also tried mode with a match
statement inside but I only get the mode for the whole worksheet. Any
help would be greatly appreciated!

:confused:


--
bbrowers
------------------------------------------------------------------------
bbrowers's Profile: http://www.excelforum.com/member.php...o&userid=18887
View this thread: http://www.excelforum.com/showthread...hreadid=380089

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MODE(IF((VendorRange="Vendor")*(PartNumRange="Par tNum"),PriceRange))

If parts numbers are numerical values and not text, remove the quotes.

Hope this helps!

bbrowers Wrote:
I have a worksheet that has part number and customer name and the price
paid on each invoice for the last 4 years. One customer may have paid
multiple prices in the last 4 years. I would like to create a formula
to calculate the mode of the price but only if the vendor and part
number are the same. I have tried using
{=if(vendersheet1=vendorsheet2,if(partnumbersheet1 =partnumbersheet2,mode(pricesheet2),0),0)}.
But I just get zero for an answer. I have also tried mode with a match
statement inside but I only get the mode for the whole worksheet. Any
help would be greatly appreciated!

:confused:


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=380089