SUMDIVISION formula function - does it exist?

[Peter A Davidson]

I am looking for a formula that does not seem to exist within the
existing range.

I am an intermediate level user of Excel

I often use the SUMPRODUCT formula to calculate the product value from
two columns but I now a requirement for what might be described as a
SUMDIVISION function which acts in exactly the same way as SUMPRODUCT
but divides rather than mulitplies the values between two associated
columns.

I can achieve the desired result by manually entering the operators -
e.g. =N11/$J11+N12/$J12+N13/$J13+N14/$J14+N15/$J15+N16/$J16 and so on
but there does not seem to be a formula such as:
SUMDIVISION ($J11:$J16, N11:N16)

There is a maximum limit to the number of characters you can enter
intp a formula. Because the columns are quite large I exceed this
limit

Doe anybody know a shorthand method of writing the formula to included
large arrays of cells in a shortform so I do not exceed the character
limit?

Thanks in advance for any advice offered


Peter Davidson
Alderley Edge
NW. England

 

[joeu2004]

I now a requirement for what might be described as a
SUMDIVISION function which acts in exactly the same way as
SUMPRODUCT but divides rather than mulitplies the values between
two associate dcolumns.

Someone is likely to point out that this question has been asked (by
you under the name "padav") and answered earlier this morning. Odd:
I find the thread when I do a search of Google Groups. But I do not
see the thread in the normal list of threads in this newsgroup --
which might explain the OP's reposting.

Anyway, the OP has add some detail....

I can achieve the desired result by manually entering the operators -
e.g.  =N11/$J11+N12/$J12+N13/$J13+N14/$J14+N15/$J15+N16/$J16
and so on but there does not seem to be a formula such as:
SUMDIVISION ($J11:$J16, N11:N16)

Well, first, I would think you want SUMDIVISION(N11:N16, $J11:$J16).
That can be written simply as:

=sumproduct(N11:N16/$J11:$J16)

 

[Ragdyer]

Was not in this (functions) group, but in the misc. group.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I now a requirement for what might be described as a
SUMDIVISION function which acts in exactly the same way as
SUMPRODUCT but divides rather than mulitplies the values between
two associate dcolumns.

Someone is likely to point out that this question has been asked (by
you under the name "padav") and answered earlier this morning. Odd:
I find the thread when I do a search of Google Groups. But I do not
see the thread in the normal list of threads in this newsgroup --
which might explain the OP's reposting.

Anyway, the OP has add some detail....

I can achieve the desired result by manually entering the operators -
e.g. =N11/$J11+N12/$J12+N13/$J13+N14/$J14+N15/$J15+N16/$J16
and so on but there does not seem to be a formula such as:
SUMDIVISION ($J11:$J16, N11:N16)

Well, first, I would think you want SUMDIVISION(N11:N16, $J11:$J16).
That can be written simply as:

=sumproduct(N11:N16/$J11:$J16)

 

[joeu2004]

Was not in this (functions) group, but in the misc. group.

Right you are! I misread the Google Groups search output. Perhaps
the OP can explain why he reposted the question.

 

[padav]

Right you are! I misread the Google Groups search output. Perhaps
the OP can explain why he reposted the question.

Many thanks for this reply - your solution works!

Simple really, if you know what you're doing (which I obviously don't)

Sorry for the repost

I posted originally at work but could not see my post appear in the
newsgroup so I tried again from home using a different application

Thanks again for your kind assistance


Peter Davidson
Alderley Edge
NW England

 

[ilia]

Someone is likely to point out that this question has been asked (by
you under the name "padav") and answered earlier this morning. Odd:
I find the thread when I do a search of Google Groups. But I do not
see the thread in the normal list of threads in this newsgroup --
which might explain the OP's reposting.

Anyway, the OP has add some detail....


Well, first, I would think you want SUMDIVISION(N11:N16, $J11:$J16).
That can be written simply as:

=sumproduct(N11:N16/$J11:$J16)

Perhaps more easily read, following the SUMPRODUCT format:

=SUMPRODUCT(N11:N16, 1/$J11:$J16)

Depending on the precision level you are looking for, this may lead to
minor decimal errors.

 

[Harlan Grove]

Perhaps more easily read, following the SUMPRODUCT format:

=SUMPRODUCT(N11:N16, 1/$J11:$J16)

....

And just in case zeros in J11:J16 should be skipped,

=SUMPRODUCT(N11:N16,($J11:$J16<>0)/($J11:$J16+($J11:$J16=0)))