Is there a sigma function in excel?

[smerk]

eg. that could complete a function such as

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

 

[Gary''s Student]

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

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.

 

[Gaurav]

=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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[David Biddulph]

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

 

[smerk]

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:G10>A3), TRUE VALUE, FALSE VALUE))

thanks

 

[Bernard Liengme]

Did you try it, to see if it works?
I used this:
=IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10>A3), 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:G10>A3)* 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

 

[smerk]

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:G10>A3) are included in the calculation. Is this
possible? thanks for any suggestions