sum product subtotal with offset function

[gand3rson]

Hello new member so thanks in advance for any help I can get <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have an excel formula that I am using to calculated dales sales on hand (a financial ration) that is as follows. I found the formula on the net and I am trying to dissect it/understand how it works. The part of the formula I am having difficulty with is <o></o>
<o></o>
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E4:AB4,,,,COLUMN(E4:AB4)-COLUMN(E4)+1))<=D3))<o></o>
<o></o>
I understand that sumproduct with “(--(“ is a way to count cells meeting a specific criteria. In this case the criteria is the number of cells that summed up must be less than cell “D3”. I am just not sure how the subtotal(9) and offset function are being combined to do this. Does anyone have any insight or have a place to point to me where I can read up on combining these three functions?<o></o>
<o></o>
Again much thanks<o></o>

 

[Marcelo Branco]

Hi,

Welcome to the forum!

Take a look at
http://www.dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset/

HTH

M.

 

[Matty]

Welcome to the Board!

I suppose my first question is to establish why you picked that specific formula for your calculation? What are you trying to calculate?

Matty

 

[gand3rson]

^^^ thanks I will give that a read it looks good<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
What I trying to calculate is days sales on hand = which is a financial indicator.<o></o>
<o></o>
I have numbers for inventory in one row<o></o>
<o></o>
In the row below I have COGS. So for each number of inventory I want to calculate how much time in the future it would take to burn through the inventory I have.<o></o>
<o></o>
i.e Inventory is end of month 1 = 100 and COGS is month 2 = 30 month 3 = 40 month 4 = 60. The answer here is 2.5 months (i.e. month one and month 2 I sell 70 of inventory and sell the remaining 30 of inventory in one half of month 4).<o></o>
<o></o>
Jan-09 Feb-09 Mar-09 Apr-09<o></o>
Inventory 100 70 60 50<o></o>
COGS 46 30 40 60<o></o>
<o></o>
The two choices so far are <o></o>
1) =SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D4:AA4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))<=C3))+LOOKUP(0,SUBTOTAL(9,OFFSET(D4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))-C3-D4:AA4,(D4:AA4-SUBTOTAL(9,OFFSET(D4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))+C3)/D4:AA4)<o></o>
<o></o>
which gives me the answer on months (I can get in days but it’s a doozy of a formula)<o></o>
<o></o>
or <o></o>
<o></o>
using a nested if statement<o></o>
<o></o>
=IF((C3-D4)<0,(D2-C2)*((C3)/D4),IF((C3-D4-E4)<0,(D2-C2)+(E2-D2)*((C3-D4)/E4),IF((C3-D4-E4-F4)<0,(E2-C2)+(F2-E2)*((C3-D4-E4)/F4),IF((C3-D4-E4-F4-G4)<0,(F2-C2)+(G2-F2)*((C3-D4-E4-F4)/G4),IF((C3-D4-E4-F4-G4-H4)<0,(G2-C2)+(H2-G2)*((C3-D4-E4-F4-G4)/H4),IF((C3-D4-E4-F4-G4-H4-I4)<0,(H2-C2)+(I2-H2)*((C3-D4-E4-F4-G4-H4)/I4),IF((C3-D4-E4-F4-G4-H4-I4-J4)<0,(I2-C2)+(J2-I2)*((C3-D4-E4-F4-G4-H4-I4)/J4),IF((C3-D4-E4-F4-G4-H4-I4-J4-K4)<0,(J2-C2)+(K2-J2)*((C3-D4-E4-F4-G4-H4-I4-J4)/K4),IF((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4)<0,(K2-C2)+(L2-K2)*((C3-D4-E4-F4-G4-H4-I4-J4-K4)/L4),IF((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4-M4)<0,(L2-C2)+(M2-L2)*((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4)/M4),IF((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4-M4-N4)<0,(M2-C2)+(N2-M2)*((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4-M4)/N4),IF((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4-M4-N4-O4)<0,(N2-C2)+(O2-N2)*((C3-D4-E4-F4-G4-H4-I4-J4-K4-L4-M4-N4)/O4)))))))))))))<o></o>
<o></o>
which works for up to 12 months but it’s a horribly long formula<o></o>
<o></o>
So I am trying to dissect formula (a) b4 using it - or if there is a simpler way I am definitely all ears. Thanks<o></o>
<o></o>

 

[gand3rson]

Hi,

Welcome to the forum!

Take a look at
http://www.dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset/

HTH

M.

I looked through and it seems that this article talks about the second argumnet in the offset function being an array which does not seem to be the case in the formula I am talkig about (or is it?)

 

[Marcelo Branco]

I looked through and it seems that this article talks about the second argumnet in the offset function being an array which does not seem to be the case in the formula I am talkig about (or is it?)

Not exactly the second argument, but in your formula
COLUMN(E4:AB4)-COLUMN(E4)+1
generates an array

To see it:
Open a new workbook
Select E4:AB4
type in the formula-bar
=COLUMN(E4:AB4)-COLUMN(E4)+1
Confirm with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

you will see
1 2 3 .....................24

This is what this part of your formula is doing, ie, generating an horizontal array
{1.2.3.4.......24}

In the article the array generated is vertical (rows), but the idea is exactly the same.

M.

 

[gand3rson]

Not exactly the second argument, but in your formula
COLUMN(E4:AB4)-COLUMN(E4)+1
generates an array

To see it:
Open a new workbook
Select E4:AB4
type in the formula-bar
=COLUMN(E4:AB4)-COLUMN(E4)+1
Confirm with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

you will see
1 2 3 .....................24

This is what this part of your formula is doing, ie, generating an horizontal array
{1.2.3.4.......24}

In the article the array generated is vertical (rows), but the idea is exactly the same.

M.

Thanks i thougt it might have something to do with columns vs. row

on the array when i confirm it the result that I view is still "1" but if understand its really giving me an array?

thanks again

 

[barry houdini]

As Marcelo says this part:

COLUMN(D4:AA4)-COLUMN(D4)+1

is generating an array from 1 to 24 then that array forms the width argument of OFFSET so the whole OFFSET function, i.e.

=OFFSET(D4:AA4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1)

.....is generating an array of progressively larger ranges, i.e. just D4 then D4:E4 then D4:F4 etc......and the SUBTOTAL function sums these (SUM won't work here) ranges to give an array of the sums of the progressively larger ranges......

To get days try this version

=LOOKUP(0,SUBTOTAL(9,OFFSET(D4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))-C3-D4:AA4,C2:Z2+(D4:AA4-SUBTOTAL(9,OFFSET(D4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))+C3)/D4:AA4*(D2:AA2-C2:Z2))-C2

format as number

 

[Marcelo Branco]

.....is generating an array of progressively larger ranges, i.e. just D4 then D4:E4 then D4:F4 etc......and the SUBTOTAL function sums these (SUM won't work here) ranges to give an array of the sums of the progressively larger ranges......

Hi Barry,

This use of SUBTOTAL still is not 100% clear to me.

If i understood correctly, you are saying that (as the width changes) SUBTOTAL is computing a SUM of SUMs like:

=SUM(D4) + SUM(D4:E4) + SUM(D4:F4)+....+SUM(D4:AA4)

Is it?

M.

 

[Marcelo Branco]

No,no...

I think that SUMPRODUCT is summing these. SUBTOTAL only generates these SUMs

M.