Announcement

Collapse
No announcement yet.

count and sum only imaginary part of numbers, sign specific

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • count and sum only imaginary part of numbers, sign specific



    Imaginary Numbers conditional.xlsmHello,
    I have a list of numbers, some of which are ordinary real numbers (e.g. 6), some of which have an imaginary component (e.g. 0+2i or 0-3i).
    1) I'm trying to count the number of cells in the list that have a positive imaginary component, and how many have a negative imaginary component. The following works but is inelegant and does not help with item (2).
    =COUNTIF(C10:C20,"*i") for counting cells with positive imaginary numbers,
    =COUNTIF(C10:C20, "*-*i") for cells with negative imaginary numbers.

    2) Similarly, I'd like to do a conditional sum of the magnitudes of the negative OR positive imaginary values. E.g. if the list of numbers is
    6
    5
    3i
    2i
    -5i
    -6i

    Then I'd like to find that all the positive imaginary numbers add up to 5, while the negative imaginary numbers add up to 11. I need to do this without creating a new column (e.g. it is possible to create a column with formula =IMAGINARY(), then use the resulting ordinary numbers in a simple COUNTIF or SUMIF function, but I cant have a new column).
    Thank you.
    Last edited by jswalemail; August 29th, 2014, 13:27. Reason: Sample attachment added

  • #2
    Re: count and sum only imaginary part of numbers, sign specific

    Posting a sample workbook might be useful.

    Sample Data ....

    Sample Result ...
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: count and sum only imaginary part of numbers, sign specific

      Sample workbook attached to post. Thanks.

      Comment


      • #4
        Re: count and sum only imaginary part of numbers, sign specific

        Sheet 2 has the formulas ...

        Note ... for those items with no imaginary I put in 0i ... to make the coding easier ...
        Attached Files
        Regards,
        Barry

        My Favorite New Thing:
        Dynamic Named Ranges



        The alternative for
        "Press Any Key To Continue."

        and we all have one we'd like to use it on

        1. Cross Posting Etiquette
        2. Are You Here To Learn: What Have You Tried?
        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

        Comment


        • #5
          Re: count and sum only imaginary part of numbers, sign specific

          Hi,

          Assuming you're using Excel 2007 or later, you could also achieve the results with a couple of array formulas**:

          For positive:

          =SUM(IFERROR(POWER(SQRT(IMAGINARY(OFFSET(C8,ROW(C8:C11)-MIN(ROW(C8:C11)),,,))),2),0))

          For negative:

          =SUM(IFERROR(POWER(SQRT(-IMAGINARY(OFFSET(C8,ROW(C8:C11)-MIN(ROW(C8:C11)),,,))),2),0))

          Regards


          [I]**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
          Last edited by NBVC; August 29th, 2014, 22:01. Reason: Removed duplicate reference to website link.

          Comment


          • #6
            Re: count and sum only imaginary part of numbers, sign specific

            This is a good solution, thanks.
            Is it possible to do with formulas/arrays rather than a vba function? I'm not sure how to get the Imaginary function to return an array, or if it is possible.
            Thank you.

            Comment


            • #7
              Re: count and sum only imaginary part of numbers, sign specific

              Originally posted by jswalemail View Post
              This is a good solution, thanks.
              Is it possible to do with formulas/arrays rather than a vba function? I'm not sure how to get the Imaginary function to return an array, or if it is possible.
              Thank you.
              Did you see my post?

              Regards

              Comment


              • #8
                Re: count and sum only imaginary part of numbers, sign specific

                Originally posted by XOR LX View Post
                Did you see my post?

                Regards
                Sorry XOR LX, I didn't see your post when I wrote mine.
                Both the formulas and the vba function are excellent and clever solutions, exactly what was needed.
                Thanks for a speedy response also, very much appreciated.

                Comment


                • #9


                  Re: count and sum only imaginary part of numbers, sign specific

                  =COUNTIF(C10:C20,"*i") should return the count of all complex numbers with a non-zero imaginary part.
                  =COUNTIF(C10:C20,"*-*i") should return the count of complex numbers with negative imaginary part.

                  =COUNTIF(C10:C20,"*i")-COUNTIF(C10:C20,"*-*i") should count the number of imaginary numbers with a positive imaginary part.

                  Comment

                  Working...
                  X