Formula Diagnosis

[ncrb_10]

Hello,

I need help with the following diagnosis and a modification to an existing formula.

Here is the table I am working with:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Upgrade 1

Upgrade 2

Upgrade 3

# employees

# months

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

2

May

3

3

<tbody>
</tbody>

In my OP, the logic I needed solved was - if any cell in F2:Q2 = cell (A2:C2)-E2, then =D2.

This was the formula that was provided, which works great but I ran into an issue - SUMPRODUCT((MONTH($F41:$H41)=MONTH(EOMONTH(L$40,($K41+1))))*$J41))

Here is my problem...if I have no data in cells A2:C2, then cell P2=9... and let's say I enter a month into A2, then P2=6. Why is that happening?

And secondly, I also need the formula to show the number of employees I need for each month leading up to the upgrade month.

Thanks!

 

[Col Delane]

Re the 1st problem (formula):

1. Note that the formula shown in your post doesn't match the spreadsheet extract you've pasted in (it's out of sync by 5 columns and 40+/- rows)
2. I think the main problem is that the formula shown converts all dates to Month numbers, and in doing so (for a reason I don't yet understand) converts a blank cell in A2:C2 to month No. = 1 (& in your example returns that result twice), and then because Excel treats these as valid month numbers, they factor into the rest of the calculations.
3. The fix:
   1. Enter all dates (in columns A:C rows and F:Q headings) as date values rather than text (i.e. enter as dd/mm/yy for UK/Australia or mm/dd/yy for North America), and then format as "Mmm-YY".
   2. Change the formula in F2 (Oct) to = SUMPRODUCT( ( ( $A3:$C3 ) = EOMONTH( F$2, $E3 ) ) *$D3 ) and then copy across to Q2. Note that this doesn't deal with the 2nd issue you've requested help with (see below)

Re the 2nd problem (pre-upgrade lead up):

I also need the formula to show the number of employees I need for each month leading up to the upgrade month.

I presume that this lead-up period in which you want the number of employees to show matches the values in column E, so in your example, you expect to see 3 in each of Feb, Mar & Apr. (Is there a further presumption here that the upgrade goes live in the month entered in A:C so no labour is required in the upgrade month?)

 

[Col Delane]

Due to the nature of the data and the logic you require, I have been unable to find a way to employ arrays (like that available in SUMPRODUCT) to include the Upgrade months (A:C) into the formula so as to reduce its size. As a result, I've ended with with the following ugly monster that has a formula component to separately deal with each possible Upgrade and which now solves both problems you've described (at least I think so! ray

Enter this formula in F2 (Oct):
= IF( $A2, IF( AND( F$1 >= OFFSET( INDEX( $F$1:$Q$1, MATCH( $A2, $F$1:$Q$1, 0 )), 0, -$E$2 ), F$1 < OFFSET( INDEX( $F$1:$Q$1, MATCH( $A2, $F$1:$Q$1, 0 )), 0, 0 )), $E2, 0 ), 0) +
IF( $B2, IF( AND( F$1 >= OFFSET( INDEX( $F$1:$Q$1, MATCH( $B2, $F$1:$Q$1, 0 )), 0, -$E$2 ), F$1 < OFFSET( INDEX( $F$1:$Q$1, MATCH( $B2, $F$1:$Q$1, 0 )), 0, 0 )), $E2, 0 ), 0) +
IF( $C2, IF( AND( F$1 >= OFFSET( INDEX( $F$1:$Q$1, MATCH( $C2, $F$1:$Q$1, 0 )), 0, -$E$2 ), F$1 < OFFSET( INDEX( $F$1:$Q$1, MATCH( $C2, $F$1:$Q$1, 0 )), 0, 0 )), $E2, 0 ), 0)

If your timeline only extends for a rolling 12 months, you could include each of the upgrade components in separate helper tables (one for each upgrade) off the the right of your master table, and then in each month of the master table just sum the employee counts in each of the corresponding months in the subsidiary tables.