+ Reply to Thread
Results 1 to 10 of 10

Calculate only with cells which contain numbers

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Calculate only with cells which contain numbers

    Dear all!
    I couldn't find the solution so far, so I write a new thread (hope it is on the right place).

    I am preparing some automatic form for field data.
    The problem is that I wont to calculate only with cells which contain numbers.
    Please see attachment.
    For example, if I measure 4 sets of fractures in the field and later I want to calculate some trigonometric functions,
    how can I use only cells which contain this filed data - of course automatically?
    In example, you can see that trigonometric functions are calculated also for empty cells,
    and the total result is wrong, because for example Cos0=1, but in fact there is no data for this empty cells.

    hope you understand me
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Calculate only with cells which contain numbers

    Hey mkrac,

    You could tell Excel not to count "1" values in your sum formula like this:

    Please Login or Register  to view this content.
    which gives you the answer you're looking for....
    Attached Files Attached Files
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Calculate only with cells which contain numbers

    Hi - Welcome to the forum. By definition excel treats a blank cell as 0, hence when you apply COS function to a blank cell it returns 1, which though trignometrically is correct but is not the ideal result. I think my assessing the length of the cell we can overcome this.

    Try the below in cell B16

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and this in cell C16:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does this help?

    P.S.: From my understanding this will give an error if you just type a space in your data range and this could be overcome by using trim.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  4. #4
    Registered User
    Join Date
    04-18-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate only with cells which contain numbers

    Dear BB1972,

    thanks for replay.
    The problem is that field result can also be 0 (A=0 and D=0) and in this case ignoring 1 would be an error
    Last edited by mkrkac; 11-05-2012 at 06:10 PM.

  5. #5
    Registered User
    Join Date
    04-18-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate only with cells which contain numbers

    Mr kbkumar,

    thanks for your suggestion, but your formula is not working in my excel file.
    Can you please try it on attached file and than attach it, because I can't find error (not so familiar with If, and...

    Thanks

  6. #6
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Calculate only with cells which contain numbers

    Attached. Let me know if this works for you.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Calculate only with cells which contain numbers

    Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

    =SUMIF(E2:E11,"<>",B16:B25)

    Or SUMPRODUCT with just one table.

    =SUMPRODUCT((COS(RADIANS(F2:F11)))*(COS(RADIANS(E2:E11)))*(E2:E11<>""))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  8. #8
    Registered User
    Join Date
    04-18-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate only with cells which contain numbers

    Mr. kbkumar,

    now it works, thank you!

  9. #9
    Registered User
    Join Date
    04-18-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate only with cells which contain numbers

    Quote Originally Posted by Haseeb A View Post
    Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

    =SUMIF(E2:E11,"<>",B16:B25)

    Or SUMPRODUCT with just one table.

    =SUMPRODUCT((COS(RADIANS(F2:F11)))*(COS(RADIANS(E2:E11)))*(E2:E11<>""))
    Mr. Haseeb,

    thank you for your suggestion and your time, but I attached this table only as an example, in fact I don't need sum of ax, ay, az.
    But I learned something :-)
    I will play a little bit with table and than attach final version if I succeed to finish it or even if I don't finish it.

    Sorry
    Last edited by mkrkac; 11-06-2012 at 05:26 PM.

  10. #10
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Calculate only with cells which contain numbers

    Glad it worked for you. In case this is resolved, please mark the thread as "Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1