| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Array help with a formula | Excel Worksheet Functions | |||
| Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
| Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Hybrid Mode
