+ Reply to Thread
Results 1 to 5 of 5

show value only if not all cells are blank

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    5

    show value only if not all cells are blank

    First time posting...

    GOAL:
    - I have a column of data (multiple choice test answers). At the top is a calculation based on entries in the cells below. I want it to calculate if and only if the cells are not all blank.

    - The formula I'm using in cell E4: =IF(E5:E104="","",(A*B + C*D)) where A,B,C and D represent some other calculated values. .

    PURPOSE:
    - On the test, if a kid skips the first question but answers others, I want it to calculate the grade, but if the kid didn't take the test at all, I don't want a 0 contributing to the class average.

    PROBLEM:
    - E4 goes blank only when E5 is blank. I only want E4 to be blank if every cell in the range E5:E104 is blank. I tried it as both an array and non-array formula, but the problem persists.


    SOLUTION?

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: show value only if not all cells are blank

    Would this work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please click the * icon below if I have helped.

  3. #3
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: show value only if not all cells are blank

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

  4. #4
    Registered User
    Join Date
    12-11-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: show value only if not all cells are blank

    Got it. Had to use the SUMPRODUCT in an IF statement. SUMPRODUCT was adding up the nonblanks and therefore multiplying the calculation rather than just giving me the calculation. I modified it to:

    =IF(SUMPRODUCT(--(AH5:AH104<>""))=0,"",(A*B+C*D)))

    Thanks for the help!

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: show value only if not all cells are blank

    Or, as an alternative :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

+ 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