How to do calculations with Null in the column?

[Guest]

As showed in the example:

Row No. A B C Calculations
1 5 #N/A #N/A
2 10 3 20------------(A4-A2)
3 20 #N/A #N/A
4 30 6 25------------(A7-A2)/2
5 40 #N/A #N/A
6 50 #N/A #N/A
7 60 8 25-----------(A9-A4)/2
8 70 #N/A
9 80 14 20-----------(A9-A7)
10 90 #N/A
11 100 #N/A


Whether we do the calculations depends on the status of cells in the column
B. If the cell in the column B is denoted as #N/A, there's no calculation at
all. If the value in column B is a number, then we do some calculations with
cells in the column A and display the results in the column C.
The way to calculate is half the distance between cells in column A on
either side. But on upper or lower edge, it's simply the difference between
the edge cell and its adjacent cell. It's too difficult to explain this.
Please see the calculations above.

Thanks for the patience. Please help me solve this problem.

 

[Guest]

Use one of the is functions along with an IF function

=if(isna(B2),,B2+B4)



ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

 

[Guest]

Yuanhang

I could not understand your question but noticed your earlier post and
wonder if it is the same problem?

Here are two formulas, the first returns what I think you required in the
text and the second returns the same as the examples
what you asked for:
=IF(OR($B1:$B2=0,$C1:$C2=0),"",IF($A1>30,$B1,IF($A1<30,$C1,IF($A1=30,AVERAGE($B1:$C1)))))

what was shown if the example
=IF(OR($B1:$B2=0,$C1:$C2=0),"",IF($A1<30,$B1,IF($A1>30,$C1,IF($A1=30,AVERAGE($B1:$C1)))))

Post back if this does not solve this problem with as much detail as you
can, especially requirements and restraints.

Regards
Peter

 

[Guest]

If you want to show the N/A value replace the "" with NA() as in

=IF(OR($B1:$B2=0,$C1:$C2=0),NA(),IF($A1<30,$B1,IF($A1>30,$C1,IF($A1=30,AVERAGE($B1:$C1)))))

Best of luck
Peter

 

[Guest]

Thank you. Actually this is not the same problem as that in my earlier post.
As for this post, since there are some many #N/A in the column, what I want
is to ignore all the #N/A and calculate the intervals. If there is a way that
I can post my entire spreadsheet here, maybe I can explain this much better.
But now, I really don't know how to describe this. Thank you again.

Yuanhang