It looks so long you keep rows in in your pivot based on single column (Item, or ItemName only), the solution I provided will work anyway (you can calculate Average of ratio as ratio, Sum of qty as totalSMALL, Average of qty2 as resultBIG, and Average of qty3 as resultSMALL).



It will getting ugly, when you try add more columns to pivot rows (Item, item name, packSmall, packBig) - pivot interprets them as data groping fields, calculating subtotals for all of them. And there is nothing you can do about this - this is how pivot works.

One way to get all additional columns to be displayed, is to use an ODBC query instead of pivot table.

Another way is possible when you have a separate Items sheet, where all items are described (and which may serve as source for Data Validation List to select item in your tables) - item codes, item names, packSMALL, packBIG and Ratio. Then you can make all calculations in this Items table instead of pivot (using SUMIFS() to calculate totalSmall there, and calculating resultBIG and resultSMALL using your formulas).