Complex IF statement with average calculation.

[LianeS]

Sorry the title wasn't very descriptive, wasn't sure how to word this.

I have a spreadsheet with three columns: 1st Observation, 2nd Observation, Average.

In the observation columns, there will either be a number 1-5, or the letter 'N'.

For the average column:

If both columns contain "N", then the average column should be "N" - simple enough.
If neither column contains "N", then the average of the two numbers/observations should be calculated - also simple.
If only one column contains the letter "N', then the number from the column which isn't "N" should be returned.


So for example:
1st: 5, 2nd: 6, Average: 5.5
1st: N, 2nd: N, Average: N
1st: N, 2nd: 4, Average: 4
1st: 3, 2nd: N, Average, 3


How would I go about doing this?

[protonLeah]

=if(and(a1="n",b1="n"),"n",if(a1="n",b1,if(b1="n",a1,average(a1,b1))))

[Pete_UK]

Try this in C2:

IF(A2&B2="NN","N",IF(COUNTIF(A2:B2,"N")=1,SUM(A2:B2),(A2+B2)/2))

then copy down.

Hope this helps.

Pete

[oeldere]

=if(countif(F1:G1,"N,")>=1,"N",average(F1:G1)) and drag down.

[LianeS]

=if(and(a1="n",b1="n"),"n",if(a1="n",b1,if(b1="n",a1,average(a1,b1))))

Thank you! This worked with a little tweak:

=IF(AND(J6="N",K6="N"),"N",IF(J6="N",K6,IF(K6="N",J6,SUM(J6+K6)/2)))

Not sure why the average didn't work as it was.

[Pete_UK]

Another variation, adjusted for your real cells:

=IF(J6&K6="NN","N",SUM(J6:K6)/(2-(COUNTIF(J6:K6,"N")=1)))

Not as many IFs now.

Hope this helps.

Pete

[LianeS]

Another variation, adjusted for your real cells:

=IF(J6&K6="NN","N",SUM(J6:K6)/(2-(COUNTIF(J6:K6,"N")=1)))

Not as many IFs now.

Hope this helps.

Pete

This didn't work for me, I'm afraid.