Missing values and Average Problem!

[fahadferoz]

Please have a look at the attached file!

Requirement:
In A and B there are different values. If any value is missing from A or from B then Column C will dircectly take the value available in any column. For exampe, please look at A2 and B2 where A2 has 30 but B2 has 0. So, C2 will be 30. But for C4, it needs to take the average value of A4 and B4 as both the data is present.

Thanks for your help!

 

[vletm]

Missing values?
0 is not missing value! It is real value ...
Test this:
For Cell C2 =IFERROR(AVERAGEIF(A2:B2,">0"),"")

 

[fahadferoz]

Hi Vletm, I am basically using a data sheet to be applying to SPSS Modeler. In that software, missing values means blank cell. And 0 means No (in bivarte data column). S0, 0 and Missing are not same. You are right (in mathematical term also).

The formula you gave fills up the blanks with 0 which is a problem for the SPSS Modeler analysis. Is there any way to develop a formula that does the work with out converting the blank space to 0.

Thanks for your reply and help!

 

[Hui]

Have a look at the AVERAGEIFS() function

C2: =IFERROR(AVERAGEIF(A2:B2,">"&0),0)
copy down

 

[vletm]

@fahadferoz
I see, Did You even check my answer?
and
You got basic twice same answer... (for averageifs() You would check syntax )
You could change that my given "" to something "cat", "none", 0...
which matters with Your 'SPSS Modeler'.

 

[fahadferoz]

Hi Vletm, Thanks for your response. It solved the problem.
Thanks!