What formula can I use if I have a row of values where each previous cell to
the left is added until a tested criteria changes?

For example, row A1 to E1 looks like this:

0,1,0,0,1

I need a formula in row A2 to E2 that tests each of the above cells. For
example: B2 will add the values of A1 and B1, because there are only '0's,
(as in 1 '0'), to the left of B1. However, E2 will add the values of C1:E1,
because the values of C1 and D1 are not '1'. A1 and B1 are not included,
because B1 is a '1'.

In other words how can I write a formula that tests if an adjacent cell meets
a criteria? If it does, the formula tests the next cell in the row to see if
it meets the criteria and so on until the formula encounters a cell that does
not meet the criteria is encountered?

For an example, I've upload a simplified spreadsheet to
www.linkm9.com/help2.xlsx
You can refer to this spreadsheet in your reply.

Thanking you.

you mean sth. like:

=SUM(INDIRECT(ADDRESS(1,MAX(IF($A$1:D1=1,COLUMN($A $1:D1)))+1)&":"&ADRES
(1,COLUMN(E1))))

?

array-enter this formula (i.e. with CTRL+SHIFT+ENTER instead of just
using ENTER)

HIH




On 14 Paź, 23:31, "GBExcel" <u55438@uwe wrote:
What formula can I use if I have a row of values where each previous cell to
the left is added until a tested criteria changes?

For example, row A1 to E1 looks like this:

0,1,0,0,1

I need a formula in row A2 to E2 that tests each of the above cells. For
example: B2 will add the values of A1 and B1, because there are only '0's,
(as in 1 '0'), to the left of B1. However, E2 will add the values of C1:E1,
because the values of C1 and D1 are not '1'. A1 and B1 are not included,
because B1 is a '1'.

In other words how can I write a formula that tests if an adjacent cell meets
a criteria? If it does, the formula tests the next cell in the row to see if
it meets the criteria and so on until the formula encounters a cell that does
not meet the criteria is encountered?

For an example, I've upload a simplified spreadsheet towww.linkm9.com/help2.xlsx
You can refer to this spreadsheet in your reply.

Thanking you.

there is typo in my formula

here is the correct one:

=SUM(INDIRECT(ADDRESS(1,MAX(IF($A$1:D1=1,COLUMN($A $1:D1)))+1)
&":"&ADDRESS
(1,COLUMN(E1))))

sorry

On 15 Paź, 10:10, Jarek Kujawa wrote:
you mean sth. like:

=SUM(INDIRECT(ADDRESS(1,MAX(IF($A$1:D1=1,COLUMN($A $1:D1)))+1)&":"&ADRES
(1,COLUMN(E1))))

?

array-enter this formula (i.e. with CTRL+SHIFT+ENTER instead of just
using ENTER)

HIH

On 14 Paź, 23:31, "GBExcel" <u55438@uwe wrote:



What formula can I use if I have a row of values where each previous cell to
the left is added until a tested criteria changes?

For example, row A1 to E1 looks like this:

0,1,0,0,1

I need a formula in row A2 to E2 that tests each of the above cells. For
example: B2 will add the values of A1 and B1, because there are only '0's,
(as in 1 '0'), to the left of B1. However, E2 will add the values of C1:E1,
because the values of C1 and D1 are not '1'. A1 and B1 are not included,
because B1 is a '1'.

In other words how can I write a formula that tests if an adjacent cell meets
a criteria? If it does, the formula tests the next cell in the row to see if
it meets the criteria and so on until the formula encounters a cell that does
not meet the criteria is encountered?

For an example, I've upload a simplified spreadsheet towww.linkm9.com/help2.xlsx
You can refer to this spreadsheet in your reply.

Thanking you.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Thank you,

I appreciate the help

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200910/1