Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do calculations with Null in the column?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do calculations with Null in the column?
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) "Yuanhang" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do calculations with Null in the column?
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($A130,$B1,IF($A 1<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($A 130,$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 "Yuanhang" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do calculations with Null in the column?
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( $A130,$C1,IF($A1=30,AVERAGE($B1:$C1))))) Best of luck Peter "Billy Liddel" wrote: 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($A130,$B1,IF($A 1<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($A 130,$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 "Yuanhang" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do calculations with Null in the column?
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 "Billy Liddel" wrote: 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($A130,$B1,IF($A 1<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($A 130,$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 "Yuanhang" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I pick up only the rows with a non-null key column on a separate sheet? | Excel Worksheet Functions | |||
How do I Excel countif column a =? and column b = null ? | Excel Worksheet Functions | |||
end of column refence in several calculations | Excel Discussion (Misc queries) | |||
formula to sum all sum calculations in a column | Excel Worksheet Functions | |||
Average Column, but Skip Null and 0? | Excel Worksheet Functions |