# Scaling Values through the Use of Averages

#### samcoarse

##### Board Regular
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.

 1 2 3 4 5 6 A 0.34 0.39 14.70% 0.45 32.35% 23.45 B 1.09 1.13 3.66% 1.14 4.58% 109.54 C 2.78 3.01 8.27% 2.84 2.15% 233.95 D 1.45 1.56 7.58% 1.56 7.58% 54.65

<tbody>
</tbody>

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

 1 2 3 A 8.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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Bump

#### mikerickson

##### MrExcel MVP
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:

#### mikerickson

##### MrExcel MVP
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"

#### samcoarse

##### Board Regular
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.

Replies
1
Views
389
Replies
1
Views
446
Replies
7
Views
245
Replies
5
Views
599
Replies
11
Views
1K

1,191,025
Messages
5,984,198
Members
439,877
Latest member
kellylet

### 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?

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