Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Redefine range of cells, array formula returns NA | Excel Discussion (Misc queries) | |||
Sumproduct returns value error when ref is formula | Excel Discussion (Misc queries) | |||
Array Offset() formula with height of 1 returns duplicates? | Excel Worksheet Functions | |||
Array formula returns blank in the cell where it is entered | Excel Worksheet Functions | |||
Looking for formula index/match-type that returns an array | Excel Worksheet Functions |