SUMDIVISION?

  • Thread starter Thread starter Elijah
  • Start date Start date
E

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
 
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
 
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
 
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
 
Elijah said:
Going on from this - Is it possible to use these functions (SUM
arrays, or SUMPRODUCT) with non-contingent cells/or ranges?
Click to expand...
....

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.
 
Thanks Harlan,

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

Elijah
 
Back
Top