I need to determine the maximum number of consecutive negative numbers in a column. The column contains formulas that evaluate to a positive number, a negative number, zero or "" (blank). I cannot add an additional column. The following are the results of formulas in cells A1:A11 that need a formula in A12 that evaluates to 3, the maximum number of consecutive negative numbers counting cells A2, A6 and A7. 1 -2 "" "" "" -6 -7 8 -9 "" -11

0 |

1/1/2010 9:17:01 PM

You're sure you can't us an extra column somewhere?? Even a hidden one? There are many examples of how to do this published on the internet (search for a phrase like "find sequence of negative numbers in excel" and you'll see them). But almost all involve a 'helper' column. My own solution required a helper column also, and I was looking for one without it. Could you accept a User Defined Function (a VB macro that you can use on a worksheet just like a built in function)? "JP" wrote: > I need to determine the maximum number of consecutive negative numbers in a > column. The column contains formulas that evaluate to a positive number, a > negative number, zero or "" (blank). I cannot add an additional column. The > following are the results of formulas in cells A1:A11 that need a formula in > A12 that evaluates to 3, the maximum number of consecutive negative numbers > counting cells A2, A6 and A7. > > 1 > -2 > "" > "" > "" > -6 > -7 > 8 > -9 > "" > -11 >

0 |

1/2/2010 1:30:01 AM

Try this array formula** : =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "JP" <JP@discussions.microsoft.com> wrote in message news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... >I need to determine the maximum number of consecutive negative numbers in a > column. The column contains formulas that evaluate to a positive number, > a > negative number, zero or "" (blank). I cannot add an additional column. > The > following are the results of formulas in cells A1:A11 that need a formula > in > A12 that evaluates to 3, the maximum number of consecutive negative > numbers > counting cells A2, A6 and A7. > > 1 > -2 > "" > "" > "" > -6 > -7 > 8 > -9 > "" > -11 >

0 |

1/2/2010 3:38:39 AM

Thanks very much for your reply but I can't use a hidden column and I need to avoid user-defined functions and macros. If it is not possible using a formula or array formula, then at least I'll know that and can stop trying--so I appreciate your help. "JLatham" wrote: > You're sure you can't us an extra column somewhere?? Even a hidden one? > There are many examples of how to do this published on the internet (search > for a phrase like "find sequence of negative numbers in excel" and you'll see > them). But almost all involve a 'helper' column. My own solution required a > helper column also, and I was looking for one without it. > Could you accept a User Defined Function (a VB macro that you can use on a > worksheet just like a built in function)? > > > "JP" wrote: > > > I need to determine the maximum number of consecutive negative numbers in a > > column. The column contains formulas that evaluate to a positive number, a > > negative number, zero or "" (blank). I cannot add an additional column. The > > following are the results of formulas in cells A1:A11 that need a formula in > > A12 that evaluates to 3, the maximum number of consecutive negative numbers > > counting cells A2, A6 and A7. > > > > 1 > > -2 > > "" > > "" > > "" > > -6 > > -7 > > 8 > > -9 > > "" > > -11 > >

0 |

1/2/2010 9:50:01 AM

Thank you for your reply. This is the type of solution I've been looking for but it evaluates incorrectly to 2 rather than 3 (using my example). "T. Valko" wrote: > Try this array formula** : > > =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) > > ** array formulas need to be entered using the key combination of > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > key then hit ENTER. > > -- > Biff > Microsoft Excel MVP > > > "JP" <JP@discussions.microsoft.com> wrote in message > news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... > >I need to determine the maximum number of consecutive negative numbers in a > > column. The column contains formulas that evaluate to a positive number, > > a > > negative number, zero or "" (blank). I cannot add an additional column. > > The > > following are the results of formulas in cells A1:A11 that need a formula > > in > > A12 that evaluates to 3, the maximum number of consecutive negative > > numbers > > counting cells A2, A6 and A7. > > > > 1 > > -2 > > "" > > "" > > "" > > -6 > > -7 > > 8 > > -9 > > "" > > -11 > > > > > . >

0 |

1/2/2010 9:57:01 AM

My error, it evaluates perfectly. Thank you so much, T. Valko! I've been working on this for a long time and it's finally solved thanks to you. Best regards, JP "JP" wrote: > Thank you for your reply. This is the type of solution I've been looking for > but it evaluates incorrectly to 2 rather than 3 (using my example). > > "T. Valko" wrote: > > > Try this array formula** : > > > > =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) > > > > ** array formulas need to be entered using the key combination of > > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > > key then hit ENTER. > > > > -- > > Biff > > Microsoft Excel MVP > > > > > > "JP" <JP@discussions.microsoft.com> wrote in message > > news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... > > >I need to determine the maximum number of consecutive negative numbers in a > > > column. The column contains formulas that evaluate to a positive number, > > > a > > > negative number, zero or "" (blank). I cannot add an additional column. > > > The > > > following are the results of formulas in cells A1:A11 that need a formula > > > in > > > A12 that evaluates to 3, the maximum number of consecutive negative > > > numbers > > > counting cells A2, A6 and A7. > > > > > > 1 > > > -2 > > > "" > > > "" > > > "" > > > -6 > > > -7 > > > 8 > > > -9 > > > "" > > > -11 > > > > > > > > > . > >

0 |

1/2/2010 10:03:01 AM

This works perfectly, many many thanks and happy New Year to you. "T. Valko" wrote: > Try this array formula** : > > =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) > > ** array formulas need to be entered using the key combination of > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > key then hit ENTER. > > -- > Biff > Microsoft Excel MVP > > > "JP" <JP@discussions.microsoft.com> wrote in message > news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... > >I need to determine the maximum number of consecutive negative numbers in a > > column. The column contains formulas that evaluate to a positive number, > > a > > negative number, zero or "" (blank). I cannot add an additional column. > > The > > following are the results of formulas in cells A1:A11 that need a formula > > in > > A12 that evaluates to 3, the maximum number of consecutive negative > > numbers > > counting cells A2, A6 and A7. > > > > 1 > > -2 > > "" > > "" > > "" > > -6 > > -7 > > 8 > > -9 > > "" > > -11 > > > > > . >

0 |

1/2/2010 10:05:01 AM

I finally understand how this is working. It's a brilliant and elegant solution that is greatly appreciated. Thanks once again, Biff and happy trails. JP "T. Valko" wrote: > Try this array formula** : > > =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) > > ** array formulas need to be entered using the key combination of > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > key then hit ENTER. > > -- > Biff > Microsoft Excel MVP > > > "JP" <JP@discussions.microsoft.com> wrote in message > news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... > >I need to determine the maximum number of consecutive negative numbers in a > > column. The column contains formulas that evaluate to a positive number, > > a > > negative number, zero or "" (blank). I cannot add an additional column. > > The > > following are the results of formulas in cells A1:A11 that need a formula > > in > > A12 that evaluates to 3, the maximum number of consecutive negative > > numbers > > counting cells A2, A6 and A7. > > > > 1 > > -2 > > "" > > "" > > "" > > -6 > > -7 > > 8 > > -9 > > "" > > -11 > > > > > . >

0 |

1/2/2010 1:32:01 PM

You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JP" <JP@discussions.microsoft.com> wrote in message news:A5D8D2FD-C20A-4915-A3BA-154735D00967@microsoft.com... >I finally understand how this is working. It's a brilliant and elegant > solution that is greatly appreciated. Thanks once again, Biff and happy > trails. > > JP > > > "T. Valko" wrote: > >> Try this array formula** : >> >> =MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11>=0,IF(A1:A11<>"",ROW(A1:A11))))) >> >> ** array formulas need to be entered using the key combination of >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the >> SHIFT >> key then hit ENTER. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "JP" <JP@discussions.microsoft.com> wrote in message >> news:29043AFA-6F71-42EB-992A-2AA4547BAFEF@microsoft.com... >> >I need to determine the maximum number of consecutive negative numbers >> >in a >> > column. The column contains formulas that evaluate to a positive >> > number, >> > a >> > negative number, zero or "" (blank). I cannot add an additional >> > column. >> > The >> > following are the results of formulas in cells A1:A11 that need a >> > formula >> > in >> > A12 that evaluates to 3, the maximum number of consecutive negative >> > numbers >> > counting cells A2, A6 and A7. >> > >> > 1 >> > -2 >> > "" >> > "" >> > "" >> > -6 >> > -7 >> > 8 >> > -9 >> > "" >> > -11 >> > >> >> >> . >>

0 |

1/2/2010 4:12:16 PM

As you now know, there's almost always a solution (key word 'almost'), and in this case I just kind of figured if Biff couldn't do it, it couldn't be done. Glad he was able to solve your problem. "JP" wrote: > Thanks very much for your reply but I can't use a hidden column and I need to > avoid user-defined functions and macros. If it is not possible using a > formula or array formula, then at least I'll know that and can stop > trying--so I appreciate your help. > > "JLatham" wrote: > > > You're sure you can't us an extra column somewhere?? Even a hidden one? > > There are many examples of how to do this published on the internet (search > > for a phrase like "find sequence of negative numbers in excel" and you'll see > > them). But almost all involve a 'helper' column. My own solution required a > > helper column also, and I was looking for one without it. > > Could you accept a User Defined Function (a VB macro that you can use on a > > worksheet just like a built in function)? > > > > > > "JP" wrote: > > > > > I need to determine the maximum number of consecutive negative numbers in a > > > column. The column contains formulas that evaluate to a positive number, a > > > negative number, zero or "" (blank). I cannot add an additional column. The > > > following are the results of formulas in cells A1:A11 that need a formula in > > > A12 that evaluates to 3, the maximum number of consecutive negative numbers > > > counting cells A2, A6 and A7. > > > > > > 1 > > > -2 > > > "" > > > "" > > > "" > > > -6 > > > -7 > > > 8 > > > -9 > > > "" > > > -11 > > >

0 |

1/2/2010 11:19:01 PM

I am trying to print an entire workbook and would like the page numbe to be specific to each worksheet set. For example, my workbook contains 7 sheets, each of which has a uniqu header that contains the command for Page X of X Pages. Each shee contains multiple pages. If I print the entire workbook, I can get the printout for each shee to begin with a 1 by manually setting the First Page setting to "1" i Page Setup. No problem there. However, my question is...Can I control the command for "X Pages"? Fo example, when I print the entire workbook, I'd like the first ...

I need to find out how to incorporate each document’s total page count into the Table of Contents instead of the current page number. In my case, we have 20-60 separate document files that need a single TOC that states the total number of pages for each file. Example: Section No. of Pages 100 3 200 2 300 10 I forgot to mention, I am using MS Word 2007 "Krystal" wrote: > I need to find out how to incorporate each document’s total page count into > the Table of Contents instead of the current page number. > > In my case, we have 20-60 s...

Hi I use publisher to print some labels at work. It would be really useful if I could print each label with a unique and possibly sequential number. Is this possible using MS publisher, or even MS Word? Thanks Andy. Hi Andy, Yes, you can do this using Microsoft Word or Publisher. What you need to do is create a database of numbers, such as using Excel, and then using the Mail Merge features in Microsoft Word/Publisher, you can merge those numbers to each label. Visit the training section of my site to learn more about merging onto labels/business cards using a database. Brian K...

I have a spreadsheet where I enter weights. The weights can be any whole number 1-99999. I would like to be able to flag suspicious weights using conditional formatting by putting a letter as the last character, such as 123a, or 57b, 4471c, etc.What conditional formatting formula can I use to accomplish this? Thanks, Tonso Assuming your weights are in column A, starting with A2, highlight the cells in that column with A2 as the active cell, and use this formula in the CF dialogue box: =3DAND(CODE(UPPER(RIGHT(A2)))>=3D65,CODE(UPPER(RIGHT(A2)))<=3D90) Then click on the Format button a...

i have looked a little but can't find what i think is the right thing. i have a workbook with 3-4 pages in it. in the upper righthand corner is a page of pages cells set up. i want to have it grab the current tab(page) for one cell and the rest of the tabs(pages) for the other cell. i am not very good at the coding VB thing so exact instructions would be grately appreciated. i have tried a few of the suggestions but cannot get them to work. i am using excel 2002. TIA ...

why is it, that if i incert a number (say 0.8) into a cell in excel another number comes out instead of my original number(say 0.08) tools, options, edit (tab), remove check from fixed decimal places -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "excel man" <excel man@discussions.microsoft.com> wrote in message news:6B3D4586-C5D9-41F0-8D9A-94F0E959CD0C@microsoft.com... > why is it, that if i incert ...

I am probably making this harder than it needs to be. What is a good way to test if a user enters a whole number as a data input. I cobbled together the following but is there a simplier way? Sub Test() Dim pInt As Integer Dim pStr As String Do pStr = InputBox("Enter a whole number") pInt = pStr Loop While pInt <> pStr MsgBox "Thank you" End Sub -- Greg Maxey See my web site http://gregmaxey.mvps.org for an eclectic collection of Word Tips. Arrogance is a weed that grows mostly on a dunghill (Arabic proverb) I am sure there ar...

HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

How to create an ongoing addition of numbers in an excell chart. Or sum of Numbers I should Say. I need the info for work. I think there is an easier way than how I am doing it. Worksheets are the things to use for adding numbers. That's not what a chart is designed to do. -- David Biddulph "sanicay" <sanicay@discussions.microsoft.com> wrote in message news:E94E1035-DC2C-4698-AC32-3439BDB2BDA6@microsoft.com... > How to create an ongoing addition of numbers in an excell chart. Or sum of > Numbers I should Say. I need the info for work. I think there is an >...

I'm having difficulty with a query and hope someone can help me out. Basically, I'm looking to select the maximum amounts from a table based on a column in one of the tables named StateID. It works fine when I do this: SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS Amount, dbo.States.StateName FROM dbo.Bids INNER JOIN dbo.States ON dbo.Bids.StateID = dbo.States.StateID GROUP BY dbo.Bids.StateID, dbo.States.StateName ORDER BY dbo.States.StateName However, when I start to join fields from other tables in...

Hello, I'm trying to figure out how to 'unstick' a working form/report/table, etc in Access. Even if I design an entry form at say, 3" by 5" wide, it'll automatically maximize to fill up the entire working screen next to the navigation bar. To my knowledge, I have never set any property to maximize every window. . . Did I miss a global setting, or is it an option I can turn off? Thanks, Tripler "Tripler of the SDMB" <Tripler of the SDMB@discussions.microsoft.com> wrote in message news:96E65DF9-A187-4710-A6AC-2350A66F38D1@microso...

Is there a way to delete an unused check number not entered into the system? We used a check for a manual payment, but ended up voiding the check as it was not needed. Since the check was never printed, this check number does not appear in the system so I can't void it. But the physical check itself is obviously not usable now. Thanks for any help. Melissa, The only thing I can think of is entering a manual payment for $0 to use up this check number. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "melissa" <melissa@discussions.microsoft.com> wrote in ...

Is there a way to have Excell 'spell out' a numeric entery in a cell, for example: '1,234' into 'one thousand two hundred thirty-four'? I am using a Chinese version, in the format menu it gives a "Basic number format code" [DBNum2] to spell out in Chinese. I need them in English. English version user can highlight a cell, go to Formats -- Cells -- Number -- Special and see if there is a setting for that. Select the setting, then click Custom in the Category menu on the left and the "code" will be shown on the right in the "type"...

Is there any way of making page numbering start at a different number? I created a 16 A5 page document, the first two and last two sheets were a cover page which I printed separately. When I printed pages 3-14 I was asked if I wanted the pages to be printed as a separate document and I replied YES. However the page numbering on this separate document started at page 3 - I would have preferred page 1. The obvious solution of deleting pages 1,2,15,16 did not work. When I deleted pages 1 and 2 the text moved on leaving blank pages. I realise now that the text of the document was a continuous s...

I have a colum with numbers for specific products (example 111,123456) and I want a picture in the colum to the right of it that matches the number. On the computer I have a folder with pictures named with the product numbers. So my question is: Is there a function to link the the number in for example cell A1 with the picture that has the same name as the value in A1 and put that picture in cell B1. Thanks to whom ever have an answer, it would help my life. Best Regards Dennis Hi Dennis Have a look at this site : http://www.mcgimpsey.com/excel/lookuppics.html HTH John...

I have a sheet which has data with date & time fields like this 19/10/2005 10:10 19/10/2005 11:30 19/10/2005 12:12 19/10/2005 12:15 19/10/2005 10:12 How do I make a formula to pull out count between each half hour slot? like: 10:00 - 10:30 =2 10:30 - 11:00 =0 11:00 - 11:30 =1 11:30 - 12:00 =0 12:00 - 12:30 =2 Please help. Cheers! Sunny =sumproduct(--(B1:B100>--"10:00:00"),--(B1:B100<=--"10:30:00")) -- HTH RP (remove nothere from the email address if mailing direct) <sunilkeswani@gmail.com> wrote in message news:1131569006.191782.216510@g44g200...

Have created an Invoice and am trying to generate a continous invoice number every time I type a new name. See this... http://mcgimpsey.com/excel/udfs/sequentialnums.html -- Biff Microsoft Excel MVP "Ed" <Ed@discussions.microsoft.com> wrote in message news:4790979B-FE32-4FED-A7B5-DD15C1B634A7@microsoft.com... > Have created an Invoice and am trying to generate a continous invoice > number > every time I type a new name. hi that can get tricky. see this site. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html regards FSt1 &qu...

How do I add the % sign to a existing number without changing the value? Example: 1.68 should show 1.68% not 168? Select the cell & press Ctrl-1 Navigate to the Number tab Select Custom in the left hand panel In the Type edit box enter 0.00"%" Click on OK "sweetsue516" wrote: > How do I add the % sign to a existing number without changing the value? > Example: 1.68 should show 1.68% not 168? Sue, Percentage-formatted cells are based on 1 for 100%. It's only when you key in the percentage that it's converted, for example you key 1.68%, and it...

Hi, We have a user here that have a lot of rules activated in the Rules Wizard. When he try to add a new rule, he get an error saying that the rule cannot be created and that Outlook might not handle more rules. Is it possible to know the maximum of rules allowed to be activated in Outlook XP (2002)? Thanks in advance for your help. Best Regards Martin If you use Exchange server, you are limited to 32k of server-side rules - this is not configurable. Simplify the rules or get rid of some. Martin Cote wrote: > Hi, > We have a user here that have a lot of rules activated &...

I have received a file that has a date column. However, the column is reading as text. I need to change the column so that the date reads as a number format. Any ideas? -- montagu ------------------------------------------------------------------------ montagu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15227 View this thread: http://www.excelforum.com/showthread.php?threadid=314927 Right click and reformat as you want then click on cell and press F2 an enter to refresh data that should do the tric -- scottymellot --------------------------------------...

My apologies if anyone responded to this when I originally posted. I didn't download enough headers and I can't recapture the original.... I want to highlight any row that has the same data as the previous row. For example, if A:3=A:2 and B:3=B:2 and C:3=C:2, I want to highlight Row 3. I also want to repeat this logic throughout the spreadsheet. Can I do with this with Conditional Formatting? Or, would I need to use VBA...in which case, how would I code this? Thanks in advance. I have no idea if anyone responded to your earlier post, but you could check yourself: You can ...

Hi, I have a dataset coming back from an application screen where I have to check that the values in a set of fields are in ascending order. Is there an easy way to do this in SQL? I've written the following code using a WHILE loop (which works), but wondered if there was a better way of doing this i.e. using a CTE or similar? Here's the temp table that I've created to hold the values with an added row number: row_number row_id entry_value 1 101965 23000.00 2 101966 24000.00 3 101967 22000.00 ...

I have hunted for this without success. I want to know - programmatically - what page the active cell is in. More precisely, what page ActiveCell.Top is in. (I take it some sows can split into more than one page.) I want to automate putting manual page breaks into a sheet. I would put breaks at or before those automatically inserted by Excel. Thanks -- Walter Briscoe In message <KBH7nVN7dSuJFwvS@freenetname.co.uk> of Thu, 12 Mar 2009 15:17:15 in microsoft.public.excel.newusers, Walter Briscoe <wbriscoe@nospam.demon.co.uk> writes >I have hunted for this without success. >...

Hello All Can some one please show me how to find the absolute maximum (x & y) from a 4th order polynomial equation... Excel tells me the equation is; Y=-0.007x�+0.0321x�-0.4409x+3.6696 The equation was derived from the following x,y points; (12.27,1.721),(14.43,1.762),(17.03,1.808),(19.22,1.790) I know the absolute maximum is approx (17.6,1.810) by physically graphing and then reading of the max point but is there any way Excel can calculate this for me? Any thoughts appreciated, thanx in advance Matt ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Bus...

Have the following fields in a table: Date,Name,JobNo,Hours. I need a SQL query to count the number of days each Name appears over a period of days. Any Name can appear more than once on any Date. If a Name appeared three times on the same Date it should only count as one day. Any suggestions or help would be appreciated Thanks Phil_mac SELECT name, COUNT(*) FROM (SELECT DISTINCT name, [date] FROM tableName WHERE [date] BETWEEN firstDate AND lastDate) AS a GROUP BY name where firstDate and lastDate are parameters defining the range of date of your interest. ...