[Andrew]

A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?

[Max]

Something like this in say, E1:
=SUMPRODUCT((A1:A100="FD")*(B1:B100-C1:C100=0))
should return what you seek
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?

[Andrew]

Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows are
updated) sequence happened, and then return the value of the latest sequence.

In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)

Possible?

"Max" wrote:

Something like this in say, E1:
=SUMPRODUCT((A1:A100="FD")*(B1:B100-C1:C100=0))
should return what you seek
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?

[Max]

Ah, I mis-understood it earlier, sorry. Think I'm out of ideas.
Perhaps other responders could step in here ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote in message
...
Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If
you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows
are
updated) sequence happened, and then return the value of the latest
sequence.

In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)

Possible?

[Monkey-See, Monkey-Do[_2_]]

why not have (in column d)

=IF(A1="FD", IF((B1-C1=0), 1, 0), 0)

Which would put a 1 in the cell if your criteria is met and 0 if not...

Then at the bottom sum up all your column D? The total is the answer you
want...






"Max" wrote:

Ah, I mis-understood it earlier, sorry. Think I'm out of ideas.
Perhaps other responders could step in here ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote in message
...
Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If
you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows
are
updated) sequence happened, and then return the value of the latest
sequence.

In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)

Possible?

[Janusz Pawlinka]

U¿ytkownik "andrew" napisa³ w wiadomo¶ci
...
A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula
detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before
a
break again).

What is the best way to calculate this?

It is a bit complicated, but...


in columns A, B, C are your data

in cell D1 insert the formula:
=IF(A1="FD", IF((B1-C1=0), 1, 0), 0)
as Monkey-See, Monkey-Do wrote
and copy it down below

in column E1 insert the formula
=IF(A1="FD", IF((B1-C1<0), 1, 0), 0)*ROW()
and copy it down below

in column F1 insert the formula
=ISBLANK(A1)
and copy it down below until value 'TRUE' appears

and then for example in cell G1 insert
=SUM(INDIRECT("D"&IF(COUNTIF(E:E,"0")=1,MAX(E:E), LARGE(E:E,2))),
INDIRECT("D"&MATCH(TRUE,F:F,0)))

--
--
==============================
Janusz Pawlinka

[Max]

Maybe you could give this tinker a shot ..

Source data assumed in A1:C15

In D1: =IF(AND(A1="FD",B1-C1=0),"x",IF(AND(A1="FD",B1-C1<0),"R",""))
Copy down to last row of source data, ie to D15

In E1: =COUNTIF(D$1:D1,"x")
Copy down to penultimate row of source data, ie to E14

Then place in say, F1, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(D1:D15="x",E1:E15))-MAX(IF(D1:D15="R",E1:E15))
to yield the desired result
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows are
updated) sequence happened, and then return the value of the latest sequence.

In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)

Possible?

[Janusz Pawlinka]

little correction:
=SUM(INDIRECT("D"&IF(COUNTIF(E:E,"0")=1,MAX(E:E), LARGE(E:E,2))):
INDIRECT("D"&MATCH(TRUE,F:F,0)))
--
--
==============================
Janusz Pawlinka

[Herbert Seidenberg]

Or this array formula:

=LOOKUP(999,IF(FREQUENCY(IF((bin2-bin3=0)*(bin1="FD"),ROW(bin1)),
IF(bin2<bin3,ROW(bin1)))0,
FREQUENCY(IF((bin2-bin3=0)*(bin1="FD"),ROW(bin1)),
IF(bin2<bin3,ROW(bin1)))))

Variation on a theme by Biff.

[Max]

Herbert,
Besides the OP, I wonder just how many readers out there understand / know
how to implement your suggestion. Perhaps the inclusion of some explanation
lines on assumed sheet set-ups, ranges to be defined etc would certainly
help to support it, for completeness.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Andrew]

Thanks Max, worked perfectly!

"Max" wrote:

Maybe you could give this tinker a shot ..

Source data assumed in A1:C15

In D1: =IF(AND(A1="FD",B1-C1=0),"x",IF(AND(A1="FD",B1-C1<0),"R",""))
Copy down to last row of source data, ie to D15

In E1: =COUNTIF(D$1:D1,"x")
Copy down to penultimate row of source data, ie to E14

Then place in say, F1, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(D1:D15="x",E1:E15))-MAX(IF(D1:D15="R",E1:E15))
to yield the desired result
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows are
updated) sequence happened, and then return the value of the latest sequence.

In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)

Possible?

[Andrew]

Thanks Janusz.

"Janusz Pawlinka" wrote:

little correction:
=SUM(INDIRECT("D"&IF(COUNTIF(E:E,"0")=1,MAX(E:E), LARGE(E:E,2))):
INDIRECT("D"&MATCH(TRUE,F:F,0)))
--
--
==============================
Janusz Pawlinka

[Max]

Glad to hear that. Do spare a moment to click the "Yes" button below, from
where you're reading this.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
Thanks Max, worked perfectly!

[David Biddulph[_2_]]

As you are talking about completeness, Max, please remember when replying to
a message to quote enough of the original message to put your reply into
context.
--
David Biddulph

"Max" wrote in message
...
Herbert,
Besides the OP, I wonder just how many readers out there understand / know
how to implement your suggestion. Perhaps the inclusion of some
explanation lines on assumed sheet set-ups, ranges to be defined etc would
certainly help to support it, for completeness.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Lori]

For an array formula slightly shorter than Herbert's above, maybe try this:

=1/LOOKUP(2,1/FREQUENCY(IF((A1:A15="FD")*(B1:B15-C1:C15=0),
ROW(A1:A15)),IF((A1:A15="FD")*(B1:B15-C1:C15<0),ROW(A1:A15))))

Ctrl+Shift+Entered.

"andrew" wrote:

A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?

[Max]

David,
Out of politeness to Herbert, I was merely following his style of responding
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
As you are talking about completeness, Max, please remember when replying
to a message to quote enough of the original message to put your reply
into context.
--
David Biddulph

[Andrew]

Thanks Lori.

Is it also possible to use your formula to keep track whenever "FD" appears
in the weekly update? I mean, if "FD" appears in A15 but the result was a -
(negative), it will just show 0. If the following 2 weeks, A16 and A17 is
added where "FD" appears as + (positive), the formula counts it as 2. Can
this be done?

"Lori" wrote:

For an array formula slightly shorter than Herbert's above, maybe try this:

=1/LOOKUP(2,1/FREQUENCY(IF((A1:A15="FD")*(B1:B15-C1:C15=0),
ROW(A1:A15)),IF((A1:A15="FD")*(B1:B15-C1:C15<0),ROW(A1:A15))))

Ctrl+Shift+Entered.

"andrew" wrote:

A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?