| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula help please
is it possible to shorten this, possibly array or sumproduct?
=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
=SUM(B4:R4*TRANSPOSE(K3:K19))
array entered. "Gary Keramidas" wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
Correction:
=SUM(B4:R4*TRANSPOSE(Production!K3:K19)) "Gary Keramidas" wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
one other thing, there will not be values in every column of row 4 in this
example, so only columns with numbers will factor into the total. in this example, there are only numbers in D4 and F4. not sure if this makes any difference, though. -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
One way:
=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Gary Keramidas wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
Or just
=MMULT(B4:R4,Production!$K$3:$K$19) (not array entered) Dave Peterson wrote: One way: =SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Gary Keramidas wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
i put the values from K3:K19 in b1:r1 on the same sheet and this seemed to work.
which solution is best? =SUMPRODUCT((B4:R40)*($B$1:$R$1)*($B4:$R4)) -- Gary "Dave Peterson" wrote in message ... One way: =SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Gary Keramidas wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
this one gave me a #value for some reason. i'll use the other option. thanks for
the transpose idea. -- Gary "Dave Peterson" wrote in message ... Or just =MMULT(B4:R4,Production!$K$3:$K$19) (not array entered) Dave Peterson wrote: One way: =SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Gary Keramidas wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
thanks, jmb, worked fine. i think i'll just copy the values to the same sheet
and use the =sum array formula. then the user can see what they're multiply by anyway. -- Gary "JMB" wrote in message ... Correction: =SUM(B4:R4*TRANSPOSE(Production!K3:K19)) "Gary Keramidas" wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula help please
ok, thanks, that's what i thought might be the case.
-- Gary "JMB" wrote in message ... The MMULT error you see is likely due to some of the cells being empty. Is the 0 intended to test for empty cells or negative values? Sumproduct will treat empty cells as 0, perhaps just: =SUMPRODUCT($B$1:$R$1, $B4:$R4) "Gary Keramidas" wrote: i put the values from K3:K19 in b1:r1 on the same sheet and this seemed to work. which solution is best? =SUMPRODUCT((B4:R40)*($B$1:$R$1)*($B4:$R4)) -- Gary "Dave Peterson" wrote in message ... One way: =SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Gary Keramidas wrote: is it possible to shorten this, possibly array or sumproduct? =(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19) -- Gary -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
| Commenting custom formula fields/formula on formula editor | Excel Programming | |||
Linear Mode
