ExcelBanter - Excel equations

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel equations (https://www.excelbanter.com/excel-discussion-misc-queries/448389-excel-equations.html)

I want to write an equation that says if E20, E30, E40, E50, and
E60 then I would take C2*E2, plus C3*E3, plus C4*E4, plus C5*E5,
plus C6*E6. So for example C2=400, C3=400, C4=450, C5=500, C6=550,
E2=200, E3=0, E4=100, E5=0, and E6=150. What I want to get for a
total is 207,500. Taking C2*E2+C4*E4+C6*E6. Can anyone help me?

Try...

=IF(AND(E20,E30,E40,E50,E60),SUM(C2*E2,C3*E3, C4*E4,C5*E5,C6*E6),0)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi ldehn

See the formula below, is this something that you could work with! You can replace the double quotes with whatever you require if the logical part is false, at the moment if it is false, it will display a empty cell.

=IF(AND(C20,C30,C40,C50,C60),SUMPRODUCT(C2:C6 ,E2:E6),"")

Kevin

Quote:

Originally Posted by ldehn (Post 1610265)

On Tue, 12 Mar 2013 21:31:14 +0000, ldehn wrote:

I want to write an equation that says if E20, E30, E40, E50, and
E60 then I would take C2*E2, plus C3*E3, plus C4*E4, plus C5*E5, plus
C6*E6. So for example C2=400, C3=400, C4=450, C5=500, C6=550, E2=200,
E3=0, E4=100, E5=0, and E6=150. What I want to get for a total is
207,500. Taking C2*E2+C4*E4+C6*E6. Can anyone help me?

Your description does not make sense with your requested answer.

In your example, you "AND" e2:e6. Since at least one of them (actually two: E3 and E5) is not 0, then, logically, you should not be executing the multiplication to obtain 207500.

In other words, you are NOT indicating, by your example, that you want to "AND" E2:E6 else your result should be zero.

If what you really want is an equation that says if E20, E30, E40, E50, OR E60, then ...

Try: =SUMPRODUCT((E2:E60)*C2:C6*E2:E6)

However, if E2:E6 can never be less than zero, you could simplify further to:

=SUMPRODUCT(C2:C6*E2:E6) Since 0*some number will always be 0

On Tue, 12 Mar 2013 21:31:14 +0000, ldehn
wrote:

I want to write an equation that says if E20, E30, E40, E50, and
E60 then I would take C2*E2, plus C3*E3, plus C4*E4, plus C5*E5,
plus C6*E6. So for example C2=400, C3=400, C4=450, C5=500, C6=550,
E2=200, E3=0, E4=100, E5=0, and E6=150. What I want to get for a
total is 207,500. Taking C2*E2+C4*E4+C6*E6. Can anyone help me?

Your description does not make sense with your requested answer.

In your example, you "AND" e2:e6. Since at least one of them
(actually two: E3 and E5) is not 0, then, logically, you should not
be executing the multiplication to obtain 207500.

In other words, you are NOT indicating, by your example, that you
want to "AND" E2:E6 else your result should be zero.

If what you really want is an equation that says if E20, E30,
E40, E50, OR E60, then ...

Try: =SUMPRODUCT((E2:E60)*C2:C6*E2:E6)

However, if E2:E6 can never be less than zero, you could simplify
further to:

=SUMPRODUCT(C2:C6*E2:E6) Since 0*some number will always be 0

I like that! It's enough to make me wanna look at SUMPRODUCT a lot
closer for future use<g! (I've never used it...)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wed, 13 Mar 2013 16:00:41 -0400, GS wrote:

I like that! It's enough to make me wanna look at SUMPRODUCT a lot
closer for future use<g! (I've never used it...)

It can also be frequently useful in entering array-type formulas,
especially when you don't want to rely on the end-user remembering
<ctrl<shift<enter vs just <enter.

Thanks! I'll pay attention to that when I'm playing around with it...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion