Problem With An Average IF Formula

[Guest]

I am using this formula:

{=IF(ISERROR(AVERAGE(IF(Sheet2!$A$5:$A$58=Sheet3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sheet2!$B$5:$FF$58))));0;AVERAGE(IF(Sheet2!$A$5:$A$58=Sheet3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sheet2!$B$5:$FF$58))))}

This is Sheet2 (my data)

Sheet2
BOX# 20060103 20060104 20060105 20060106 20060109 20060110
BOX005 0.00% 30.51% 0.00% 0.00% 0.00% 0.42%

With header row formatted to"TEXT"

Here is the result of the formula:

Sheet3
BOX# 01 02
BOX005 0 0

With header row formatted "TEXT".

The result should be:

Sheet3
BOX# 01 02
BOX005 5.16% 0

Can someone please help me ?

Thank you in advance.

 

[Biff]

Why start a new thread?

It'd be nice to get some feedback one way or the other instead of starting a
new post. Like: Thanks, that works or Thanks, but that didn't work.

Looks like this portion:

MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4

Should be:

MID(Sheet2!$B$4:$K$4;5;2)=Sheet3!B$4

With header row formatted to"TEXT"

This is the header row that needs to formatted as text:

Sheet3
BOX# 01 02

See this screencap:

http://img382.imageshack.us/img382/8136/samplehy9.jpg

Biff

 

[Guest]

Thank you Biff.

Why start a new thread?

It'd be nice to get some feedback one way or the other instead of starting a
new post. Like: Thanks, that works or Thanks, but that didn't work.

Looks like this portion:

MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4

Should be:

MID(Sheet2!$B$4:$K$4;5;2)=Sheet3!B$4


This is the header row that needs to formatted as text:


See this screencap:

http://img382.imageshack.us/img382/8136/samplehy9.jpg

Biff

 

[Biff]

You're welcome!

Biff

Thank you Biff.