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
    Viewing 6 reply threads
    • Author
      Posts
      • #1934548 Reply
        rjstorms
        AskWoody Lounger

        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 Lounger

        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 Lounger

        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 Lounger

        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

    Viewing 6 reply threads

    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.