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

Jose Luis Alanis

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.

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.

 

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.

Travis

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).