+ Reply to Thread
Results 1 to 11 of 11

Weighed Grade formula stop working with blank cell

  1. #1
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Weighed Grade formula stop working with blank cell

    HI! I am using the following formula for my grade-book which works great if all cells have a number; however, once a cell is empty it gives me a value error. How can I fix it. Any help will be greatly appreciated.

    =IF($J$4="","",(SUM(K5*L5,N5*O5,Q5*R5,T5*U5,W5*X5,Z5*AA5,AC5*AD5)/SUM(L5,O5,R5,U5,X5,AA5,AD5)))

    For example: K5 is the grade and L5 is the weight so if all grades and weights are entered it works but if I erase any of the grades it stops working or if I put only the first grade it does not work. In other words, it only works if all the grades are written. Since this is a grade book, I need to give me the result or the new result each time I put a Grade.

    Thanks!

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    Could you attach sample file (.xlsx or .xlsm) with any data and correct result and incorrect result with description why it is incorrect?
    For me formula works well.

    How to:

    Attach a sample workbook. (Not a picture!)
    Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
    Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
    Remember to desensitize the data.
    Note:
    • Please do not attach password protected workbooks/worksheets
    • Please do not attach file(s) from exterior servers
    • Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

    20 rows of data is enough (probably)

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.
    sandy
    I want to see what you are trying to achieve, not how you are trying to do it
    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Weighed Grade formula stop working with blank cell

    I did the attachment but I don't know if it attached
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    I will take N6 cell for example:
    N6: =IF(OR(M6="E",M6=""),"",M6/$M$3) - if this formula will produce any correct value that is ok, but if it will produce empty string, i.e. "" and next you want multiply empty string by any other value: =IF($J$4="","",SUM(K6*L6,N6*O6,Q6*R6,T6*U6,W6*X6,Z6*AA6,AC6*AD6)/SUM(L6,O6,R6,U6,X6,AA6,AD6)) you will get error #VALUE!
    Simply you can check it this way: in any empty cell type: =1*"" and Enter. You will see exactly the same error

    You tried multiply text by number. It will never play

    So, what you can do?
    Change everwhere in formulas empty string to zero [ "" to 0 ] ie. =IF(OR(M6="E",M6=""),"",M6/$M$3) to =IF(OR(M6="E",M6=""),0,M6/$M$3)

    and if you won't see zeroes in cells use Conditional Formatting where you can select font color to white if value =0

    After all if you get any error back here and again attach "new" workbook with description what and why

    I changed file, check this one
    :
    Attached Files Attached Files
    Last edited by sandy666; 03-12-2017 at 11:24 PM.

  5. #5
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Weighed Grade formula stop working with blank cell

    Thanks! I did what you suggested but still the problem is that the formula does not calculate the total unless all the grades are written and I need to be able to put one grade and see the total, then next time I put a grade to see the total of the first grade plus the second and so on until I have all the grades.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    Could you attach file with correct answers and incorrect answers? I am not well with spanish so I would like to see that with detailed description. Maybe anyone else will do that faster

  7. #7
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Weighed Grade formula stop working with blank cell

    LOL sorry for the Spanish, let me ask you something first, is there a way to make this formula to work or do the same thing even if a cell is empty
    =(K5*L5)+(N5*O5)+(Q5*R5)+(T5*U5)+(W5*X5)+(Z5*AA5)+(AC5*AD5)
    so for example, do the K5*L5 even if I have no data on N5*O5 etc...

    I am pretty sure it has to be the =IF(AND(ISNUMBER or something like that but I do not know how to write it because I am multiplying and adding
    Last edited by lizsantiago07; 03-14-2017 at 06:28 PM.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    But it works, see attsachment (or I don't understand what you want to achieve)
    Attached Files Attached Files

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    You need to remeber in yellow cells shoud be 0 or "" not 0 and "". If you will use 0 so every next cells should be 0, or if you will use "" so every next cells should be "". Not mixed.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Smile Re: Weighed Grade formula stop working with blank cell

    Thanks so much, I see that it was my mistake when entering the formula you gave me before. thanks again!

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    10,029

    Re: Weighed Grade formula stop working with blank cell

    You are welcome

    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 10-09-2016, 10:46 PM
  2. Replies: 15
    Last Post: 10-07-2016, 07:40 AM
  3. Replies: 1
    Last Post: 10-05-2016, 08:56 AM
  4. Replies: 1
    Last Post: 10-05-2016, 08:56 AM
  5. Formula stop when seeing a blank cell
    By whatsmyname in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 08-05-2010, 06:21 PM
  6. How Do You Make A Formula Stop At A Blank Cell?
    By BZK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2007, 05:46 PM
  7. [SOLVED] Getting a chart to stop if the formula resutls in a blank cell
    By markag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2006, 11:05 AM

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