count the number of new lows within a range of cells

Hello,
Is there a formula or method to count the number of new lows within a
range of cells?  For example, the following are in col A rows 5 to 14.
Within this set a new low has been reached 3 times- (96,95,94).  What I
would like is a formula or macro that that would produce the results -
3.  There are about 9000 rows of data and I am looking for "count of
new lows within the preceding 10 rows" on a rolling basis.

Thanks.
100
101
96
97
98
97
95
97
94
95

0
jbesr1230 (21)
6/1/2006 4:01:41 AM
excel 39879 articles. 2 followers. Follow

2 Replies
512 Views

Similar Articles

[PageSpeed] 51

Version 1:
Use this if the 9000 or so rows don't have blanks in them.
In this version you just need to select the first cell and run the
macro. (so in your example case you will be selecting A5)


Code:
--------------------
        Dim i, r, c, min, count, range As Integer
  
  
  r = Selection.Row
  c = Selection.Column
  range = 10      'the number of rows to look at one time
  
  While Len(Cells(r, c).Text) > 0
  i = 0
  count = 0
  min = Cells(r, c).Value
  For i = 0 To (range - 1)
  If Cells(r + i, c).Value < min Then
  min = Cells(r + i, c).Value
  If min < Cells(r + i + 1, c).Value Then
  count = count + 1
  End If
  End If
  r = r + 1
  Next i
  MsgBox count
  Wend
--------------------



If they do have blanks then you can use version 2 but you will need to
know how many rows you have in total:
Version 2:

Code:
--------------------
    Dim j, i, r, c, end, min, count, range As Integer
  
  
  r = Selection.Row
  c = Selection.Column
  range = 10      'the number of rows to look at one time
  end = 9000          ' the number of rows you want to look at in total
  
  For j = 1 to end
  i = 0
  count = 0
  min = Cells(r, c).Value
  For i = 0 To (range - 1)
  If Cells(r + i, c).Value < min Then
  min = Cells(r + i, c).Value
  If min < Cells(r + i + 1, c).Value Then
  count = count + 1
  End If
  End If
  r = r + 1
  Next i
  MsgBox count
  Next j
--------------------


-- 
schoujar
------------------------------------------------------------------------
schoujar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26574
View this thread: http://www.excelforum.com/showthread.php?threadid=547315

0
6/1/2006 5:18:19 AM
jbesr1230 wrote:
> Hello,
> Is there a formula or method to count the number of new lows within a
> range of cells?  For example, the following are in col A rows 5 to 14.
> Within this set a new low has been reached 3 times- (96,95,94).  What I
> would like is a formula or macro that that would produce the results -
> 3.  There are about 9000 rows of data and I am looking for "count of
> new lows within the preceding 10 rows" on a rolling basis.
>
> Thanks.
> 100
> 101
> 96
> 97
> 98
> 97
> 95
> 97
> 94
> 95

Worksheet version:
First of all, to keep the formula entry simple, insert 9 rows before
your first entry.
In the same column as your first entry, enter the value of the first
entry in all 9 cells. Best way is to select all 9, type the value and,
while holding <ctrl> type <enter>.
I'll use column A as your values, B as a test for minimums and C as the
count of local minimums.
Into B10, enter the formula =IF(A10<MIN(A1:A9),1,0)
Into C10, enter the formula =SUM(B1:B10)
Now copy these from row 11 down to your last value's row.
To clean it up, copy the range from B10 to C18 and then paste those
values back into the same range. Now you can delete rows 1 through 9.
As you add new values to the end of your list, copy the two formulas
down from the preceding row.

0
naeyaert (21)
6/1/2006 2:43:34 PM
Reply:

Similar Artilces:

I need a message if number of duplicate records exceeds number allowed
Thanks in advance for any help. I'm trying to create If DCount code on my form that will count the number of duplicate records and then notify me when the number of duplicate records exceeds the number allowed. The number of duplicates allowed is established in tblFloorProgCriteria in the field FloorProgMaxObservations. Here's what I have so far...It's giving me the message as soon as I attempt to add the first record. If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & " And [FloorProgCriteriaID] = "...

Page number functions in Word are dimmed and don't work. What now
I use Office Home & Student 2007. I cannot insert page numbers into my Word document. I could not find Microsoft Office's "Add-ins" either. What can I do to restore the page numbering function? Place your cursor in a header or footer. On Feb 8, 7:19=A0pm, A Tai <a...@discussions.microsoft.com> wrote: > I use Office Home & Student 2007. =A0I cannot insert page numbers into my= Word > document. =A0I could not find Microsoft Office's "Add-ins" either. =A0Wha= t can I > do to restore the page numbering function? A page number...

Determine cells that drive conditional formatting?
Example: Cells A1:A4 have conditional formatting set up that states if they are equal to cell A10 they will be highlighted yellow. Is there a way to quickly see what cells drive conditional formatting? In other words, we can use Edit -> Go To Special to see which cells have conditional formatting applied; however, this doesn't show us that A10 is involved. Since there isn't a formula directly involved, we can't trace precedents/dependents. Can we only know that A10 is involved in the formatting of A1:A4 by selecting those cells and going into the conditional format...

Count "," in a cell
Dear expert, Is it possible to count "," in a cell please? I used this ... but does not work =COUNTIF(FO93,",") Say below ... can it be solved? 2,5,3 22,25,5 5,2,3,4 Try this… =LEN(FO93)-LEN(SUBSTITUTE(FO93,",","")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Elton Law" wrote: > Dear expert, > > Is it possible to count "," in a cell please? > > I used this ... but does not work > > =COUNTIF(FO93,",&qu...

unknown range
I want to write a macro that selects a range that starts in A3 and ends in Jsomewhere. Is it possible to refer to an unknown cell in an known column? It might be nice to know what determines the "unknown" cell. =offset($a$3,0,0,counta($J:$J),10) could be a defined name range to find the last cell in J based on data in J -- Don Guillett SalesAid Software dguillett1@austin.rr.com "fanny" <f.klompsma@chello.nl> wrote in message news:76373$45b0fa73$3ea35143$7359@news.chello.nl... >I want to write a macro that selects a range that starts in A3 and ends in >J...

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

text to fit in cell
How do I make the text fit in a cell. I want to be able to print the page with all of the words in the cell even if it has to make the cell larger, without going over into the next cell. How do I do this? Hi goto 'Format - Cells - alignment' and check 'Wrap text' -- Regards Frank Kabel Frankfurt, Germany DaveB wrote: > How do I make the text fit in a cell. I want to be able > to print the page with all of the words in the cell even > if it has to make the cell larger, without going over > into the next cell. How do I do this? Dave Format>Cells>Alignm...

Totaling cells from separate worksheets to master form
If I have 2 separate worksheets with individual cell values and want the total to show the sum of obth cells, how do I do it? Nelson Suppose those values are in F10 on one sheet and in G6 of the other sheet, then put this in the appropriate cell of your master sheet: =3DSheet1!F10 + Sheet2!G6 to add them both together. If your actual sheet names contain spaces you will need to include apostophes around the sheet name, like: =3D'First Sheet'!F10 + 'Second Sheet'!G6 Hope this helps. Pete On Apr 21, 10:31=A0pm, snake941 <snake...@discussions.micros...

Why does Excel in XP create new files?
Excel adds duplicate files - Assist Log (2).xle, asssits Log (3).xls. Why? Can I make this stop. It appears all the files are identical - so if I just delete the extra files will I be losing any data? hi, I think this is something new in xl relating to thier file recovery during a GPF shutdown. but i keep getting it even without a GPF. when i save, excel will just up and ask me which file i want to save leaveing me sitting there asking "what do you mean which file. i only have one." it's getting anoying. >-----Original Message----- >Excel adds duplicate files - ...

How do I change headings for columns from numbers to letters? #2
...

can't copy text to new pub document
I am trying to copy entire pages from one Publisher 2007 doc to another. It will copy the text in some text boxes but not in others. I don't understand why this is happening. How do I get it to copy the entire page as is? I am using Ctrl A or Select All. Thank you. Oh, another thing to add: Even if I select only a single text box, it will not copy any text. "lindalou" wrote: > I am trying to copy entire pages from one Publisher 2007 doc to another. It > will copy the text in some text boxes but not in others. I don't understand > why this is happening. How d...

Format Excel cells
Anyone could help me to solve this problem : How to format the selected cells so that it allows other users to "paste values" only(number from 0 to 100) to the cells. It rejects "paste" function which put formula, format etc into the cells. Thanks a million ! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreade...

cells changing from number to date
I am putting a number in a cell, then tabbing to the next cell, the previous cell changed from a number to a date. How do I keep the number there? What are you inputting, a fraction?if so, format as # ??/?? -- HTH RP (remove nothere from the email address if mailing direct) "mwhite@srgmac.com" <mwhite@srgmac.com@discussions.microsoft.com> wrote in message news:E71CED6D-8370-44D1-A4BF-9A6B352205F3@microsoft.com... > I am putting a number in a cell, then tabbing to the next cell, the previous > cell changed from a number to a date. How do I keep the number there? ...

How do I search for a telephone number in Contacts
How do I search for a telephone number in a sub-folder of Contacts? When I try a search for a number, that I know is there, it comes up blank. Regards, Peter Ledden ...

Number lock key on the computer key pad
Hi, I have come across a weird situation with respect to number lock key. Before the transaction I make sure that the number lock key is ON, then I scan few items, and after I finish the transaction I find the number lock key is automatically turned off. So on next transaction I have to manually turn it on to punch the tender amount. I have tried rebooting the computer, resetting the key pad, resetting the scanner, but none helps. Has anyone else encountered this condition, and is there any fix for that? Thanks, Vaidehi Vaidehi, Never seen it happen. Here's a link to always tur...

Problem
i all I am having a strange issue with my excell file since a while. This is the second year I am using it without problem. I start a workday with a new sheet so every morning I just copy (Edit/Move or Copy Sheet) the latest sheet, rename it and use it. All of a suddent when I perform this operation I have a prompt that says "a formula or sheet you want to move or copy contains the name 'aaa', which exists in the destination worksheet. Do you want to use this version of the name?" I would hit yes to proceed, then I would get over 50 other prompts with differ...

Empty cell in vlookup
hi, is there a way to have a vlookup see an empty cell and rather than filling it in with a 0 it could leave it blank? here's my vlookup formula =IF(A21="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)) Thank you in advance for your help :) =IF(A21="","",if(VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE))) -- Please click "yes" if this post helped you! Greatly appreciated Eva "confused" wrote: ...

How do I return to prior cell?
After I have traced a formula using Ctrl+[, what is the keyboard shortcut to return to the same cell where I traced the formula? Thank you! Press Ctrl+] Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "NYC18" wrote: > After I have traced a formula using Ctrl+[, what is the keyboard shortcut to > return to the same cell where I traced the formula? > > Thank you! Thank you, but I can only get Ctrl+] to work if all the referenced cells are within one worksheet. If I am writing formulas usi...

Batch Naming of ranges
Dear all, There are 80 sheets in my workbook. I want to name the ranges A1:B20 with systematic names in these 80 sheets, say, student_01 for Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I name these ranges easily? Thanks. Best Regards, Andy Dim i As Long For i = 1 To 80 Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" & Format(i, "00") Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Chan" <chankhandy-msnewsgroup...

thousand separator as indian style for negative number
hi every body we in india to any number put comma as stated below ie 15,13,10,565.00 fifteen crores thirteen lacs ten thousand five hundred sixty five only 00,00,00,000.00 but excel we have only thousand separator.it separates the number afte every thousand. can it possible in excel to formate number as per our style. 00,00,00,000.00 for both + and - numbers. if possible please guide me kamlaka -- kamlaka ----------------------------------------------------------------------- kamlakar's Profile: http://www.msusenet.com/member.php?userid=328 View this thread: http://www.ms...

Linking Non-Contiguous cells
I am trying to link different rows of data in Excel to a Visio diagram as labels. In Excel I have a document that has a header row with a multiple rows of data following. Each picture on my Visio diagram corresponds to a different row on the excel worksheet. So to link a lable to each picture in Visio I need the first row(header row) and the corresponding data row. In all but one, these rows are not going to be next to each other. I've tried to highlight the header row and the data row and use copy, paste special in Visio and it pastes every row between them instead of just the...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Excel range truncates when Pasted as Picture to PPT & Word
Hi... I have been trying to copy an Excel Spreadsheet into PPT, but have had problems. In order to solve it, I created new .xls and .ppt files to create a test, but got the same problems... The following steps recreate the problem: 1) In a blank spreadsheet, I placed a single number in each cell, starting at A1 and going across to AS, until there are 1 through 45 across. Format them in some way... say Red text with an underline. 2) Set the width of all the columns to 2.00 (0.11 inches). 3) Select A1:AS 4) Copy (or Add to Scrapbook) -- The result is the same with both. 5) From Scrapb...

sorting two columns of merged cells
I have two columns of merged cells. two cells in each column are merged in each row: ie A1 and B1 are merged into one cell, a2 and B2, etc. The next column is the same; C1 and D1 are merged, C2 and D2, etc. Is there any way I can sort these columns? I need a descending sort by col A and B. Im using Excel 2003 Hope this is clear. Jim Don't merge cells. Look in the archives of this group for countless reasons why not; you've just found one of them. -- David Biddulph "bigjim" <bigjim@discussions.microsoft.com> wrote in message news:...

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009601098-9 ...