Formula that uses results of multiple arrays to get to one answer

[concatch]

Hi,

I have attached a spreadsheet. The logic is as follows:

I calculate Mean using Averageif and SD using an array formula (since there is no Stddev-if)

Base on these I calculate Z

I then Absolute Z and find an average which is highlighted in yellow.

Is it possible to have the data in columns A and B and arrive at the single value highlighted without having to go through all the steps ie One formula to calculate all my intermediate steps?

Any help would be great! I'm sure there is a way to do it in a macro but I'm looking for a single formula.

Thanks a million

[XOR LX]

Hi,

Well, you can just substitute each of your calculation steps into one (array) formula:

=AVERAGE(ABS((B3:B5-(AVERAGEIF($A$3:$B$7,D3,$B$3:$B$7)))/STDEV(IF($A$3:$A$7=D3,IF($B$3:$B$7<>"",$B$3:$B$7)))))

Regards

[benishiryo]

hi concatch. not sure if there's a ready-made statistical formula since i'm not familiar with that. but combining your formulas, another alternative is:

Please Login or Register  to view this content.

[concatch]

Thanks for the help, however both your solutions yield different results to each other and to my solution. Is there any way you could download the workbook provided and then upload your solution?

[concatch]

I've managed to get to the value in H3 using =STANDARDIZE(B3,AVERAGEIF($A$3:$B$7,D3,$B$3:$B$7),STDEV(IF($A$3:$A$7=D3,IF($B$3:$B$7<>"",$B$3:$B$7)))) but as an array Ctrl+Shift+Enter

Maybe this will assist....

[XOR LX]

The formula I gave you gives precisely the same result (0.734793113699706) as that in cell I9 of your attachment.

Regards

[concatch]

XOR LX, you're right it does. Not sure why I was getting a different answer, perhaps I didn't enter it as an array. Thank you very much.

[XOR LX]

You're welcome. Thanks for the feedback.