Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Use of ''. (Office XL 2003)

    Hi everyone;
    Last Saturday I learned the use of in a formula (thanks again to Hans and Jezza).
    Now, I am trying to use it in my application (file attached).
    I want cell D37 to show result only when ALL cells in range B2:C33 have been filled. The current formula (at D37) works when the last cell has been filled.
    The copy of table on the right (with editing for the desired result), gives me an error.
    I am sure there is something I am missing.
    Help will be appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of ''. (Office XL 2003)

    You could use
    <code>
    =IF(COUNTIF(J2:K33,"=")>0,"",3/(AVERAGE(J2:K33)/$D$2))
    </code>
    COUNTIF counts the number of cells in the range specified in the first argument that satisfy the condition specified in the second argument. The condition "=" means "is empty".

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: Use of ''. (Office XL 2003)

    Yeeees! IAnd it works marvels!.
    Thanks again, Hans.

    Best regards,

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of ''. (Office XL 2003)

    You could use:
    =IF(COUNT(B2:C33)<>64,"",(3/(AVERAGE(B2:C33)/$D$2)))

    The formula counts to see how many of the 64 cells are occupied by a number. If all are occupied it performs your calculation otherwise it displays nothing (or "" if you will).

    The reason for the #VALUE is because you were trying to compare a range of cells to the "". Cannot be done.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: Use of ''. (Office XL 2003)

    Thank you all very much.
    As usual, I went back to my book to learn more about this (new for me), function.
    Best regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •