Dividing with Arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carlsondaniel@gmail.com - 30 Sep 2006 02:09 GMT

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

Reply to this Message

Bob Phillips - 30 Sep 2006 13:54 GMT

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

Signature

HTH

Bob Phillips

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

> 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
[quoted text clipped - 12 lines]
> but my knowledge is limited. Any suggestions, questions or feedback
> would be greatly appreciated! Thank you!!

Reply to this Message

carlsondaniel@gmail.com - 30 Sep 2006 19:29 GMT

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

> 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
[quoted text clipped - 26 lines]
> > but my knowledge is limited. Any suggestions, questions or feedback
> > would be greatly appreciated! Thank you!!

Reply to this Message

Bob Phillips - 01 Oct 2006 01:52 GMT

Can you give an example of the data layed out?

Signature

HTH

Bob Phillips

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

> Hi Bob,
>
[quoted text clipped - 16 lines]
> > 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))
> >
[quoted text clipped - 21 lines]
> > > but my knowledge is limited. Any suggestions, questions or feedback
> > > would be greatly appreciated! Thank you!!

Reply to this Message

carlsondaniel@gmail.com - 01 Oct 2006 02:21 GMT

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

> Can you give an example of the data layed out?
>
[quoted text clipped - 55 lines]
> > > > but my knowledge is limited. Any suggestions, questions or feedback
> > > > would be greatly appreciated! Thank you!!

Reply to this Message

Bob Phillips - 01 Oct 2006 09:48 GMT

> Hi again,
>
[quoted text clipped - 17 lines]
> 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
[quoted text clipped - 4 lines]
>
> 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.

Reply to this Message

carlsondaniel@gmail.com - 04 Oct 2006 02:49 GMT

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

> > Hi again,
> >
[quoted text clipped - 43 lines]
>
> I would rather stick to the newsgroups, so that others can participate/see.

Reply to this Message

Rate this thread: