Remember Me?

#1
July 27th 19, 08:46 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2013 Posts: 26
Confusion on Sumproduct Calculation for arriving at a weighted Net Value

Hi,

I have a table comprising of Quantity Price & calorific heat value of Coal

Quantity is in Kg, Cost is in \$/Kg, Heat Value is in KiloCalories per Kg

Different Quantitites have different Prices and Different Kcal.

A sample table is appended below

A B C D E F G
QTY (Kg)(Kcal/kg) Rate \$/Kg cost \$ kcal \$ / Kcal \$/Kcal
500 4000 400 200000 2000000 0.1000 0.1000
500 3000 350 175000 1500000 0.1167 0.1167
500 2000 200 100000 1000000 0.1000 0.1000

1500 317 475000 4500000 0.1056 0.1056

Column E is the Heat value of the Quantity = Qty X Kcal/Kg

Column F is a parameter termed as \$/Kcal which is derived from dividing Column C data (\$/Kg) by Column B data (Kcal/Kg)

Column G is the same parameter as Column F i.e. \$/Kcal but derived from
dividing Column D (Cost in \$ = Column A X Column C) by Column E (Kcal of the Qty = Column A X Column B)

The net \$/Kcal of all the 3 quantities for Column F (0.1056) is calculated as Sumproduct( Individual \$/Kcal with Individual Qty) / Sum Qty

Then net \$/Kcal of the 3 Quantities for Column G (0.1056) is calculated as
Sum Cost \$ / Sum Kcal, i.e Summation of Col D / Summation of Col E

When the quantities are same both the Net \$/Kcal of Column F & Column G are equal. However when Quantities differ, these two values also differ, though the indivdual row \$/Kcal remains same Same is appended below

A B C D E F G
QTY (Kg)(Kcal/kg) Rate \$/Kg cost \$ kcal \$/Kcal \$/Kcal
2000 4000 400 800000 8000000 0.1000 0.1000
500 3000 350 175000 1500000 0.1167 0.1167
500 2000 200 100000 1000000 0.1000 0.1000

3000 358 1075000 10500000 0.1028 0.1024

QTY (Kg)(Kcal/kg) Rate \$/Kg cost \$ kcal \$/Kcal \$/Kcal
500 4000 400 200000 2000000 0.1000 0.1000
500 3000 350 175000 1500000 0.1167 0.1167
2000 2000 200 400000 4000000 0.1000 0.1000

3000 258 775000 7500000 0.1028 0.1033

Would like help on undersatnading Which Net \$/KCal is correct, Column F net or Column G Net and the reason for the same.

Thanks for the help.

San

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post kjdeutin New Users to Excel 0 May 7th 13 02:47 AM DP Excel Worksheet Functions 3 April 23rd 09 08:14 PM L Excel Discussion (Misc queries) 2 October 30th 08 12:58 PM MB51 Excel Discussion (Misc queries) 3 February 1st 07 07:39 PM UK-Graham Excel Discussion (Misc queries) 4 February 11th 05 05:32 PM

All times are GMT +1. The time now is 08:30 PM.