Values from a weighted average

[JK123]

Hi all,

I am new to the forum so hopefully this is posted in the correct place. I am trying to three derive values from a weighted average that I know. The below data starts in A1. I know the weighted average value of my data is 473.27, I need a formula to calculate the three missing values in C3-C5 and these three values must be of equal value. The three missing values are currently roughly 470 based on the weighted average of 473.27 but I need a formula as the 473.27 will change and I want those three missing values to move equally as the weighted average changes.

Volume Density Value
2,793.794 0.7393 482.48
2,656.384 0.7370
2,745.854 0.7345
2,546.092 0.7365


Weighted Average Value 473.27

Hopefully that description of the problem makes sense, some help would be appreciated.

Thank you in advance

 

[Andrew Poulsom]

Welcome to MrExcel.

How is the weighted average calculated. To solve your problem I think you would need the Volume and Density that applies to the weighted average.

 

[JK123]

The weighted average is a price based on an average quote of a commodity price for the month, as there are a few days remaining in the month value this will change as the market moves. As the first value (482.48) is priced in and fixed, I need an equal value across the remaining 3 volumes to be priced as the month closes. So as the month ends, the weighted average price will be the average price of the commodity for November. The 473.27 is for November as it stands today. I wanted a formula so as the weighted average moves until the month closes, the remaining three values to make up this weighted average will also move with it

 

[Andrew Poulsom]

As I said before I think you will need the Volume and Density that applies to the weighted average.

 

[JK123]

The volume and the density are in the above table, sorry if that wasn't clear; hopefully the below helps. Just need a formula for a value of the ones in bold that would then make that weighted average

Volume Density Value
2,793.794 0.7393 482.48
2,656.384 0.7370
2,745.854 0.7345
2,546.092 0.7365

Weighted Average Value 473.27

 

[Andrew Poulsom]

I understand what data you have. But I think you will need the Volume and Density that applies to the weighted average to make calculations of the missing Values. You still haven't said how the weighted average is calculated. If it isn't based on a list of Volumes, Densities and Values you won't be able to calculate the missing Values.