Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Could anyone help me to apply the average formula to a couple cells, which at the same time had an "

Jose Luis Alanis
Jose Luis Alanis ✭
edited 12/09/19 in Archived 2015 Posts

Technical Part:

 

I had in cell "[Q1 - Points]2" =IF([1.- Question]2 = "c) Right Answer", "10", "0") and in cell "" I had [Q2 - Points]2=IF([2.- Question]3 = "e) Right Answer", "9", "2") and both formulas work right by themselves. Nonetheless, as I wrote down =AVG([Q1 - Points]2:[Q2 - Points]2) in a new cell, the result was #Divide by Zero. 

 

Problem Statement:

 

Cells referred as "[1.- Question]2" & "[2.- Question]3" have a dropdown list property. What I am doing is to assign a weight to every option of the dropdown list. Later, I tried averging any value obtained ([10,0]:[9,2]) out of the two cells, but I got the #Divide by Zero.

 

Could anyone help to obtain the average? Or else, explain why it is not working, or telling me if it is not possible to reach it?

 

Do not hesitate to contact me at jlalanis@bscglobal-usa.com. I highly appreciate your help.

Tags:
· Share on FacebookShare on Twitter

Comments

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    Hi Jose,

     

    It may be that the two cells you are trying to average contain text instead of numbers so when the average function tries to average the two cells it comes up with zero as the divisor in the average. I think dropdown lists can only contain text.

     

    · Share on FacebookShare on Twitter
  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    By the way, if the cells contain text, you can still use the AVG formula by usinging the VALUE() function to convert the text to numbers withing the AVG formula before adding them and dividing by the number of cells being averaged.

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    Jose - The issue is, the result of your formula ("10" or "0") is formatted as a text string because you are using parentheses. 

     

    "10" = text string

    10 = number

     

    Change your formulas to this...

     

    =IF([1.- Question]2 = "c) Right Answer", 10, 0)

     

    =IF([2.- Question]3 = "e) Right Answer", 9, 2)

     

    Jims suggestion of using VALUE() would also work becuase it would change the text string "10" into a number (10).

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories