Not sure what formula or array to use
 

I didn't know where to ask. I've attached an excel 2007 worksheet to
help show the problem. Please point me in the right direction if someone
could please.

1) I have a weekly forcast of mother parts. Weeks are on the top row.
MotherParts on on the left column. MotherParts might not always be in
the
same order, and they do repeat, but do not repeat in any order.
2) There are a set number of child parts for each type of mother part.
MotherPartA may have Xnumber of ChildPartM, and MotherPartB may have
Ynumber of ChildPartN and Znumber of ChildpartM. Childparts per mother
part does not change, it can be listed in a small table.
3) I'm needing a daily forcast of childparts.

I've tried {sum((range=criteria)*(range=criteria)*(data))} arrays, but

having a difficult time (a) matching the day to the weekly forcast and
(b)
multipling by the corrosponding number of child parts.
I've tried in Index(data,match(criteria,rang),match(criteria,ran ge))
function, but it only returns the forcast from the 1st mother part it
sees,
and doesn't add together the mulitple mother part forecasts.

Thank you anyone for some direction, rather it be a suggestion for a
formula
or somewhere else to ask.


+-------------------------------------------------------------------+
|Filename: example.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=230|
+-------------------------------------------------------------------+

--
Alexander
------------------------------------------------------------------------
Alexander's Profile: http://www.thecodecage.com/forumz/member.php?userid=713
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126850

Not sure what formula or array to use
 

I have to wish to join CageCode so I cannot see your worksheet
Suggest you read up on SUMPRODUCT

Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct

Then come back and give us a brief idea of how you worksheet looks

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Alexander" wrote in message
...

I didn't know where to ask. I've attached an excel 2007 worksheet to
help show the problem. Please point me in the right direction if someone
could please.

1) I have a weekly forcast of mother parts. Weeks are on the top row.
MotherParts on on the left column. MotherParts might not always be in
the
same order, and they do repeat, but do not repeat in any order.
2) There are a set number of child parts for each type of mother part.
MotherPartA may have Xnumber of ChildPartM, and MotherPartB may have
Ynumber of ChildPartN and Znumber of ChildpartM. Childparts per mother
part does not change, it can be listed in a small table.
3) I'm needing a daily forcast of childparts.

I've tried {sum((range=criteria)*(range=criteria)*(data))} arrays, but

having a difficult time (a) matching the day to the weekly forcast and
(b)
multipling by the corrosponding number of child parts.
I've tried in Index(data,match(criteria,rang),match(criteria,ran ge))
function, but it only returns the forcast from the 1st mother part it
sees,
and doesn't add together the mulitple mother part forecasts.

Thank you anyone for some direction, rather it be a suggestion for a
formula
or somewhere else to ask.


+-------------------------------------------------------------------+
|Filename: example.xlsx |
|Download:
http://www.thecodecage.com/forumz/attachment.php?attachmentid=230|
+-------------------------------------------------------------------+

--
Alexander
------------------------------------------------------------------------
Alexander's Profile:
http://www.thecodecage.com/forumz/member.php?userid=713
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=126850

Not sure what formula or array to use
 

Alexander;458342 Wrote:
I didn't know where to ask. I've attached an excel 2007 worksheet to
help show the problem. Please point me in the right direction if someone
could please.

1) I have a weekly forcast of mother parts. Weeks are on the top row.
MotherParts on on the left column. MotherParts might not always be in
the
same order, and they do repeat, but do not repeat in any order.
2) There are a set number of child parts for each type of mother part.
MotherPartA may have Xnumber of ChildPartM, and MotherPartB may have
Ynumber of ChildPartN and Znumber of ChildpartM. Childparts per mother
part does not change, it can be listed in a small table.
3) I'm needing a daily forcast of childparts.

I've tried {sum((range=criteria)*(range=criteria)*(data))} arrays, but

having a difficult time (a) matching the day to the weekly forcast and
(b)
multipling by the corrosponding number of child parts.
I've tried in Index(data,match(criteria,rang),match(criteria,ran ge))
function, but it only returns the forcast from the 1st mother part it
sees,
and doesn't add together the mulitple mother part forecasts.

Thank you anyone for some direction, rather it be a suggestion for a
formula
or somewhere else to ask.

Try:


Code:
--------------------
=IF(LEFT(TEXT(C23,"ddd"))="S","",SUMPRODUCT(SUMIF( $D$21:$D$22,$C$4:$C$12,$E$21:$E$22)*($D$3:$K$3=C24-WEEKDAY(C24,3)),$D$4:$K$12)/5)
--------------------


copied across


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126850