Flagging a particular cell ?

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

As numbers are added and subtracted in a column of cells over time,
the sum of these cells changes.
I should like to discover a formula which would flag a
particular cell (or point in time) when that sum fluctuates by a
given amount (10 in this example) over the last flagged cell.
Example:

cell|data|sum|flag
1) 0 0
2) 3 3
3) 4 7
4) -2 5
5) 5 10 FLAG
6) 6 16
7) 9 25 FLAG
8) -3 22
9) -8 14 FLAG
etc.

The formula I am looking for would examine the sum column and would
flag a variance of 10 or more from the last flagged cell.
Is this possible? Any ideas?

Thanks,
Les
____________________________________
My email address has an unnecessary E.
 
Hi Les

Not fully sure I get your needs but try

=IF(MAX(A1:B1)-MIN(A1:B1)>=10,"Flag","")

*****Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum & Business Software*****
 
Hi Les
If I understood you correctly and your data is in column B (starting in
row 1) insert the following in column C / row 2 (C2):
=IF(ABS(B2-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C1,0)),0,MATCH("FLAG",
$C$1:C1)-1),0))>=10,"FLAG","")
and copy down
this tests the value of the current row either with the value in row 1
('0' in your example) or with the lasts flagged row
 
Frank,
I think you do understand me! Your formula is almost there. Apply it to the
following example:

cell|data|sum|flag
1) 0 0
2) 3 3
3) 4 7
4) -2 5
5) 5 10 FLAG (yourFLAG)
6) 6 16
7) -2 14
8) 8 22 FLAG
9) 3 25
10) -1 24 (yourFLAG)

The flag should be found on rows 5 and 8. Your formula finds row 5 but
misses until row 10. There are too many nested functions for me to follow.
But you definately have the right idea. I am amazed. Try one more time.
Thanks,
Les
 
Thanks for taking the time to reply Dave.
Your formula will find the change between two adjacent cells, but suppose
the change to + or -10 takes 2 or 3 or more entries. The formula has to have
a way of finding the last flagged cell.
Les
 
Hi Les
I tried the formula on these data and I got exactly the results you
wanted?
- can you post the formula you applied in this 'Flag column'. You may
copy the formula from row2, row 5 and row 8 so I can check this error
 
Frank,
This is the formula from row 2 (which I copied from your post):
=IF(ABS(B2-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C1,0)),0,MATCH("FLAG",$C$1:
C1)-1),0))>=10,"FLAG","")

This is the formula from row 5 (where the first 'flag' occurs):
=IF(ABS(B5-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C4,0)),0,MATCH("FLAG",$C$1:
C4)-1),0))>=10,"FLAG","")

Formula from row 8 (where a flag should be but no 'flag' appears):
=IF(ABS(B8-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C7,0)),0,MATCH("FLAG",$C$1:
C7)-1),0))>=10,"FLAG","")

Formula from row 10 (where the second 'flag' occurs):
=IF(ABS(B10-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C9,0)),0,MATCH("FLAG",$C$1
:C9)-1),0))>=10,"FLAG","")

I am learning alot about 'vlookup', 'match', and 'offset' functions from
this exercise. Thanks for your help.
Les
PS. I am emailing to 'frank.kabel[at]freenet.de' a copy of my worksheet.
 
Hi Les
if you can accept flagging the entries with the number '1' (and not a
Text) use the following formula in C2
=IF(ABS(B2-OFFSET($B$1,IF(ISNA(MATCH(1,$C$1:C1,0)),0,MATCH(1000,$C$1:C1
)-1),0))>=10,1,"")
and copy down
You may use a custom format to show this value as a text. Select the
column C and goto 'Format - Cells' and enter the following custom
format
"FLAG";;

--
Regards
Frank Kabel
Frankfurt, Germany
Frank,
This is the formula from row 2 (which I copied from your post):
=IF(ABS(B2-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C1,0)),0,MATCH("FLAG",
$C$1:
C1)-1),0))>=10,"FLAG","")

This is the formula from row 5 (where the first 'flag' occurs):
=IF(ABS(B5-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C4,0)),0,MATCH("FLAG",
$C$1:
C4)-1),0))>=10,"FLAG","")

Formula from row 8 (where a flag should be but no 'flag' appears):
=IF(ABS(B8-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C7,0)),0,MATCH("FLAG",
$C$1:
C7)-1),0))>=10,"FLAG","")

Formula from row 10 (where the second 'flag' occurs):
=IF(ABS(B10-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C9,0)),0,MATCH("FLAG"
,$C$1
C9)-1),0))>=10,"FLAG","")
Click to expand...

I am learning alot about 'vlookup', 'match', and 'offset' functions
from this exercise. Thanks for your help.
Les
PS. I am emailing to 'frank.kabel[at]freenet.de' a copy of my
worksheet.

--------------------------------------------------------------------- -------
-------------------

Frank Kabel said:
Hi Les
I tried the formula on these data and I got exactly the results you
wanted?
- can you post the formula you applied in this 'Flag column'. You may
copy the formula from row2, row 5 and row 8 so I can check this
error

--
Regards
Frank Kabel
Frankfurt, Germany

=IF(ABS(B2-OFFSET($B$1,IF(ISNA(MATCH("FLAG",$C$1:C1,0)),0,MATCH("FLAG",
Click to expand...
Click to expand...
 
Back
Top