Calculating AVERAGE DEVIATION from a SPECIFIED POINT??

[JayMW]

Hi People,

I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%)

I hope this makes sense.

Any help would be much appreciated.

James

[DonkeyOte]

Do you mean ?

=AVERAGE(IF(ISNUMBER(A1:A10),1-A1:A10))
confirmed with CTRL + SHIFT + ENTER

if you wish also to exclude 0%

=AVERAGE(IF(ISNUMBER(A1:A10)*(A1:A10<>0),1-A1:A10))
confirmed with CTRL + SHIFT + ENTER

where A1:A10 holds % values

[JayMW]

Hi DonkeyOte,

Thanks for your reply. Your suggestion works until a score above 100% is encountered, for example I will substitute one of the four scores used above with a 100%+ score:

88%, 94%, 118%, 67%

118% has the same DEVIATION from 100% as as does 82%, so substituting this score should not affect the AVERAGE DEVIATION FROM 100% of 17.25%, however using the suggested formula gives a figure of 8.25%

Thanks

[DonkeyOte]

Change the below section of the formula

1-A1:A10

to

ABS(1-A1:A10)

remember to reapply the Array when finished editing (CTRL + SHIFT + ENTER)

[JayMW]

Thanks again DonkeyOTE,

The new formula works across the set of data that I provided as an example, however in the spreadsheet I was looking to use the formula, the cells I need to calculate the AVE DEV FROM 100% are not consecutive, and when I change the formula =AVERAGE(ABS(1-A3, C3, E3, G3)) it gives an error.

Do you know a way to calculate over non contiguous cells?

Apologies I should have specified this at the start.

Thanks again

[DonkeyOte]

Yes, this is a pretty fundamental criteria...

Can you outline how many non-contiguous ranges we're talking about and also outline if there is any discernible pattern to the ranges to be used ?
(ie every x rows, every x columns etc... your ex. of A,C,E,G implying odd columns etc...)

To further narrow down options, are you able to install third party XL add-ins (freeware) - in particular I'm referring to the well known (and trusted) morefunc.xll add-in (offers a nice ARRAY.JOIN feature, useful when dealing with lots & lots of non-contiguous ranges)

[JayMW]

Ideally I dont want to use any plug-ins etc, as this workbook needs to be forwarded onto other users without requiring them to install anything.

In this example I have 88 columns of data. Every 4th column contains a 'Sales vs Target' % score. I need to calculate the AVG DEV FROM 100% across these 22 columns without altering the general layout of the columns.

Thanks

[DonkeyOte]

OK so for sake of demo let's assume that your values are in A3 to CJ3 with % in D3,H3,L3 etc to CJ3

=AVERAGE(IF(ISNUMBER(D3:CJ3)*(MOD(COLUMN(D3:CJ3)-COLUMN(D3),4)=0),D3:CJ3))
confirmed with CTRL + SHIFT + ENTER

[JayMW]

I just set up a new sheet matching the above cell locations (e.g. 88 columns of data starting in A3 and ending in CJ3, with the % scores in every 4th column (D3, H3, L3 etc) and then the formula in the next empty cell (CK3)

=AVERAGE(IF(ISNUMBER(D3:CJ3)*(MOD(COLUMN(D3:CJ3)-COLUMN(D3),4)=0),D3:CJ3))

Confirmed with CTRL+SHIFT+ENTER

And the result is 100.32%, which is not correct. The Answer should be 14.7%

Thanks

[DonkeyOte]

can you copy the sheet you created into a new file and upload ? If you want to omit 0 value from calculation you need:

=AVERAGE(IF(ISNUMBER(D3:CJ3)*(MOD(COLUMN(D3:CJ3)-COLUMN(D3),4)=0)*(D3:CJ3<>0),D3:CJ3))
committed with CTRL + SHIFT + ENTER

[JayMW]

Sure thing.

Attached now.

[DonkeyOte]

I was rushing my reply... I didn't actually put the correct calculation in did I... ie the AVERAGE should be of ABS(1-D3:CJ3) not simply D3:CJ3 !

[JayMW]

Hmm now Im confused. So what should the complete formula be for the example sheet I posted?

Thanks very much for your assistance

[DonkeyOte]

Assuming 0% values are to be ignored in the calculation:

Please Login or Register  to view this content.

[JayMW]

Works perfectly! Thanks a trillion^trillion



James