How to calculate in a one cell formula MAD = SUM(ABS(Fi Xi - Fi AVERAGE(Xi))

[afndst]

Hello

I am trying to achieve the computation of descriptive mesures in one cell formulas without relying on the computation of other mesures in separate cells, for exmple, the mean to compute the variance.

For raw data it has been very easy, bot not quite for frequencies data.

Perhaps my problem can be solved if i can multiply a range by a scalar within the formula.

Anyone can help?

Regards

[XOR LX]

Hi,

An example with expected result would be useful, even more useful if provided within an actual workbook.

Regards

[afndst]

Hello

Please find attached a Excel file that I would like to complete for MAD as done for the variance and standard deviation

Thank you

[XOR LX]

Thanks, so what precisely are your expected results in here?

Regards

[afndst]

The expected result is 1.20 for MAD with a formula like the following in which the arguments are only the Xi and Fi ranges:

s^2 = 2.8625 =SUMPRODUCT((C5:C9-SUMPRODUCT(D5:D9,C5:C9)/SUM(D5:D9))^2,D5:D9)/(SUM(D5:D9)-1)

This formula I am sending now is different from the one contained in the spreadsheet sent obtained by substituting

$E$11

by

SUMPRODUCT(D5:D9,C5:C9)/SUM(D5:D9)

in order to eliminate the reference to the sample mean that is now calculated from the data set.


Regards

[XOR LX]

Possibly this can be abbreviated further:

=SUMPRODUCT(D5:D9,ABS(C5:C9-SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)))/SUM(D5:D9)

Regards

[afndst]

Hello

It works perfectly.

Thank yo very much

[XOR LX]

You're welcome!

Regards