Average formula

[maabbas]

The file containing 8 sheets the last sheet has week to date total.

I am getting the #Value! error while using the following formula, please note that there are some cells value zero.

=SUM(Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/INDEX(FREQUENCY((Mon!N93,Tue!N93,Wed!N93,Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)


please help me out

 

[Derek Brown]

Hello and welcome to The Board.
Try using 'Evaluate Formula' to see exactly which values are being used in your worksheets.
You may also find the following ("3D Spearing") of interest:
http://www.youtube.com/watch?v=moMzsM-KwgQ

 

[Aladin Akyurek]

The file containing 8 sheets the last sheet has week to date total.

I am getting the #Value! error while using the following formula, please note that there are some cells value zero.

=SUM(Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/INDEX(FREQUENCY((Mon!N93,Tue!N93,Wed!N93,Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)


please help me out

The figures are on different sheets which FREQUENCY cannot cope with (strange though)...

The following would work...

=SUM((Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/INDEX(FREQUENCY(CHOOSE({1,2,3,4,5,6,7},(Mon!N93,Tue!N93,Wed!N93,Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)

 

[maabbas]

The figures are on different sheets which FREQUENCY cannot cope with (strange though)...

The following would work...

=SUM((Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/INDEX(FREQUENCY(CHOOSE({1,2,3,4,5,6,7},(Mon!N93,Tue!N93,Wed!N93,Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)

Its also did not work, "getting too few argument" error.

 

[maabbas]

Hello and welcome to The Board.
Try using 'Evaluate Formula' to see exactly which values are being used in your worksheets.
You may also find the following ("3D Spearing") of interest:
http://www.youtube.com/watch?v=moMzsM-KwgQ

Thanks I will try from home, at work can not go to youtube

 

[Aladin Akyurek]

Its also did not work, "getting too few argument" error.

Sorry, it's a parens issue...

=SUM(Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/
 INDEX(FREQUENCY(CHOOSE({1,2,3,4,5,6,7},Mon!N93,Tue!N93,Wed!N93,
   Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)

 

[maabbas]

Sorry, it's a parens issue...

=SUM(Mon!N93,Tue!N93,Wed!N93,Thur!N93,Fri!N93,Sat!N93,Sun!N93)/
 INDEX(FREQUENCY(CHOOSE({1,2,3,4,5,6,7},Mon!N93,Tue!N93,Wed!N93,
   Thur!N116,Fri!N93,Sat!N93,Sun!N93),0),2)

i tried at home it worked will check at work tomorrow, by the way thank you very much, one more thing how actually this formula works?

 

[Aladin Akyurek]

i tried at home it worked will check at work tomorrow, by the way thank you very much, one more thing how actually this formula works?

You are welcome. Thanks for providing feedback.

Explanation:

The formula is a re-write of AVERAGE(array) in terms of SUM/FREQUENCY.

Taking up the same cells from just three sheets, that is:

=SUM(Mon!N93,Tue!N93,Wed!N93)/INDEX(FREQUENCY(CHOOSE({1,2,3},Mon!N93,Tue!N93,Wed!N93),0),2)

CHOOSE collects all of the cells from different sheets into an array, something like:

=SUM(Mon!N93,Tue!N93,Wed!N93)/INDEX(FREQUENCY({3,4,0},0),2)

Evaluating the SUM bit, we have:

=7/INDEX(FREQUENCY({3,4,0},0),2)

The FREQUENCY bit creates a count of values up to 0 into the 0 bin. All values above 0 go into say the > 0 bin, with something like as result:

=7/INDEX({1;2},2)

{1;2} says 1 value corresponding <= 0 and 2 values >0.

The INDEX bit takes out the 2nd item from {1;2}, which is 2, with:

=7/2, resulting in 3.5, the average we want to obtain.

Hope this helps.

 

[snoopyhr]

I am totally confused by the concept of ignoring the "0" sales day , whether or not you had sales , should the total always get divided by total number of days ( in this case it seems to be a 7) if you ignore the "0" day sale you are diluting your average and showing in to be higher than it should be. More or less like ENRON way . Or maybe this is a new way to express weekly average.

 

[maabbas]

You are welcome. Thanks for providing feedback.

Explanation:

The formula is a re-write of AVERAGE(array) in terms of SUM/FREQUENCY.

Taking up the same cells from just three sheets, that is:

=SUM(Mon!N93,Tue!N93,Wed!N93)/INDEX(FREQUENCY(CHOOSE({1,2,3},Mon!N93,Tue!N93,Wed!N93),0),2)

CHOOSE collects all of the cells from different sheets into an array, something like:

=SUM(Mon!N93,Tue!N93,Wed!N93)/INDEX(FREQUENCY({3,4,0},0),2)

Evaluating the SUM bit, we have:

=7/INDEX(FREQUENCY({3,4,0},0),2)

The FREQUENCY bit creates a count of values up to 0 into the 0 bin. All values above 0 go into say the > 0 bin, with something like as result:

=7/INDEX({1;2},2)

{1;2} says 1 value corresponding <= 0 and 2 values >0.

The INDEX bit takes out the 2nd item from {1;2}, which is 2, with:

=7/2, resulting in 3.5, the average we want to obtain.

Hope this helps.

Thank you very much for the explaination