# Formula Diagnosis

#### ncrb_10

##### New Member
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!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Col Delane

##### Active Member
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)

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

##### Active Member
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.

Replies
1
Views
111
Replies
1
Views
176
Replies
7
Views
134
Replies
5
Views
141
Replies
2
Views
138

1,195,827
Messages
6,011,820
Members
441,648
Latest member
Bigjohnj

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back