Problem With An Average IF Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carl - 24 Aug 2006 18:21 GMT

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.

Reply to this Message

Biff - 24 Aug 2006 20:08 GMT

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

>I am using this formula:
>
[quoted text clipped - 25 lines]
>
> Thank you in advance.

Reply to this Message

carl - 24 Aug 2006 20:41 GMT

Thank you Biff.

> Why start a new thread?
>
[quoted text clipped - 51 lines]
> >
> > Thank you in advance.

Reply to this Message

Biff - 24 Aug 2006 21:25 GMT

You're welcome!

Biff

> Thank you Biff.
>
[quoted text clipped - 54 lines]
>> >
>> > Thank you in advance.

Reply to this Message