#NUM! Error in CSE Formula

Posted by Anton on September 12, 2001 12:19 AM

Hi,

I have a CSE formula that contains a few if statements, that are looking at a query on another sheet called "Data". The formula looks something like this:

=SUM(IF(Data!$C$1:$C$64000=A15,IF(Data!$D$1:$D$64000=0,Data!$F$1:$F$64000,0)))

The above works fine however when I refresh the query, all the formulas on the first sheet containing the above query show the error #NUM! I have not included field names in the query results, but it still seems to show the same error. Does anyone have an idea on how to prevent this form happening.

Thanks,

Anton.

Posted by Mark W. on September 12, 2001 1:28 PM

Check all of you values in F1:F64000 to be sure
that they're all numeric. Sometimes they look
like they're numeric, but they aren't. Use
the ISNUMBER worksheet function to test each
of the values.

Posted by Anton on September 12, 2001 7:34 PM

Mark,

Thanks for the response, but all the numerical columns in the data page are formatted as numeric. I used the =ISNUMBER() formaula to check all the numbers, and they all came back as true. The funny thing that happens, is when I refesh the query on the "Data" page, all the formulas loose their cell numbering reference. For example, the cell references $C$1:$C$64000 becomes $C:$C!. If I change back the cell references, the formula works ok. Does anybody have any other ideas?

Posted by Aladin Akyurek on September 12, 2001 11:32 PM

Anton,

Two proposals.

On Data:

(1)
Activate Insert|Name|Define.
Enter NumRecs in the Names in Workbook box.
Enter the following formula in the Refers To box.

=COUNTA(Data!$C:$C)

Click Add.

Don't close the Define Name window.

Enter CRANGE (or any sensible descriptive name) in the Names in Workbook box.
Enter the following formula in the Refers To box.

=OFFSET(Data!$C$1,0,0,NumRecs,1)

Click Add.

Enter DRANGE (or any sensible descriptive name) in the Names in Workbook box.
Enter the following formula in the Refers To box.

=OFFSET(Data!$D$1,0,0,NumRecs,1)

Click Add.

Enter FRANGE (or any sensible descriptive name) in the Names in Workbook box.
Enter the following formula in the Refers To box.

=OFFSET(Data!$F$1,0,0,NumRecs,1)

Click OK.

Change your CSE formula to:

=SUM(IF(CRANGE=A15,IF(DRANGE=0,FRANGE,0)))

By the way, an alternative CSE formula would be:

=SUM((CRANGE=A15)*(DRANGE=0)*(FRANGE))

or a non-CSE formula:

=SUMPRODUCT((CRANGE=A15)*(DRANGE=0)*(FRANGE))

(2)

Activate the option Tools|Macro|Visual Basic Editor.
Activate Insert|Module.
Paste the following user-defined function in the window with the "(code)" bit in the title:

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

Activate File|Close and Return to Microsoft Excel.

Activate A15. Type CRIT (or any other sensible name) in the Name Box on the Formula Bar.

Modify one of the formulas that you prefer to use for the task at hand as follows (I pick the SUMPRODUCT version, others also will do):

=SUMPRODUCT((USED(C:C)=CRIT)*(USED(D:D)=0)*(USED(F:F))

Now, this is important, the modified formula must be entered in some cell in DATA.

Obviously, you need to use the result in another worksheet. Simply refer to the cell of this formula in the target worksheet.

Note. Use one of the options, not both at the same time.

Aladin

================

Posted by Mark W. on September 13, 2001 6:50 AM

Anton, I presume that you used the Data | Get External Data
menu command to create your external data range.
This facility will automatically create a named
range (e.g., ExternalData1) that will expand and
contract as needed each time the data is refreshed.
Using either the Insert | Name | Define... menu
command or the drop down Name box adjacent to
the formula bar identify the name assigned to
your range and then use either INDEX or OFFSET
to reference the rows in column F.