+ Reply to Thread
Results 1 to 2 of 2

Calculating Max-Mix for multiple part numbers

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Greenville, South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    1

    Calculating Max-Mix for multiple part numbers

    I have a list of 2012 invoices by part number and I need to calculate the difference between the highest price paid per part and the lowest price paid. I then need to multiple this difference to show a maximum potential savings if we were to purchase all parts at the lowest price (under the assumption we purchased all at the highest price). I have a way to do this using [=Max()-Min()] and then [=Count()*result of first formula]. The problem is that I have a list of 30,000 part numbers averaging 4-5 invoices each. Is there a way to use =SUMIF or some other function that will allow me to copy the formula all the way down the column, but only make the calculation on rows with matching part numbers? Once all the calculations are done, I will =SUM the entire "total potential savings" column to present our possible savings (we know why there is price variance and want to implement a plan to correct it and want to present how much we could possibly save by doing so)

    Here is a sample of my spread sheet currently
    \1
    My current formulas
    \1

    Thanks!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Calculating Max-Mix for multiple part numbers

    Hi,

    In cell G2 enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) and copy down:

    =MAX(IF($A$2:$A$5000=A2,$F$2:$F$5000))-MIN(IF($A$2:$A$5000=A2,$F$2:$F$5000))

    In cell H2 enter this formula (non-array) and copy down:

    =G2*COUNTIF($A$2:$A$5000,A2)

    I went up to 5000 rows here as an example but obviously change them as required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1