Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 155
Default Why did I use array formula - help

Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default Why did I use array formula - help

You are doing the equivalent of an "averageif" by having IF function sort
through your Score range and only outputting data that meets your criteria.
This is why you needed an array. The ISERROR is there in case you have zero
data points meeting criteria.

However, a shorter, non-array formula would be:
=IF(COUNTIF(Score,"0")=0,"",SUMIF(Score,"0")/COUNTIF(Score,"0"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Diddy" wrote:

Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Why did I use array formula - help

You might have zero values in the range "score", and if so the normal
AVERAGE function would count these and give you a lower average. Hence
the AVERAGE(IF part of the formula which only counts values that are
above zero. There is further checking in the formula so that it only
returns an average (of above-zero items) as long as there are no
errors in the range (or indeed if all values are zero, so there is no
average to return).

It needs to be an array formula so that it can check each value in the
range to see that it is above zero.

Hope this helps.

Pete

On Dec 8, 4:34*pm, Diddy wrote:
Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Why did I use array formula - help

The reason for it being an array formula is that you want to apply the
IF(Score0,Score) formula to each element of the Score array.
--
David Biddulph

"Diddy" wrote in message
...
Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 155
Default Why did I use array formula - help

Hi Luke,

Thanks for replying.
I've probably been helped with the formula by someone here.
The workbook works for different groups by hiding some rows using a macro.
So would the formula work to average only those cells in the range that have
a value. Hidden rows have zeros and shouldn't be included in the average.
If that is what it's doing could you help me by explaining how the formula
works please.

Many thanks
Diddy


"Luke M" wrote:

You are doing the equivalent of an "averageif" by having IF function sort
through your Score range and only outputting data that meets your criteria.
This is why you needed an array. The ISERROR is there in case you have zero
data points meeting criteria.

However, a shorter, non-array formula would be:
=IF(COUNTIF(Score,"0")=0,"",SUMIF(Score,"0")/COUNTIF(Score,"0"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Diddy" wrote:

Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 155
Default Why did I use array formula - help

Hi Luke,

Many thanks for your reply :-)

Please ignore previous post. When I stopped panicking and let the brain work
(it does sometimes), I realised that you had answered all the question :-)

Thank you again and thanks for the alternative formula

Cheers
Diddy

"Luke M" wrote:

You are doing the equivalent of an "averageif" by having IF function sort
through your Score range and only outputting data that meets your criteria.
This is why you needed an array. The ISERROR is there in case you have zero
data points meeting criteria.

However, a shorter, non-array formula would be:
=IF(COUNTIF(Score,"0")=0,"",SUMIF(Score,"0")/COUNTIF(Score,"0"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Diddy" wrote:

Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 155
Default Why did I use array formula - help

Many thanks
Diddy

"David Biddulph" wrote:

The reason for it being an array formula is that you want to apply the
IF(Score0,Score) formula to each element of the Score array.
--
David Biddulph

"Diddy" wrote in message
...
Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy



.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 155
Default Why did I use array formula - help

Many thanks
Diddy

"Pete_UK" wrote:

You might have zero values in the range "score", and if so the normal
AVERAGE function would count these and give you a lower average. Hence
the AVERAGE(IF part of the formula which only counts values that are
above zero. There is further checking in the formula so that it only
returns an average (of above-zero items) as long as there are no
errors in the range (or indeed if all values are zero, so there is no
average to return).

It needs to be an array formula so that it can check each value in the
range to see that it is above zero.

Hope this helps.

Pete

On Dec 8, 4:34 pm, Diddy wrote:
Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array formula SUMIF with 2D sum_range array Rich_84 Excel Worksheet Functions 3 April 3rd 09 10:46 PM
Array formula: how to join 2 ranges together to form one array? Rich_84 Excel Worksheet Functions 2 April 1st 09 06:38 PM
Find specific value in array of array formula DzednConfsd Excel Worksheet Functions 2 January 13th 09 07:19 AM
meaning of : IF(Switch; Average(array A, array B); array A) DXAT Excel Worksheet Functions 1 October 24th 06 06:11 PM
Array Formula - using LEFT("text",4) in formula Andrew L via OfficeKB.com Excel Worksheet Functions 2 August 1st 05 02:36 PM


All times are GMT +1. The time now is 05:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"