Dividing with Arrays

[carlsondaniel]

I have an interesting problem. I am trying to break out a quantity
number from a list of products from a cash flow. On one row I have a
Product name, Quantity, Sales Price per Unit, and Total Sales Price
(Qty x SP per U) for my breakdown. Also on this row I have a Beginning
Month and an End Month for the range of when the products are sold.
This then divides the Products Sales Price evenly throughout the date
range specified.

Essentially, per row I have one product and all its calculations -
and I have a list of over 50 products. They all have different Sales
Prices and Dates - and sometimes they are empty.

What I am trying to do create a formula that will count the number of
units sold in that particular month. I have tried to use some arrays
but my knowledge is limited. Any suggestions, questions or feedback
would be greatly appreciated! Thank you!!

 

[Bob Phillips]

I am assuming that begin and end months are actual dates, such as 1/9/2006
and 31/10/2006, and you want to multiply that daily amount by the number of
days in the target month, Sep (9) in my example.

=SUMPRODUCT(--(MONTH(E2:E20)<=9),--(MONTH(F2:F20)>=9),G2:G20)*(DATE(YEAR(TOD
AY()),9+1,1)-DATE(YEAR(TODAY()),9,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[carlsondaniel]

Hi Bob,

Thanks for the reply. I am trying to work with the formula but I think
I may have not stated something correctly. The dates are broken down
by month, not day.(So the month is technically 10/1/2006, 11/1/2006,
etc.. I have a stream of cash flows broken down with each column being
a new month - 60 months out(used Edate). I have already broken down the
cash flows into each month by using a formula somewhat similar to
yours. I want to calculate the total units sold for that given month. I
think the easiest thing would be to divide the cash flow month array
by the total number of Qty column since they correlate. I am willing to
send you the spreadsheet of what I am trying to do if it will make
things clearer. Thank you again for you time - I sincerely appreciate
it.

Dan

 

[Bob Phillips]

Can you give an example of the data layed out?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[carlsondaniel]

Hi again,

Here is an example, hopefully the everything will stay aligned in this
posting (is there a way I can import pictures into here?) Essentially,
there are 3 different components to the calculation They all happen on
the same row and reference their calculations from this row with the
exception of the date header in the cash flow. The letters indicate
column

A-C are hard coded - D = B x C

A B C D
Description Qty Price/Unit Total Price

E - F are hard coded

E F
Beg Date (month) Units to Sell per month (Takedown)

G-H calcuate the sales. If the B (Qty) = 11 and F(Takedowns) = 5, then
G=2 and H=1

G H
Total Full Takedowns Remaining Units in Last to sell in last
month

I
End Date (month)

This is the last month, even if H=0

J - xxxx are the monthly cash flows.

I am not sure if this makes thing more confusing or not. Would you like
me to email it?

Thanks, Dan

 

[Bob Phillips]

Hi again,

Here is an example, hopefully the everything will stay aligned in this
posting (is there a way I can import pictures into here?) Essentially,
there are 3 different components to the calculation They all happen on
the same row and reference their calculations from this row with the
exception of the date header in the cash flow. The letters indicate
column

A-C are hard coded - D = B x C

A B C D
Description Qty Price/Unit Total Price

E - F are hard coded

E F
Beg Date (month) Units to Sell per month (Takedown)

G-H calcuate the sales. If the B (Qty) = 11 and F(Takedowns) = 5, then
G=2 and H=1

Why does G=2 and H=1, what is the rule?

G H
Total Full Takedowns Remaining Units in Last to sell in last
month

I
End Date (month)

This is the last month, even if H=0

What does that mean exactly?

J - xxxx are the monthly cash flows.

And what is the significance of these?

I am not sure if this makes thing more confusing or not. Would you like
me to email it?

It doesn't make it clear yet. I was hoping to see the headers, some example
data, and your expected results. You have introduced columns that you didn't
mention previously.

I would rather stick to the newsgroups, so that others can participate/see.

 

[carlsondaniel]

Sorry for my inconsistency. I thought this was a quick fix or something
I was overlooking so I left out some stuff to simplify in my first go
round. I will try to be more clear.

A B C
D
Description Qty Price/Unit Total
Price
Product 1 10 $5.00
$50.00
Product 2 15 $10.00
$150.00
Product 3 20 $20.00
$400.00

E F
Beg Date Units Sold per Month (Takedown)
Jan-2006 10
Feb-2006 7
Mar-2006 6

G H
Full Takedowns Leftover Units (In last month)
1 0
2 1 (2 full takdowns of 7
each = 14, 1 remaining )
3 2 (3 full takedowns of 6
each = 18, 2 remaining)

I
End Date
Feb-2006
April-2006
June-2006

these dates calculate from "H" (Leftover Units) Even if it is H = 0 it
still refers to that month.)
Example: Product 2 is sells 7 in Feb, 7 in March, and the remaining 1
in April. So the End Date calculation is in April. Even though Product
1 sells 0 in Feb, it is still calculating it as the last month.


The cash flows then break all of this out:

J K L
M N O
Jan-2006 Feb-2006 Mar-2006 Apr-2006
May-2006 Jun-2006
$50.00
$70.00 $70.00 $10.00
$120.00
$120.00 $120.00 $40.00


I am trying find a formula that can total the units sold per month. I
was thinking the best way to do that would be to use an array that
would divide the cash flow colum by the number of units. and that is
what I am struggling with. Does this help much. Let me know. Thanks
again for your support.

Dan