|
Problem With An Average IF Formula
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=She et3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sh eet2!$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. |
Problem With An Average IF Formula
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 "carl" wrote in message ... 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=She et3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sh eet2!$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. |
Problem With An Average IF Formula
Thank you Biff.
"Biff" wrote: 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 "carl" wrote in message ... 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=She et3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sh eet2!$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. |
Problem With An Average IF Formula
You're welcome!
Biff "carl" wrote in message ... Thank you Biff. "Biff" wrote: 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 "carl" wrote in message ... 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=She et3!$A5;IF(MID(Sheet3!$B$4:$K$4;5;2)=Sheet3!B$4;Sh eet2!$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. |
| All times are GMT +1. The time now is 02:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com