Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question
Hi all.
I'm doing a variation on the sumproduct equation that I've had working for a while, and while it's not returning any errors, I am getting a null answer, which I know to not be true-- by having looked. The general form of it that I'm using is: sumproduct((range1=cella1)*(range2&""=cella2&"")*( range3)) Per previous discussions I've found that the &"" acts as a data type converter. Today I'd like to attempt to expand that to 4 arrays, from 3. My form is: sumproduct((range1=cella1)*(range2&""=cella2&"")*( range4&""=any-arbitrary-#)*(range3)) The addition of the any-arbirtrary-# to the equation is what's causing the null answer, or 0 to be returned. Since I'd really like to have this be generic enough to adapt to different worksheets, I need to have that any-arbitrary-# become a statement that will look for a specific number within a text string. E.g. (range4&""=string(Somebody's data 3632)) and I want to obtain the 3632 out of the string to be compared against the range4&"". If it returns a true, then it's valid. I have looked at search(), as well as find(), and by appearances it only returns the value, not a true, or false. Thus still giving me a null, or 0 answer. Any ideas as to how I could do this? Thank you for your time. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question
ISNUMBER(SEARCH( ... ) ) (or FIND) will return True or False, so
perhaps you can make use of this. Hope this helps. Pete On Sep 21, 7:36 pm, SteveDB1 wrote: Hi all. I'm doing a variation on the sumproduct equation that I've had working for a while, and while it's not returning any errors, I am getting a null answer, which I know to not be true-- by having looked. The general form of it that I'm using is: sumproduct((range1=cella1)*(range2&""=cella2&"")*( range3)) Per previous discussions I've found that the &"" acts as a data type converter. Today I'd like to attempt to expand that to 4 arrays, from 3. My form is: sumproduct((range1=cella1)*(range2&""=cella2&"")*( range4&""=any-arbitrary-#*)*(range3)) The addition of the any-arbirtrary-# to the equation is what's causing the null answer, or 0 to be returned. Since I'd really like to have this be generic enough to adapt to different worksheets, I need to have that any-arbitrary-# become a statement that will look for a specific number within a text string. E.g. (range4&""=string(Somebody's data 3632)) and I want to obtain the 3632 out of the string to be compared against the range4&"". If it returns a true, then it's valid. I have looked at search(), as well as find(), and by appearances it only returns the value, not a true, or false. Thus still giving me a null, or 0 answer. Any ideas as to how I could do this? Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another sumproduct question | Excel Discussion (Misc queries) | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
Sumproduct Question | Excel Worksheet Functions | |||
SUMPRODUCT Question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions |