+ Reply to Thread
Results 1 to 11 of 11

Exclude empty cells

Hybrid View

  1. 05-03-2009, 10:46 PM #1
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Exclude empty cells

    I have this calculation which works fine if all cells have a value. But it will happen that cells in the range are empty.

    =SUMPRODUCT(--(1*LEFT('Courses input'!C4:D6,1)-C3<=0), --(1*MID('Courses input'!C4:D6,3,1)>=2))/E3

    What can i do to exclude empty cells in the range from calculation?
    Last edited by klund; 05-05-2009 at 06:53 AM.
    Reply With QuoteRegister To Reply
  2. 05-03-2009, 11:57 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Exclude empty cells

    Maybe this:

    =SUMPRODUCT(--(C4:D6<>""),--(1*LEFT('Courses input'!C4:D6,1)-C3<=0), --(1*MID('Courses input'!C4:D6,3,1)>=2))/E3
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 05-04-2009, 02:45 AM #3
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Exclude empty cells

    unfortunately that did not work. i get #VALUE!
    do not know what to do. any more ideas?
    Reply With QuoteRegister To Reply
  4. 05-04-2009, 03:19 AM #4
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Exclude empty cells

    i think it is the MID and LEFT arguments that breaks your solution.
    Works fine if i kick them out. But i really need them.
    Hmm im stuck!

    Anyone have a brilliant idea for this?
    Reply With QuoteRegister To Reply
  5. 05-04-2009, 03:27 AM #5
    zbor
    zbor is online now
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,052

    Re: Exclude empty cells

    Upload an example..
    Never use Merged Cells in Excel
    Reply With QuoteRegister To Reply
  6. 05-04-2009, 03:45 AM #6
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Exclude empty cells

    so it supposed to count if the second number in the cell is 1.
    So if you remove 1 of the values the mid formula wont work!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-04-2009, 04:52 AM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exclude empty cells

    use Text - ie remove need for coercion to value for test

    =SUMPRODUCT(--(C4:D4<>""),--(MID(C4:D4,2,1)="1"))
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  8. 05-04-2009, 08:12 AM #8
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Exclude empty cells

    thank you that works in all of my formulas except one!
    In that one i need to compare it to a cell that can change!
    any ideas on that.

    i am very thankful for all the help i get on this forum btw!
    Reply With QuoteRegister To Reply
  9. 05-04-2009, 08:35 AM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exclude empty cells

    Do you perhaps mean ?

    =SUMPRODUCT(--(C4:D4<>""),--(MID(C4:D4,2,1)=B4&""))

    where B4 contains value of interest...
    Reply With QuoteRegister To Reply
  10. 05-05-2009, 06:09 AM #10
    klund
    klund is offline
    Registered User
    Join Date
    05-03-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Exclude empty cells

    thanks donkey!
    worked fine.

    dont know how to put solved in the thread name!
    Reply With QuoteRegister To Reply
  11. 05-05-2009, 06:42 AM #11
    zbor
    zbor is online now
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,052

    Re: Exclude empty cells

    in first thread -> go advanced and just choose Solved from drop down menu
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1