Array Formula returns error but only for specific columns
 

I am using an array formula to pull percentages for a range of numbers. Here
is the problem. The formula works fine for the majority of values i am
returning; however, when i reference certain columns in the secondary
document (the one i am pulling data from) the formula returns an error. It
has to be a format error, because when i delete the values in that row and
replace them with generic numbers the error is fixed, even when i delete all
the values and just leave the cells empy i at least get a div error instead
of a #Value error. I have tried everthing i can thing of as far as
reformating the data, but to know avail. Here is the formula i am using.

=SUM(('Austin Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$F$3:$F$17158))/SUM(('Austin
Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$D$3:$D$17158))

'Austin Direct MFS' is the secondary data sheet where all my value are
contained. This formula is in the first column i am running. The error
appears when i the above formula 'Austin Direct MFS'!$D$3:$D$17158 is
replaced with 'Austin Direct MFS'!$E$3:$E$17158. Both columns D and E
contain the same number of values. Can anyone help me out.

Array Formula returns error but only for specific columns
 

Try using SUMPROUDUCT rather than SUM
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pblenis" wrote in message
...
I am using an array formula to pull percentages for a range of numbers.
Here
is the problem. The formula works fine for the majority of values i am
returning; however, when i reference certain columns in the secondary
document (the one i am pulling data from) the formula returns an error.
It
has to be a format error, because when i delete the values in that row and
replace them with generic numbers the error is fixed, even when i delete
all
the values and just leave the cells empy i at least get a div error
instead
of a #Value error. I have tried everthing i can thing of as far as
reformating the data, but to know avail. Here is the formula i am using.

=SUM(('Austin Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$F$3:$F$17158))/SUM(('Austin
Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$D$3:$D$17158))

'Austin Direct MFS' is the secondary data sheet where all my value are
contained. This formula is in the first column i am running. The error
appears when i the above formula 'Austin Direct MFS'!$D$3:$D$17158 is
replaced with 'Austin Direct MFS'!$E$3:$E$17158. Both columns D and E
contain the same number of values. Can anyone help me out.

Array Formula returns error but only for specific columns
 

That didn't seem to fix the problem. I don't know what exactly the problem
is, thanks EXCEL for your vague error messages. I tried to evaluate the
formula, but to know avail.

"Bernard Liengme" wrote:

Try using SUMPROUDUCT rather than SUM
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pblenis" wrote in message
...
I am using an array formula to pull percentages for a range of numbers.
Here
is the problem. The formula works fine for the majority of values i am
returning; however, when i reference certain columns in the secondary
document (the one i am pulling data from) the formula returns an error.
It
has to be a format error, because when i delete the values in that row and
replace them with generic numbers the error is fixed, even when i delete
all
the values and just leave the cells empy i at least get a div error
instead
of a #Value error. I have tried everthing i can thing of as far as
reformating the data, but to know avail. Here is the formula i am using.

=SUM(('Austin Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$F$3:$F$17158))/SUM(('Austin
Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$D$3:$D$17158))

'Austin Direct MFS' is the secondary data sheet where all my value are
contained. This formula is in the first column i am running. The error
appears when i the above formula 'Austin Direct MFS'!$D$3:$D$17158 is
replaced with 'Austin Direct MFS'!$E$3:$E$17158. Both columns D and E
contain the same number of values. Can anyone help me out.

Array Formula returns error but only for specific columns
 

What "error" do you get?
Is it VALUE ? If so, some entries are not numbers
Have you tried on a dummy workbook with smaller ranges? Then you will know
if the problem is the formula or the values.
You CANNOT use SUM here, it must be
=SUMPRODUCT( array1,array2,array3)/SUMPRODUCT(array3,array4,array5)
and within each SUMPRODUCT the arrays must have the same size.

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pblenis" wrote in message
...
That didn't seem to fix the problem. I don't know what exactly the
problem
is, thanks EXCEL for your vague error messages. I tried to evaluate the
formula, but to know avail.

"Bernard Liengme" wrote:

Try using SUMPROUDUCT rather than SUM
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pblenis" wrote in message
...
I am using an array formula to pull percentages for a range of numbers.
Here
is the problem. The formula works fine for the majority of values i am
returning; however, when i reference certain columns in the secondary
document (the one i am pulling data from) the formula returns an error.
It
has to be a format error, because when i delete the values in that row
and
replace them with generic numbers the error is fixed, even when i
delete
all
the values and just leave the cells empy i at least get a div error
instead
of a #Value error. I have tried everthing i can thing of as far as
reformating the data, but to know avail. Here is the formula i am
using.

=SUM(('Austin Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct
MFS'!$F$3:$F$17158))/SUM(('Austin
Direct MFS'!$A$3:$A$17158=B2)*('Austin Direct
MFS'!$A$3:$A$17158<=C2)*('Austin Direct MFS'!$D$3:$D$17158))

'Austin Direct MFS' is the secondary data sheet where all my value are
contained. This formula is in the first column i am running. The
error
appears when i the above formula 'Austin Direct MFS'!$D$3:$D$17158 is
replaced with 'Austin Direct MFS'!$E$3:$E$17158. Both columns D and E
contain the same number of values. Can anyone help me out.