excel formula for fuel inventory sale with mix costs

This proposal assumes that you are still using the 2007 version of Excel and therefore includes some array entered formulas.
1. Copied transpose columns A and C into rows 6:7
2. Populated rows 4:5 (cost) using**: =IFERROR(INDEX($E$9:$E$19,SMALL(IF(J$9:J$19>0,ROW($E$9:$E$19)-ROW($E$8)),ROWS(J$4:J4))),"")
3. Populated rows 2:3 (percentage) using**: =IFERROR(INDEX(J$9:J$19,SMALL(IF(J$9:J$19>0,ROW($E$9:$E$19)-ROW($E$8)),ROWS(J$2:J2)))/J$7,"")
4. Populated row 1 (average cost) using: =SUMPRODUCT(J2:J3,J4:J5)
5. Populated rows 9:19 (portion of sale from purchase) using: =MIN(J$7-SUM(J$8:J8),$D9-SUM($I9:I9))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Note that any of the rows listed in steps 1:4 above may be hidden for aesthetic purposes.
Let us know if you have any questions.

@Fatekb
If you are using a later version of excel, please update your profile to reflect that. There may be other solutions available to you if you are using a later version of Excel.

Also: Please look at the banner at the top of this page on how to attach a sample file.

Yes, but the following will work without array entering:
For rows 4:5 =IFERROR(INDEX($E$9:$E$19,AGGREGATE(15,6,(ROW($E$9:$E$19)-ROW($E$8))/(J$9:J$19>0),ROWS(J$4:J4))),"")
For rows 2:3 =IFERROR(INDEX(J$9:J$19,AGGREGATE(15,6,(ROW($E$9:$E$19)-ROW($E$8))/(J$9:J$19>0),ROWS(J$2:J2)))/J$7,"")
Let us know if you have any questions.

Select cell J2 then drag the fill handle down to cell J3 and then, while J2:J3 are still selected drag the fill handle over to cell AN3.
Notice that the ROWS argument in J3 is now (J$2:J3) which will evaluate to 2 (The Evaluate Formula feature on the Formulas may be helpful in getting a better understanding).
Similarly select cell J4 then drag the fill handle down to cell J5 and then, while J4:J5 are still selected drag the fill handle over to cell AN5.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

I don't understand the difference between real $ cost and Av. Cost as shown in row 1.
Please tell us the expected value of real $ cost for column N and, unless the calculation is obvious, please include an explanation so that we have a better understanding.
Let us know if you have any questions.

A Happy Thanksgiving to you also.