If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
#NUM! error
This has been a personal issue with me for a while John, since I screwed up
royally, by revising some formulas from the "*" style to the unary. "Not sure what you mean when you say "the unary returns a zero"." I've a datalist that keeps track of the volatile pricing of dyestuff. The cost of production is in part dependant on the price of the dye at the time of purchase, then equated to the date of it's use on various lots of fabric. The datalist is populated by either importing the prices from the vendors web site, or being keyed in by the office, from faxes or snail mail. Need I say more ! Mixed data values ! BUT ... that *DIDN'T* matter with this formula: =SUMPRODUCT((A2:A200=D1)*(B2:B200=F15)*C2:C200) A = dates B = dyestuff name C = prices BUT definitely *DID* matter with this formula: =SUMPRODUCT(--(A2:A200=D1),--(B2:B200=F15),C2:C200) Felt very proud that we were able to reduce costs to such a great extent. That is, until the accounting department started reconciling inventories! "Bottom line: it's the responsibility of the spreadsheet designer to ensure that inputs are of the proper form." Very true. But just like 9/11 ... until it happens the first time, you don't really give it much thought. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "JE McGimpsey" wrote in message ... Not sure what you mean when you say "the unary returns a zero". First, if you're importing data, it's unlikely that you'll have a structure like the one I suggested - it's far more likely that a table of numeric values is intended to be imported as numbers, whether they import as numbers or text. If D120 and E1:E20 contain imported data, with all column D values imported as numbers, but all column E values imported as Text, =SUMPRODUCT(D120,E1:E20) indeed returns zero, however =SUMPRODUCT(--D120,--E1:E20) returns the appropriate value. Bottom line: it's the responsibility of the spreadsheet designer to ensure that inputs are of the proper form. In a properly designed application, you shouldn't have to worry about data type, or to cripple valid functions to compensate for possible input errors. And of course, in XL03 and XL04, at least, you get a smart button popping up whenever you have "text numbers", offering to convert them... In article , "RagDyer" wrote: But my point is, if B1:B10 is populated with imported data, and that data is numeric text, then the unary returns a zero. That doesn't tell you if the conditions were *not* met, or if the data is (was) "contaminated". |
#12
|
|||
|
|||
#NUM! error
In article ,
"RagDyer" wrote: But just like 9/11 ... until it happens the first time, you don't really give it much thought. For more on that, see .programming's thread on XL Math Error... Warning - lots of heat, no light. |
#13
|
|||
|
|||
#NUM! error
"For more on that, see .programming's thread on XL Math Error.."
That went right over my head. Found nothing pertinent in Google. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "JE McGimpsey" wrote in message ... In article , "RagDyer" wrote: But just like 9/11 ... until it happens the first time, you don't really give it much thought. For more on that, see .programming's thread on XL Math Error... Warning - lots of heat, no light. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Upgrade from exchange 2000 to exchange 2003 error | John Landis | Installation & Setup | 2 | June 18th, 2004 02:28 PM |
Upgrade from Exchange 2000 to 2003 error | John Landis | Installation & Setup | 0 | June 17th, 2004 08:26 PM |
Custom Error Messages | DMc2004 | Database Design | 4 | June 11th, 2004 11:16 PM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |