Excel equations

[ldehn]

I want to write an equation that says if E2>0, E3>0, E4>0, E5>0, an
E6>0 then I would take C2*E2, plus C3*E3, plus C4*E4, plus C5*E5, plu
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 i
207,500. Taking C2*E2+C4*E4+C6*E6. Can anyone help me

 

[GS]

I want to write an equation that says if E2>0, E3>0, E4>0, E5>0, and

E6>0 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(E2>0,E3>0,E4>0,E5>0,E6>0),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

 

[Kevin@Radstock]

Hi ldehn

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

=IF(AND(C2>0,C3>0,C4>0,C5>0,C6>0),SUMPRODUCT(C2:C6,E2:E6),"")

Kevin

 

[Ron Rosenfeld]

I want to write an equation that says if E2>0, E3>0, E4>0, E5>0, and
E6>0 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 E2>0, E3>0, E4>0, E5>0, OR E6>0, then ...

Try: =SUMPRODUCT((E2:E6>0)*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

 

[GS]

I want to write an equation that says if E2>0, E3>0, E4>0, E5>0, and
E6>0 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 E2>0, E3>0,
E4>0, E5>0, OR E6>0, then ...

Try: =SUMPRODUCT((E2:E6>0)*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[/QUOTE]

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

 

[Ron Rosenfeld]

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>.

 

[GS]

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