I would describe myself as an intermediate Excel user

I am familiar with the use of formulas to increase the productivity of Excel.

One common formula I have used frequently is SUMPRODUCT which calculates the
sum of two arrays.

However I have a requirement for a formula that might be described as
SUMDIVISION - i.e. exactly the same function as SUMPRODUCT but dividing
instead of multiplying.

I have looked in the formula lists but I cannot find one that fits the bill.
I have constructed this formula manually (a real hassle!) but you can only
have so many characters in your formula and I have run out - these are
relatively large arrays I am dealing with.

Does anybody have any advice they can offer?

You can use an array formula to do this. E.g.,

=SUM(A1:A5/B1:B5)

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See
http://www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"padav" wrote in message
...
I would describe myself as an intermediate Excel user

I am familiar with the use of formulas to increase the productivity of
Excel.

One common formula I have used frequently is SUMPRODUCT which calculates
the
sum of two arrays.

However I have a requirement for a formula that might be described as
SUMDIVISION - i.e. exactly the same function as SUMPRODUCT but dividing
instead of multiplying.

I have looked in the formula lists but I cannot find one that fits the
bill.
I have constructed this formula manually (a real hassle!) but you can only
have so many characters in your formula and I have run out - these are
relatively large arrays I am dealing with.

Does anybody have any advice they can offer?

Use either the array formula:

=SUM(E7:E16/D7:D16)

Array entered with Ctrl + Shift + Enter not just Enter

Or if you want to normally enter:

=SUMPRODUCT(E7:E16/D7:D16)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"padav" wrote in message
...
I would describe myself as an intermediate Excel user

I am familiar with the use of formulas to increase the productivity of
Excel.

One common formula I have used frequently is SUMPRODUCT which calculates
the
sum of two arrays.

However I have a requirement for a formula that might be described as
SUMDIVISION - i.e. exactly the same function as SUMPRODUCT but dividing
instead of multiplying.

I have looked in the formula lists but I cannot find one that fits the
bill.
I have constructed this formula manually (a real hassle!) but you can only
have so many characters in your formula and I have run out - these are
relatively large arrays I am dealing with.

Does anybody have any advice they can offer?

Simply use:

=SUMPRODUCT(B1:B5/A1:A5)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"padav" wrote in message
...
I would describe myself as an intermediate Excel user

I am familiar with the use of formulas to increase the productivity of
Excel.

One common formula I have used frequently is SUMPRODUCT which calculates the
sum of two arrays.

However I have a requirement for a formula that might be described as
SUMDIVISION - i.e. exactly the same function as SUMPRODUCT but dividing
instead of multiplying.

I have looked in the formula lists but I cannot find one that fits the bill.
I have constructed this formula manually (a real hassle!) but you can only
have so many characters in your formula and I have run out - these are
relatively large arrays I am dealing with.

Does anybody have any advice they can offer?