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?

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?

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?

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" <(E-Mail Removed)> wrote in message
news:67B2B902-F18A-4812-B120-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> wrote in message
> news:67B2B902-F18A-4812-B120-(E-Mail Removed)...
> > 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?
>
>
>

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$11,"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(D115="x",E1:E15))-MAX(IF(D115="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?

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.

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
---

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$11,"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(D115="x",E1:E15))-MAX(IF(D115="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?
>

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
>
>
>