Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct bllank function handling
=sumproduct({1},{0}) produces 0 as expected.
=sumproduct({1},{""}) does the same. =sumproduct({1},{a1}) where a1 is "" does the same. =sumproduct({1},{a1}) where a1 is =if(0,0,0) does the same =sumproduct({1},{a1}) where a1 is =if(0,"","") produces #VALUE! for reasons I do not understand. Why the #VALUE! which is documented as: "The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value". I am trying to take advantage of: "SUMPRODUCT treats array entries that are not numeric as if they were zeros". My real data involves a function which returns non-negative numbers including zero and "" to mean - this does not apply. P.S. Is there any good reason why array constants can't contain function calls. e.g. the following is not an array constant: {if(0,0,0)} I am using Excel 2003 (11.6355.6360) SP1 on XP Professional SP2 if that is significant. -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Handling #NUM! error | Excel Worksheet Functions | |||
Error handling in macro | Excel Discussion (Misc queries) | |||
checkbox event handling function | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions |