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
Home icon Home icon Home icon Email icon RSS icon
  • Need Help w/Excel 2003 Formula

    Posted on rjstorms Comment on the AskWoody Lounge

    This topic contains 6 replies, has 3 voices, and was last updated by  rjstorms 3 weeks, 1 day ago.

    • Author
      Posts
    • #1934548 Reply

      rjstorms
      AskWoody Plus

      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:
    • #1934627 Reply

      rjstorms
      AskWoody Plus

      Thanks, PaulB, for your reply.

      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.

      Thanks for your help.
      Robert

      Attachments:
    • #1934813 Reply

      Paul T
      AskWoody MVP

      Substitute CountIfs for Excel 2003.

      Using COUNTIFS with Excel 2003

      cheers, Paul

    • #1935098 Reply

      zeddy
      AskWoody_MVP

      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:
    • #1952713 Reply

      rjstorms
      AskWoody Plus

      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:
    • #1953788 Reply

      zeddy
      AskWoody_MVP

      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

      Attachments:
      1 user thanked author for this post.
    • #1961906 Reply

      rjstorms
      AskWoody Plus

      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.
      Thanks again for your help.
      Robert

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Need Help w/Excel 2003 Formula

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.