Finding 2 cells >0

[basset87]

Is it possible to build a function that sums 2 cells in a row with values larger than 0? I want the function to sum like the example below with the numbers mixed with the 0 on the left side and the row on the right that I want to find a function to:


0 0
1 0
2 0
0 3
0 3
3 3
4 5
0 7
0 7
5 7
0 9
0 9
6 9
' 11

[Sam Capricci]

both have to be larger or either has to be larger than 0?

[Sam Capricci]

the more I think about this the more I am leaning toward any of these three...
=IF(AND(A1>0,B1>0),SUM(A1:B1),"") - for both greater than zero
=IF(OR(A1>0,B1>0),SUM(A1:B1),"") - for either greater than zero
=SUM(A1:B1) - just takes care of the whole mess.
or =A1+B1

[basset87]

This is the row that I got as dataset:
0
1
2
0
0
3
4
0
0
5
0
0
6

or if it will make it easier I can transform the 0 values to -:

-
1
2
-
-
3
4
-
-
5
-
-
6


And now I want to find a function that sums the 2 nearest cells with a value in this dataset and sum it in the row with the function as the 2 rows in the previous post

[Sam Capricci]

first I think you are confusing rows with columns. so in your post #4 what do you want done with - 5 - should it be ignored since there is no value above or below it? What would the results for what you posted be?

[basset87]

Yes, I meant columns. In that example with -5- I want the function to sum that number with the number 4 as those are the 2 nearest numbers. I want the function to ignore all non numbers/zero values and sum the 2 nearest numbers-

[basset87]

So I want the function column will look like this:
0
0
3
3
3
5
7
7
7
9
9
9
11

[Crooza]

if your first column is in A1 down then put this in B3 and drag down.

=IF(COUNTIF($A$1:A2,">0")>1,IF(A2=0,B2,A2+MAX($A$1:A1)),0)

This only works though if your data is in ascending order like your example

[basset87]

The data are random numbers, but the formula works fine for this example, thanks. Is it possible to apply a formula that doesn't take any regards if the data is ascending or not?

Is it possible to solve this problem in another way by first deleting all the cells in the column if a cell=0 as this example (https://www.youtube.com/watch?v=6_Ysi1SB9cY). That will make it easier to just sum up the 2 values by using the formula =sum(A1:A2), then go back to how the original column looked like and fill in all the cells where there used to be 0 with blanks so I will have the same number of cells in the column as to before the action of deleting cells.

[Crooza]

Give this a try in B3 again but after copying and pasting hit control + shift + enter as its an array formula then drag it down column B

Please Login or Register  to view this content.

it looks long and complicated but the reality is the black bit below does all the work and the red section is only to ensure you get a zero in the first few rows until you get two numbers to add together

Please Login or Register  to view this content.

[zbor]

If your data starts in A1 try thi from B3 to the end:

Please Login or Register  to view this content.

This will work in any order.

[basset87]

Thanks BIG time!