Is there a sigma function in excel?
 

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

=SUM((((B1:B10)/C2)-1))

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.
--
Gary''s Student - gsnu200779


"smerk" wrote:

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

=NORMSINV(1-((B1:B10) / (C2))) + 1.5

This will give you the sigma value. with defects in B1:B10 and opportunities
in C2.

Is that what you wanted?

"smerk" wrote in message
...
eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

If I understand your question correctly, try this array-entered** formula...

=SUM(((B1:B10)/C2)-1)

** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself.

Rick


"smerk" wrote in message
...
eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

=(SUM(B1:B10)/C2)-COUNT(B1:B10) ?
--
David Biddulph

"smerk" wrote in message
...
eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

thanks. now is it possible to use this formula within another array function?
What I would like to do, is have this formula

=SUM((((B1:B10)/C2)-1)

as the 'true' value in an IF formula

as in
=SUM(IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), TRUE VALUE, FALSE VALUE))

thanks

"Gary''s Student" wrote:

=SUM((((B1:B10)/C2)-1))

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.
--
Gary''s Student - gsnu200779


"smerk" wrote:

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

Did you try it, to see if it works?
I used this:
=IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), SUM((((B1:B10)/C2)-1)),"ZZZZ")
as an array function, and it worked.
Tell us what happened when you tried it.
This will also work, if you are OK with O when the conditions are not all
meet.
=(B1:B10=A2)*(E1:E10=B2)*(G1:G10A3)* SUM((((B1:B10)/C2)-1))
(array entered)

But David's =(SUM(B1:B10)/C2)-COUNT(B1:B10) makes more sence.

Please note you do not need your opening SUM. So for expample
while =SUM(A1*A2) will give the correct answer, so will =A1*A2
best wishes

--
www.stfx.ca/people/bliengme


"smerk" wrote:

thanks. now is it possible to use this formula within another array function?
What I would like to do, is have this formula

=SUM((((B1:B10)/C2)-1)

as the 'true' value in an IF formula

as in
=SUM(IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), TRUE VALUE, FALSE VALUE))

thanks

"Gary''s Student" wrote:

=SUM((((B1:B10)/C2)-1))

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.
--
Gary''s Student - gsnu200779


"smerk" wrote:

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)

Is there a sigma function in excel?
 

thanks-almost working! but, the SUM formula isn't what I wanted.
i want to do

sum((x/C2)-1), where x = B1, B2, B3, B4, B5, B6, B7, B8, B9 and B10
BUT also, so that only those data that pass the criteria
(B1:B10=A2)*(E1:E10=B2)*(G1:G10A3) are included in the calculation. Is this
possible? thanks for any suggestions

"Bernard Liengme" wrote:

Did you try it, to see if it works?
I used this:
=IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), SUM((((B1:B10)/C2)-1)),"ZZZZ")
as an array function, and it worked.
Tell us what happened when you tried it.
This will also work, if you are OK with O when the conditions are not all
meet.
=(B1:B10=A2)*(E1:E10=B2)*(G1:G10A3)* SUM((((B1:B10)/C2)-1))
(array entered)

But David's =(SUM(B1:B10)/C2)-COUNT(B1:B10) makes more sence.

Please note you do not need your opening SUM. So for expample
while =SUM(A1*A2) will give the correct answer, so will =A1*A2
best wishes

--
www.stfx.ca/people/bliengme


"smerk" wrote:

thanks. now is it possible to use this formula within another array function?
What I would like to do, is have this formula

=SUM((((B1:B10)/C2)-1)

as the 'true' value in an IF formula

as in
=SUM(IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), TRUE VALUE, FALSE VALUE))

thanks

"Gary''s Student" wrote:

=SUM((((B1:B10)/C2)-1))

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.
--
Gary''s Student - gsnu200779


"smerk" wrote:

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)