SUMDIVISION?

[Elijah]

Hi,

Is there something similar to the SUMPRODUCT function except using division?
I looked for the sumdivision function but this is not listed.

Elijah

 

[Chip Pearson]

Elijah,

Try the following array formula:
=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 enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jerry W. Lewis]

You can do arithmetic operations on arguments in SUMPRODUCT without
having to array enter it.
=SUMPRODUCT(A1:A6,1/B1:B6)
or equivalently
=SUMPRODUCT(A1:A6/B1:B6)
both work.

Jerry

 

[Elijah]

Thanks Chip, Jerry,

excellent - that worked

Elijah

 

[Elijah]

Going on from this - Is it possible to use these functions (SUM arrays, or
SUMPRODUCT) with non-contingent cells/or ranges?

I tried naming a range for the denominator but functions returned #value. Is
this possible?

thanks

Elijah

 

[Harlan Grove]

Going on from this - Is it possible to use these functions (SUM
arrays, or SUMPRODUCT) with non-contingent cells/or ranges?

....

You mean nonadjacent cells? Yes, but it takes some nonobvious constructs
such as

=SUMPRODUCT(N(OFFSET(C8,{0;2;5;9},0,1,1))
/N(OFFSET(D8,{0;8;4;1},0,1,1)))

to achieve the same result as

=C8/D8+C10/D16+C13/D12+C17/D9

The point is that N(OFFSET(BaseAddress,RowOffsetArray,
ColumnOffsetArray,1,1)) is the most reliable way to string an arbitrary
collection of cells in the same worksheet into an array without using VBA.

 

[Elijah]

Thanks Harlan,

I guess it probably better just the simple method. However I will hold on to
that function just in case.

Elijah