Forum Discussion

Lonnie White's avatar
Lonnie White
Copper Contributor
Dec 06, 2017

Trying To Remove the #DIV/0! Error

Greetings, I'm trying to get rid of the #DIV/0! message for the following: =(G26+J26)/(F26+I26). This formula is for my basketball player's SHOOTING PERCENTAGE for the game. Please take a look at my spread sheet and see if there are some recommendations to implement. The spreadsheet is set up so that whenever I enter data in certain cells and sheets, data will appear for each player in the Team Stats section and the Average Point Per Game section.  Exclusively, I'd like to figure out a way to leave certain cells blank until there is data entered for each game in the APG Section.  For instance, games 1-4 have already happened and I would like only for the APG for players to be blank until data is entered for the corresponding game. See Excel Document.

excel
  • Tanya Denton's avatar
    Tanya Denton
    Steel Contributor
    Dec 06, 2017
    this may work, but couldn't test as i had no idea which columns to enter data in!!

    =IFERROR(AVERAGE('GM 1 vs Central:GM 15 vs NatureCoast'!B28),0)
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Dec 06, 2017

    Hello,

     

    wow, you really didn't need to upload all the sheets in the workbook. It is more confusing than helpful, because you don't say where your data is and where you want to see the result.

     

    So, without looking at the workbook, if you want to avoid the #Div/0 error, don't divide if the divisor is zero.

     

    =(G26+J26)/(F26+I26)

     

    If (F26+I26) results in zero, you will get the error, so only perform the division when that sum is greater than zero, otherwise return a blank.

     

    =if(F26+I26>0,(G26+J26)/(F26+I26),"")

     

    With that, the error message in the 3D Average cell in S52 will also go away.

Resources