Standardizing data

The workbook attached has two tables. The 1st table is labeled initial data and the second table standardized data.

In the second table, I standardize data in the first table by dividing each value with the average. The averages are regularly updated. The problem is that when the averages are negative values or zeros, I don’t agree with the results I get in the second table, especially when positive values in the initial table are expressed as negative values in the second table.

I hope forum mathematicians can advise me on how to standardize data.


Division.xlsx

Column D average is positive. ANY positive number divided by a positive number remains positive. ANY negative number divided by a positive number remains negative.

Column E average is negative. ANY negative number divided by a positive number becomes negative. ANY negative number divided by a negative number becomes positive.

The sum (and average) of column E is zero. So you get a #DIV/0 error.

You say: "I don’t agree with the results", they are mathematically correct. If you are EXPECTING something different, then you are using the wrong formula. So, what results do you EXPECT to see in column K (in numbers, not words) ?

I want Positive values in the first table to remain positive even after standardizing them in the second table, and negative values to remain negative.

Use this in I3, copied across and then down:

=D3/ABS(D$24)

Tried that Glen. It works for columns I and J, but I get the #DIV/0! error in column K.

The average is zero. Please-read my Q, from above and supply an answer:

You say: "I don’t agree with the results", they are mathematically correct. If you are EXPECTING something different, then you are using the wrong formula. So, what results do you EXPECT to see in column K (in numbers, not words) ?

Basically, if you want a formula to produce numbers that break the rules of mathematics, you'll have to explain what results you expect to see. The average of the 3rd column IS zero. Divide ANY number by zero and the answer (correctly) is infinity. If that's not what you want, what do you want?

Do you want the average of the absolute values of D, E & F??? We need to know what you really need and want...

I think you required how many percentage share against total Avg. If this correct.
In "I3"

Formula:

Please Login or Register  to view this content.

In "J3"

Formula:

Please Login or Register  to view this content.

In "K3"

Formula:

Please Login or Register  to view this content.

Copy paste down.

Thank you both for your replies. I was able to find a way of getting the results i wanted. Thank you.