Normalize Range in a Stock Chart

[Mike Slattery]

I have a formula that produces an indicator called Center of Gravity. It looks like this:

=0.2*((252*BQ117)+(128*BQ118)+(64*BQ119)+(32*BQ120)+(16*BQ121)+(8*BQ122)+(4*BQ123)+(2*BQ124)+(1*BQ125))/SUM(BQ117:BQ125)

The first two digest in the formula, in this case "0.2*" can be any whole number.

I would like to "Normalize" the result so that the plot can utilize the same scale as the closing price of the stock. First closing price is in cell GN3.

The current output of the COG formula is:$11.12
The closing price at that same time is:17.04

I am sure there is a way to utilize the coefficient at the beginning of the formula "0.2*" and todays closing price to get the two number within a few digest of each other.

Can someone suggest a solution? Thanks Michael

 

[thisoldman]

You need more than just the two points to perform a normalization. The easiest formula to scale your values would be something like this:

Scaled_COG = COG * Max(Range_of_Closing_Prices) / Max(Range_of_COGs)

 

[Mike Slattery]

Thanks thisoldman:

I can do that quite easily.

Michael

 

[Mike Slattery]

HI TOM:

If I calculate the Max of COG's and then attempt to incorporate the output from that calc within my original COG calculation I get a circular ref error.

I was wondering, since each of the numbers in the first part of the formula are weighted

=1*((256*BQ117)+(128*BQ118)+(64*BQ119)+(32*BQ120)+(16*BQ121)+(8*BQ122)+(4*BQ123)+(2*BQ124)+(1*BQ125)) /

and the second part (after the division sign) of the formal are not,

/SUM(BQ117:BQ125)

would taking the sum of this second part of the formula and then multiplying it *(511) produce a result approaching the original stock price?

That seems to make sense, but it is not working at all.

I have also tried dividing that sum by 9 and then multiplying it by 511 and this also is not working. Nine is the number of day being examined by the formula.

Note:256+128+64+32+16+8+4+2+1=511

Thanks Michael

 

[thisoldman]

I hadn't contemplated that you would incorporate the scaling factor back into your original COG equation. I was thinking of creating a new column of data for a new series. Yes, the way you are using it is going to create a circular reference.

I don't have a feel for the differences between closing price and COG. You could eyeball the factor and include it as a constant. For example, if the current MAX(COG) is 11 and the current MAX(Closing_Price) is 17, your constant multiplier, now 0.2, becomes 0.2*17/11, or close enough to 0.3 to not matter.