Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1
I have been doing a lot of reading on this function and can usually get it to
work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html The bad news is that Bob's site is experiencing problems. You may want to try every so often to see if it's back up. Kevin wrote: I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1
Here's a quick exercise to illustrate some of the differences:
Put these values in A1:C5 Name____Amount1__Amount2 Alpha___100______10 Bravo___200______20 Charlie_300______30 Delta___400______40 Now try these formulas: E1: =SUMPRODUCT(--(A1:A5="Bravo"),B1:B5)......One Amount col E2: =SUMPRODUCT(--(A1:A5="Bravo"),B1:C5)......Two Amount cols E3: =SUMPRODUCT((A1:A5="Bravo")*B1:C5)......Incl. heading row E4: =SUMPRODUCT((A2:A5="Bravo")*B2:C5).....Excl. heading row Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Kevin" wrote in message ... I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1
The determining factor as to what form to use should be by how the
calculating data range(s) are to be populated. The main difference between the unary form and the asterisk form is that when using the asterisk, *all* the data in the calculating range(s) *must* be numeric. That numeric data can even be numeric text - just as long as it looks like a number, the asterisk form will calculate it. If it's not in any numeric form, it will return an error. The unary form will simply by-pass any text or non-numeric data, and complete the calculation using only *real XL recognized* numbers. Therefore, if you're populating your calculating ranges with formulas that may return nulls ( "" ), or text messages (such as "No Data Present"), then the unary form is the *only* way to go. However, if data is to be either keyed in, or imported, the asterisk form *should* be the form of choice, since it will calculate the various forms of numbers that are usually imported from other apps or the web. Imported numeric text will by-passed with the unary form, without any indication as to what data was or was not used in the calculation, which may produce inaccurate returns. Another difference between the two forms, is that the asterisk form *must* be used when *uneven* range sizes are incorporated in calculating 2 dimensional (row v. column) formulas. With all things being equal, the unary form is supposedly the faster to calculate, making it the choice for very large ranges. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kevin" wrote in message ... I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1 -- Thanks
Thank you for the responses, they were very helpful.
I am certain that Question #2 will be posted as my usage progresses. Kevin "Kevin" wrote: I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct Question #1 -- Thanks
We appreciate your feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Kevin" wrote in message ... Thank you for the responses, they were very helpful. I am certain that Question #2 will be posted as my usage progresses. Kevin "Kevin" wrote: I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Worksheet Functions | |||
SUMPRODUCT Question | Excel Worksheet Functions | |||
Sumproduct question | Excel Discussion (Misc queries) | |||
Sumproduct question | Excel Worksheet Functions | |||
Question about sumproduct | Excel Discussion (Misc queries) |