Find specific condition in a range

I have a column showing a calculated dollar amount. The amount changes based 
on conditions entered elsewhere in the spreadsheet.
The values start at a positive number, decrease (calculated based on a value 
entered elsewhere) until they get to a negative number (and keep decreasing 
past that point, i.e. lower negative numbers).
How can I return the cell that has the last positive number (or the first 
negative number)?
0
Utf
1/16/2010 10:26:01 PM
excel 39879 articles. 2 followers. Follow

2 Replies
797 Views

Similar Articles

[PageSpeed] 25

In A1`:A21 I have 10, 9,8 .... 1, 0, -1 ... -8,-9.-10
This formula =MATCH(MIN(ABS(A1:A21)),A1:A21,0) returns position (11) of the 
smallest absolute value (here 0)
And =INDEX(A1:A21,MATCH(MIN(ABS(A1:A21)),A1:A21,0))
this the actual value (zero)
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"TP" <TP@discussions.microsoft.com> wrote in message 
news:6EF23AE8-9DEB-49AB-AE5A-08CC854E7431@microsoft.com...
> I have a column showing a calculated dollar amount. The amount changes 
> based
> on conditions entered elsewhere in the spreadsheet.
> The values start at a positive number, decrease (calculated based on a 
> value
> entered elsewhere) until they get to a negative number (and keep 
> decreasing
> past that point, i.e. lower negative numbers).
> How can I return the cell that has the last positive number (or the first
> negative number)? 

0
Bernard
1/16/2010 11:04:07 PM
All of these formulas are array formulas**.

For the last positive:

=INDEX(A1:A10,MATCH(TRUE,A1:A10<0,0)-1)

For the first negative:

=INDEX(A1:A10,MATCH(TRUE,A1:A10<0,0))

If the numbers follow a descending sequence:

For the last positive:

=MIN(IF(A1:A10>=0,A1:A10))

For the first negative:

=MAX(IF(A1:A10<0,A1:A10))

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


"TP" <TP@discussions.microsoft.com> wrote in message 
news:6EF23AE8-9DEB-49AB-AE5A-08CC854E7431@microsoft.com...
>I have a column showing a calculated dollar amount. The amount changes 
>based
> on conditions entered elsewhere in the spreadsheet.
> The values start at a positive number, decrease (calculated based on a 
> value
> entered elsewhere) until they get to a negative number (and keep 
> decreasing
> past that point, i.e. lower negative numbers).
> How can I return the cell that has the last positive number (or the first
> negative number)? 


0
T
1/16/2010 11:48:08 PM
Reply:

Similar Artilces:

keeping conditional formatting after paste
I am making a large spreadsheet that others will use and they will have to paste large amounts of data onto the spreadsheet. The issue is that when they paste their data the conditional format that I set up for them is removed. Is there a way to lock the format in place and let them paste still? I would be happy with protecting the sheet and allowing paste to still be an option. Instead of simple 'paste' try using 'paste special' > 'values'. This will then only paste the copied value into the new cell's format. Hope this helps "ericahug...

Excel: Name Range Values Reset After Refresh Data + ReCalc
Whenever I refresh data (Data->Refresh Data) the name range values are reset from, e.g., $A$1:$A$65534 to $A:$A, and then the array formulas in which the name is used won't recalc until I manually fix the values. Any idea how to stop these from changing? ...

Help With Multiple Conditional Sum
Hi, everyone... Building formulas is not yet a strong area of mine. I'll keep it concise. The scenario: Given: Columns A, B and D, respectively: Vendor, Date, Amount. I want to build a table of annual sums as follows: Columns: G, H-Q, respectively: Vendor, 1998-2006 Each vendor, then, will be listed once in the Vendor column (G). I should be able to scan across columns H-Q for each vendor row and see how much I spent at any given vendor in any given year. Any working solutions would be greatly appreciated. Thanks, KZ In H1:Q1, input the year numbers In G2:Gn, add all vendor ids ...

lookup view (find columns) doesn't work for "send email" functionality
When I add new find columns (p.e. parent customer) to my lookup view of my contacts, everything works just fine for lookups... But i recently discovered that this doesn't work if you p.e. open up an account record; you click the button "send email" on the toolbar (or just when you create a new email activity) , which will open a new email screen in MS CRM; and if you then click on the magnifying glass for the to;cc and/or bcc fields... If I select to address a contact and try to search on the parent customer (newly added find column for lookup view) I get no results! (only if i...

Sumproducts in a date range
Good day, I was wondering if any one could help me with a problem. My column A contains dates in a dd-mmm-yy format. Column C contains a number of events that took place. I am trying to count the number of events in a fiscal year. A...................C 01-Jan-05..........1 02-Feb-05.........1 03-Mar-06.........1 11-Apr-06.........1 09-May-06.........1 11-Jun-06..........1 =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006)) Will count the events in 2006. =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006),--(MONTH(activity!A2:A1000)=7)) Will count the events...

Specifying a cost based on price ranges
I have a schedule of rates which are fixed for a number of sites and whether the sites are local or further afield. For example Rate for 1-4 sites (local) �500 Rate for 1-4 sites(Distance) �600 Rate for 5-8 sites (local) �900 Rate for 5-8 sites(Distance) �1000 Rate for 9-12 sites (local) �1300 Rate for 9-12 sites(Distance) �1400 Rate for >12 sites (local) �1600 Rate for >12 sites(Distance) �1700 I have a spreadsheet which has each site as a separate row and I want to calculate a cost for each site depending on whether it is part of a group of say 4 or 8 etc. ...

Limitation of Find and Replace Text in Excel #2
It works fine. thanks so much -- wolfteet ----------------------------------------------------------------------- wolfteeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1446 View this thread: http://www.excelforum.com/showthread.php?threadid=26086 ...

Convert specific text to a corresponding number
I have a field that can contain one of four different text values, BV, BR, FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and FR=1. Can I do this with one formula? -- slh Hi To do them in situ, you would need a macro, or use Find/Replace 4 times. To do them in an adjacent column, you could use something like: =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) Hope this helps. -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com... >I...

more than 3 conditional formating in excel
Hi I am new to conditional formating in Excel. In row 2 I need to enter nos. between 1-5. I want each color to have a particular color. I have managed to do 4 (3 with conditional formating and the 4th retaining the default color). Is there a way i can do all 5 colors? Thanks You need to use VBA. Try this:- Private Sub Worksheet_Calculate() 'Code must be placed in the codemodule of the actual sheet you are working with. Dim oCell As Range For Each oCell In Range("A1:A20") Select Case oCell.Value Case Is < 1 oCell.Interior.Colo...

Find out, if test doesn't fit in a cell
Hi, I'm automatically inserting text in Excel cells. The cells height and width is fixed and shouldn't be changed. I'd like to automatically reduce font size, if a text doesn't fit. I would use conditional formatting, but I guess that there's no Excel function available that would help here. And for some reason the font size can not be changed by conditional formatting on my system. So I'd like to use VBA, but how? The easiest way would be to determine, how much space a text needs and compare it with the cell width. But how do I get the first parameter? ...

Sum in a column if there are 3 conditions in another column
I need to find the total from Col B if Col A is one of the 3 possibilities A B DONE 0 DONE 60 OBS 40 POST 55 DONE 40 DONE 0 DONE 47 POST 55 DONE RESULT OBS RESULT POST RESULT Try the below for total for "Post" =SUMIF(A:A,"Post",B:B) 'Try the below for total of all three =SUM(SUMIF(A:A,{"Post","OBS","Done"},B:B)) -- Jacob (MVP - Excel) "DogmaDot" wrote: > I need to find the total from Co...

Finding the link from a commited PO item back to the SO
We are generating POs from SOP to handle some special order situations (so we force a backorder and generate the PO). And then things get really complicated with various thrid party add-ins and e-Connect. What it boils down to is that we need a way to identify the Sales Order that is linked to the item on the PO (from SQL Server, not from within GP). I know the information is actually at the item detail level or below, but we have no idea what tables to examine to discover the links that will lead us back to the original Sales Order number. Do any experts out there have a clue/hint/...

Quest: Finding out how many heads a HDD have...
I am trying to convert Floppy CHS <--> LBA address functions to CHS <--> LBA functions by changing the sectors/track and head/cylinder constants and I can only get from DeviceIoControl() w/ IOCTL_DISK_GET_DRIVE_GEOMETRY sectors/track and tracks/cylinder... So can I calulate head/cylinder from tracks/cylinder or is there somewhere else to get head/cylinder... Any help... > I am trying to convert Floppy CHS <--> LBA address functions to CHS <--> LBA Oops should read - convert Floppy CHS <--> LBA address functions to HDD CHS <--> LBA ...

Find nth instance of a character in a string
I'm sure I've done this in the past but for the life of me I can't remember it now. Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell A1. I want to determine the position of the last "/" (forward slash). The strings won't always contain the same # of "/"s. I need to be able to do this as a formula and also in VBA code. I appreciate your help. -- Have a great day, Francis Hayes (The Excel Addict) http://www.TheExcelAddict.com Helping Average Spreadsheet Users Become Local Spreadsheet Experts If you want to extract ...

searching for values not in this range.
I've this array of alaphets. Eg {"a", "d", "k") I want to search for values NOT in the above range, what is the syntax like ? On Jun 29, 9:24=A0pm, "IT STAFF" <jkk...@hotmail.com> wrote: > I've this array of alaphets. > > Eg {"a", "d", "k") > > I want to search for values NOT in the above range, what is the syntax li= ke > ? I'm assuming you're talking about single characters. If the exclusion range isn't overly large, then you can do this: PS C:\...

Multiple Data Ranges in this Countif
Can any guru help me to edit this formula to multiple data ranges as my quarter TOTAL column are not side by side. "Stefi" wrote: > Try this: > =IF(COUNTIF(E2:H2,">"&0)>=3,"FTF","PT") > > Anyway, yor second criterium is ambiguous: > > > Low Frequency of sales will be PT type. Low Frequency of sales is defined as > > NOT having Revenue $ in at least 3 out of the last 4 quarters. That is, only > > 1 or 2 quarters having revenue$ from Q408 to Q309 (Q408, Q109, Q209 and Q309). > > at leas...

Group By Query
Hello. I need a little help. Is there a way to find out which records were grouped in a group by query, and maybe flag that record with a specific tag for example in this scenario: ID Amount Name 1 123 First 2 123 First 3 123 F-irst 4 345 Second 5 345 Second 6 345 Secon-d ....... ....... If I put this table in a group by query and Sum the amounts, I take the LastOfName and LastOfID then I get something like this: ID Amount Name 3 369 F-irst 6 ...

Event ID: 9234 Referral Interface was not able to find an msExchEx
We upgraded our Exchange Server a year ago and was counting on the MS Exchange Referral Interface to update user email profiles and point them to our new server, but it looks as though some users did not get all of their email profiles updated. Because of this, some of our users are not able to login unless their email profiles are manually updated with the new exchange server name. Is there a way to recreate this Active Directory object and point it to our new Exchange server or some other fix so that these referrals will be successful and user email profiles don't have to be updat...

Losing conditional formatting when printing
Hi, I have a spreadsheet containing amongst others, 10 lists with conditional formatting on every cell (e.g. border,pattern etc). I use VBA code to print these lists but have noticed that when I print more than 1 sheet, only the first few lines print with the con format displayed onscreen. I also noticed that the number of lines printed correctly correspond to the number of lines in the first sheet i.e. if the first sheet contains 10 lines and the second contains 20, only 10 lines on the 2nd sheet will print correctly. If I have more lines on the 1st sheet than the 2nd sheet, all li...

Count the occurence of multiple (4 conditions).
Hi, I am unable to get the correct count using the formula : =SUM(IF(A1:A999=" A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0))))) I am trying to get no of records with " A" values in Column A, "D>d" values in Column D, "1" values in Column F, "1" values in Column E and Sum all the counts- after applying the nested filters. Can someone help me in debugging the same? Will appreciate early response! Thanks in advance, Try this: =SUMPRODUCT((A1:A999="A")*(D1:D999="...

MIN within range based on criteria
I have two rows of dates, row A - "Start" date & row B - "Due" date. In cell A1 user can select a start date from the range in row A below. There will be multiple lines that have same "Start" date but the "Due" date could vary. I would like the MIN "Due" date value to appear in B1 based on the cooresponding "Start" date user chooses in cell A1. ie. A1. user chooses start date / B1. MIN value row B appears A2. 12/1/5 B2. 12/4/5 A3. 12/2/5 B3. 12/6/5 A4. 12/3/5 B4. 12/6/5 A5. 12/3/5 B5. 12/5/5 if user chooses start date of 1...

outlook can't find server
My outlook 2000 errors out saying that it can't find the server. I am on a lan and I can ping my mail server. I have to restart windows(2000) and my errors go away. Any ideas? I am able to browse the net when this error accurs. So that tells me I am connected. I'm having exactly the same problem. When installing Office 2000 or XP, it DOES work on this machine. Anyone having any idea how to solve this? The Microsoft Kb has no answer to this - not even for beta testers ... :-( In article <04b001c39354$b7fddef0$a401280a@phx.gbl>, robbiev says... > >My outlook 2000 error...

To clear the Open Other User's Folder list & To clear the Find a Contact list
What registry key would apply for Outlook 2007 for each catagory? To clear the Open Other User's Folder list ... The first article explains how to get to the right point in the registry. The second article explains what value to look for (101e0390) once you get to the right key in the right profile. It was written for Outlook 2000, but applies to Outlook 2002 as well (Outlook 2002 being version 10.0 instead of 9.0). To clear the Open Other Users folders MRU in Outlook 2003, follow the steps for Outlook 2000 but look for the binary value101f0390 To clear the Find a Contact list .....

copy conditional formats #2
I've spent the past 2 days researching this issue, but have come up dry. I'm hoping you can help me. I have a range of cell, 2 abreast in this instance, and I have placed conditional formatting on them. 1) if B2 = x than range (B2:D2) is green 2) if B2 = y than range (B2:D2) is yellow 3) if B2 = z than range (B2:D2) is red ... 1) if B6 = x than range (G6:I6) is green 2) if B6 = y than range (G6:I6) is yellow 3) if B6 = z than range (G6:I6) is red This works great. My quandary is "copying" this conditional formatting to other cells. ( Column B->D Rows 2->26 ) and...

More missing fields in Advanced Find
Also the system attribute 'Has Children' (haschildrencode) in the Entity Contact is not available for selection in the Advanced Find selection box. Does anybody have an idea why? This and several other fields are missing and are a known problem that should hopefully get fixed with a hotfix at some point. "Karel" wrote: > Also the system attribute 'Has Children' (haschildrencode) in the Entity > Contact is not available for selection in the Advanced Find selection box. > > Does anybody have an idea why? A hotfix is now available for this bug. The h...