Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 27th 19, 08:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 24
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using SUMPRODUCT to find weighted averages kjdeutin New Users to Excel 0 May 7th 13 02:47 AM
SUMPRODUCT - Creating a weighted score in column with subtotals DP Excel Worksheet Functions 3 April 23rd 09 08:14 PM
Calculation confusion L Excel Discussion (Misc queries) 2 October 30th 08 12:58 PM
Weighted average using SUMIF and/or SUMPRODUCT MB51 Excel Discussion (Misc queries) 3 February 1st 07 07:39 PM
.xls email attachments are arriving at the recipient as .dat file. UK-Graham Excel Discussion (Misc queries) 4 February 11th 05 05:32 PM


All times are GMT +1. The time now is 01:02 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017