Scaling Values through the Use of Averages

samcoarse

Board Regular
Joined
Jun 16, 2016
Messages
63
Hi,

I have a question regarding the best way to input a number and then through previous data available, scale corresponding averages to be relevant to the new input.

For example, here is some raw data. Column 3 is the per cent difference between the data in column 1 & 2. Column 5 is the per cent difference between the data in column 1 and 4. Column 6 is the market cap of a company when the stock was valued at the values in column 1.

123456
A0.340.3914.70%0.4532.35%23.45
B1.091.133.66%1.144.58%109.54
C2.783.018.27%2.842.15%233.95
D1.451.567.58%1.567.58%54.65

<tbody>
</tbody>

The below table shows the average of the values in columns 3, 5 and 6.

123
A8.55%11.66%105.39
B??14.34

<tbody>
</tbody>

is there a way to add in a value into row B (14.34) into the 2nd table, and then scale the averages in column 3 & 5 to match this new value?
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
If you have a series of numbers, n1, n2, n3, ... nk, whose average is A

Then, given a number B, the numbers (B/A)*n1, (B/A)*n2, (B/A)*n3, ..., (B/A)*nk will average to B.

Assuming that A is not 0.

I'm not exactly sure what you are looking for but I think that this would apply to your question.
 
Last edited:
Upvote 0
To clarify the question -

The average of column 3 (8.55%) is in A1 of the 2nd example table. The average of column 5 (11.66%) is in A2 and the average of column 6 (105.39) is in A3.

So, using these averages, I would like to scale the per cents to match a new input. In this example, 14.34.

My initial idea was this equation: ((1-(14.34/105.39))+1)*A1 for the value in A1 & the same for A2.

So the scaled value for 8.55% if 14.34 was entered would be 15.93%.

I don't understand what you mean by "scale the percents to match the input"
 
Upvote 0
I don't understand what you mean by "scale the percents to match the input"

Hi Mike,

Thanks for your response.

On average the difference between the data in column 1 and 2 is 8.55%, the average between the data in column 1 and 4 is 11.66%. However, the raw data shows the lower the value in column 6, the higher the values returned in columns 3 & 5. The 4 examples I have shown reflect a wider trend when there is 100 rows of data to look at.

So, as the difference between columns 1 & 2 and 1 & 4 is higher, the smaller the value in column 6, I am looking to find a way to scale the average per cent values found A1 and A2 in the 2nd example table based on a custom input, in this example the value in B3 in the 2nd example table, 14.34.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top