Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel equations
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?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel equations
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 |
#3
|
|||
|
|||
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:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel equations
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel equations
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel equations
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel equations
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in < or mathematical excel equations | Excel Discussion (Misc queries) | |||
excel equations for teachers | Excel Discussion (Misc queries) | |||
solving equations using Excel | Excel Programming | |||
Excel equations | Excel Discussion (Misc queries) | |||
Excel equations... | Excel Discussion (Misc queries) |