Need help writing a formula

[phyllis]

I need a formula that will add a string of cells until it finds a cell with a
zero, then start the adding again. For example

a b c d e f g h i j k l m n o p q r s t u v
1 8 8 8 0 8 6 5 8 9 0 6 5 4 8 7 6 0 0 5 5 5 5

So the formula would add cells a1 thru v1, but each time it encountered a
zero, ignore the previous cells and start the calucation from the next cell.
So the total for row 1 in this example would be 20, which is the total of
cells s1 thru v1

Any help you can provide is very much appreciated.

thanks

 

[Bob Phillips]

=SUM(IV1:INDEX(1:1,MAX(IF((1:1=0)*(1:1<>""),COLUMN(1:1)))))

this is an array formula, so comit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[phyllis]

Thanks Bob for your response. However, your formula doesn't appear to be
working. When I enter it, I get a total of 465 no matter what numbers I
input in to the row of cells. I'm not sure what the "IV1" in your formula
represents. I'm trying to add across the row.
phyllis

 

[Peo Sjoblom]

Are you entering values in row 1?


--


Regards,


Peo Sjoblom

 

[phyllis]

Hi, I just realized this error seconds before your post. Acutally the I'm
working in row 4. Do I change all of the 1:1' s in the formula to 4:4?

 

[Jim Cone]

phyllis,
Another way using a custom function...
Copy the code below into a standard module and enter this formula in a cell...

= AddAfterZero(A1:W1)

Works on first row of the specified cells.
Note: you can also select the cells instead of typing them in.
'Code starts----

Function AddAfterZero(ByRef rng As Excel.Range) As Variant
On Error GoTo BadSum
Dim N As Long
Dim dblSum As Double
Set rng = rng.Rows(1).Cells
For N = rng.Count To 1 Step -1
If rng(N) <> 0 Then
dblSum = dblSum + rng(N).Value
Else
Exit For
End If
Next
AddAfterZero = dblSum
Set rng = Nothing
Exit Function
BadSum:
AddAfterZero = "Error " & Err.Number
End Function

'Code ends----
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"phyllis"
wrote in message
I need a formula that will add a string of cells until it finds a cell with a
zero, then start the adding again. For example

a b c d e f g h i j k l m n o p q r s t u v
1 8 8 8 0 8 6 5 8 9 0 6 5 4 8 7 6 0 0 5 5 5 5

So the formula would add cells a1 thru v1, but each time it encountered a
zero, ignore the previous cells and start the calucation from the next cell.
So the total for row 1 in this example would be 20, which is the total of
cells s1 thru v1
Any help you can provide is very much appreciated.
thanks

 

[Jim Cone]

Further...
The function will work on any row not just row 1.
Jim Cone

 

[phyllis]

Hi Jim,

Thank you so, much. I can tell this is going to work, because whenever I
create the forumla using the function, it show the right total. However,
whenever I try use the =AddAfterZero function, excel wants to treat it like
text instead of a formula, even though I've changed the cell value to
"number". I don't understand it. I'm using MS Office 2003 version of Excel

 

[phyllis]

thanks I again Jim. It works great. I found my problem, was show formulas
was checked in options. thanks again you're the best!

 

[phyllis]

Hi Jim,

Can you write that code to ignore blank cells in the row? I found that it
only works if there are no blank cells in the row. The numbers in the
columns represents days of the month and the data is enter each day, so there
are blank cells until the end of the month. thanks!

 

[Jim Cone]

phyllis,
I don't fully understand the problem you have with the function.

The return value of the function is treated as a number for me.
It is right aligned in the cell. I set the cell format before entering the formula.
I tested the function result by using the its cell address in a Sum function with out a problem...
=SUM(X2:Z2)
I also tested it by calling it within a formula...
=SUM(addafterzero(A1:K1), X2, 100) - and no problem.

I am using xl2002 today on Windows XP.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"phyllis"
wrote in message
Hi Jim,
Thank you so, much. I can tell this is going to work, because whenever I
create the forumla using the function, it show the right total. However,
whenever I try use the =AddAfterZero function, excel wants to treat it like
text instead of a formula, even though I've changed the cell value to
"number". I don't understand it. I'm using MS Office 2003 version of Excel

 

[Bob Phillips]

=SUM(IV4:INDEX(4:4,MAX(IF((4:4=0)*(4:4<>""),COLUMN(4:4)))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

phyllis,
Yes I can, but only because Phyllis was the name of my first girlfriend. <g>
See if this does what you want?
'---
Function AddAfterZero(ByRef rng As Excel.Range) As Variant
On Error GoTo BadSum
Dim N As Long
Dim dblSum As Double
Set rng = rng.Rows(1).Cells
For N = rng.Count To 1 Step -1
If Len(rng(N)) > 0 Then 'new line here
If rng(N) <> 0 Then
dblSum = dblSum + rng(N).Value
Else
Exit For
End If
End If 'new line here
Next
AddAfterZero = dblSum
Set rng = Nothing
Exit Function
BadSum:
AddAfterZero = "Error " & Err.Number
End Function
'---
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"phyllis"
wrote in message
Hi Jim,
Can you write that code to ignore blank cells in the row? I found that it
only works if there are no blank cells in the row. The numbers in the
columns represents days of the month and the data is enter each day, so there
are blank cells until the end of the month. thanks!

 

[phyllis]

Jim it works perfectly now. Thank you so much.
--
phyllis

phyllis,
I don't fully understand the problem you have with the function.

The return value of the function is treated as a number for me.
It is right aligned in the cell. I set the cell format before entering the formula.
I tested the function result by using the its cell address in a Sum function with out a problem...
=SUM(X2:Z2)
I also tested it by calling it within a formula...
=SUM(addafterzero(A1:K1), X2, 100) - and no problem.

I am using xl2002 today on Windows XP.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"phyllis"
wrote in message
Hi Jim,
Thank you so, much. I can tell this is going to work, because whenever I
create the forumla using the function, it show the right total. However,
whenever I try use the =AddAfterZero function, excel wants to treat it like
text instead of a formula, even though I've changed the cell value to
"number". I don't understand it. I'm using MS Office 2003 version of Excel