Need help on nested if statement

[dko71166]

I need to calculate weeks of supply with a dynamic wos calculation. Using inventory from wk 21 (281,061), project that into shipments of the future weeks (wk 22, 23, 24 etc). In the first case, 281,061 covers 143,179 leavng a balance of 137,882. That balance then covers week 23 shipments of 114,629, leaving a balance of 23,253. That balance covers .169 of week 24 shipments (137,433). the answer for weeks of supply of my first inventory number of (281,061) should be 2.169. How do I write this given that in some cases, I may need to divide the first month, in other cases,after the second, in others after the third, etc, etc? Please HELP!!

Ship Invoice Prod Inv DOS
Week 21 129,230 281,061
Week 22 143,179 119,401 106,629 244,511
Week 23 114,629 108,588 117,813 247,695
Week 24 137,433 128,794 140,111 250,373
Week 25 135,212 134,335 159,597 274,758
Week 26 137,889 108,670 143,902 280,771
Week 27 133,547 104,278 95,723 242,947
Week 28 101,955 125,136 139,340 280,332
Week 29 135,157 133,540 173,838 319,013
Week 30 198,935 123,848 119,226 239,304
Week 31 260,513 164,958 172,347 151,138
Week 32 168,814 299,092 153,292 135,616

[DonkeyOte]

Not entirely sure I follow - it would help to have results for more than just Week 21 as it's not clear if Week 22 should commence from point at which Week 21 ceases to cover or whether each calculation is "new"

If we assume the latter, splitting the calculation in 2, then:

Please Login or Register  to view this content.

Column F generates the DOS values.

[dko71166]

your assumption was correct, it is a new calc at each new row. I used your calc and extended it to the bottom and it seems to work! I have no idea what its doing, but it is correct. Thank you so much!

[DonkeyOte]

Summary:

Column G essentially determines how many subsequent values in B can be added together in full before exceeding the current inventory
This is done by creating a variety of running totals c/o Subtotal w/ROW used to increment height of each range being aggregated - then using a MATCH construct to find the index pos. of the last value <= inventory within those results.

Column F uses the value in G to determine which value it needs to conduct the decimal remainder calculation against based on: inventory left over / requirement

The summation of these values is your result.

Example:

Using your sample file and row 2 as basis for explanation:

Please Login or Register  to view this content.

will generate the following array of values

{143179;257808;395241;530453;668342;801889;903844;1039001;1237936;1498449;1667263}

the first value is the sum of B3:B3, the 2nd value is sum of B3:B4, 3rd value is sum of B3:B5 etc with last value being sum of B3:B13

We test each value to see if <=E2 and given sample value this will evaluate to:

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

we then divide 1 by the Boolean to coerce it (TRUE is 1 and FALSE is 0) meaning we end up with an Array of:

{1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

The MATCH construct as we have it will return the position of the last numeric value in the above array - this will be 2.

When we then look at F we say that the integer portion (complete ship values covered by inventory) of our result is as defined by G (or 0 where non-numeric)

IF(ISNUMBER(G2),G2)

the remaining complexity relates to the (possible) decimal remainder of the incomplete ship, the first part of this calculation:

Please Login or Register  to view this content.

is concerned with establishing the remaining Inventory available at the point of handling the incomplete Ship - ie subtracting from the Inventory value the aggregate of the subsequent values that can be handled in their entirety (if any)
in this instance we know the 2 values below can be used completely (our result in G is 2)

we can then divide this value by the full amount of the first shipment that could not be catered for completely the value for which is determined by:

OFFSET($B2,1+IF(ISNUMBER($G2),$G2),0)

in this instance this will be the 3rd value below the current row

we add the result of the division to the integer value to give final result.

not trivial and there may be better methods...