News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
• ## Need Help w/Excel 2003 Formula

Posted on Comment on the AskWoody Lounge
• Author
Posts

Using Excel 2003 on Win7SP1
See attached sample worksheet

I need help with a basic formula for Excel 2003 (that I can later tweak myself) giving the number of times the difference (shown in column G) between two columns (E minus F) is equal, respectively, to <0, =0, and >0 for each of the twelve months of the year. (Excel 2003 does not have the COUNTIFS, etc. function.)

Data is entered as follows:
C108:C307 = dd/mm/yy input entry
B108:B307 = C108:C307 mmm

E108:E307 = \$\$.\$\$ input entry
F108:F307 = \$\$.\$\$ formula results from elsewhere in the spreadsheet

G108:G307 = \$\$.\$\$ difference between E cells minus F cells

Rows 268-307 are blank but contain formulas that will compute and show data when a date is entered in column C

I have tried everything I can think of and nothing seems to work! Any help would be much appreciated.
Robert

###### Attachments:

Please see my attached Sample Worksheet #2.

I used your recommended formulas in my sample worksheet. They all seem to work fine except they will not work when the difference = 0. See cells K32:K43.

Please also see my note in cell J24.

Robert

###### Attachments:

Substitute CountIfs for Excel 2003.

Using COUNTIFS with Excel 2003

cheers, Paul

Hi Robert

see attached file

Instead of using SUMPRODUCT() formulas, you can use array-entered formulas like this:

=SUM((condition1)*(condition2)*(condition3)*…)

For example, to count the number of months where the Cost Difference in range \$G\$8:\$G\$55 is 0

{=SUM(((\$C\$8:\$C\$55)<>””)*(MONTH(\$C\$8:\$C\$55)=MONTH(\$C8))*((\$G\$8:\$G\$55)=0))}

Condition1 is (\$C\$8:\$C\$55)<>””)   we only want the non-blank dates

Condition2 is (MONTH(\$C\$8:\$C\$55)=MONTH(\$C8)) this is for the respective Month

Condition3 is ((\$G\$8:\$G\$55)=0)  this is to count only those months with zer0 values.

Now, to find the Average of ONLY the negative amounts for the respective months, you would use:

{=IF(J32>0,SUM(((\$C\$8:\$C\$55)<>””)*(MONTH(\$C\$8:\$C\$55)=MONTH(\$C8))*((\$G\$8:\$G\$55<0)*(\$G\$8:\$G\$55)))/J32,0)}

For this formula, you only want to calculate the ‘negative-average-amount’ if there are actually any negative amounts for that month (because we don’t want to see any #DIV/0! errors). We already have the Count of the negative amounts for each month e.g. in cell J32.

So we need to SUM only the negative amounts for each corresponding months, and then divide by the number of times that month had a negative amount etc etc etc

zeddy

Excel Vegan Business Assets (VBA) Manager

Sample-Worksheet-3-zeddy

###### Attachments:

Sorry, zeddy, for my delay in responding to your most recent input. I’ve been incorporating your recommended formulas into my (very large!) worksheet, which I’ve revised slightly as shown in cells J47:U64 of my Updated Sample Worksheet, attached.

All your recommendations work perfectly, but I have run in to one final (I hope!) problem: I have a formula entered in column G8:G43 of the sample worksheet wherein the cell is blank if there is no entry in column E. I need this formula to be extended in column G to the very end of the worksheet.

Note that when I remove the formula in G44:G55, shaded in pink, as I have done, my desired results will show as desired in the other four ranges that are also shaded in pink. But, when I extend the formula in column G to cells G44:G55, the array formulas in the shaded ranges do not work. Apparently array formulas will not work when a formula is entered in one of the ranges.

How can I correct this new problem? Thanks for your help.

Robert

###### Attachments:

Hi Robert

The array formulas I suggested don’t like having any text values within the source reference ranges.

Your formula in G8 (copied down) puts a ‘null character’ in the cell if corresponding E8 is blank. If we changed the formula in cell G8 to put a zero (instead of a blank) in the cell (if E8 is blank) then this would work with the array formulas. But we don’t really want to see those zeros when column E entries are blank , and we can’t just use conditional-formatting to hide such zeros (since it would then also hide those zeros that we do want to see e.g. cells [G15] [G21] and [G27] in your sample file).

So to get round this, we just change the formula to put a value of say, 0.0001 in the cell when the corresponding column E value is blank, and then use conditional formatting to ‘hide’ any cells that have such a value of 0.0001 in the Cost Difference column.

see attached file.

I picked the value of 0.0001 as this value is unlikely to arise as the difference between Actual and Projected Cost.

zeddy

Excel Drop-In Day Care

Updated-Sample-Worksheet-zeddy

###### 1 user thanked author for this post. rjstorms

Thank you very much, zeddy, for your help with my worksheet problems. Your solutions worked really well, and I appreciate it very much.
I did make one slight adjustment, however, to your last suggestion. Rather than use conditional formatting to color the entire cell white, I modified it to just make the font white.