Evaluate a Range

I would like to create a formula that looks at a range with a specified
criterion and when it finds the first entry in that range that meets
that criterion returns the position of that data in the range.  For
example, given the following data beginning in A1 and extending to D1:

5,7 ,8,11

I would like to create a formula that looks for a value greater than 10
in that range and then returns the number 4 to signify that it is the
fourth entry in that range.  Thanks as always for your help.


---
Message posted from http://www.ExcelForum.com/

0
1/27/2004 1:50:47 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
621 Views

Similar Articles

[PageSpeed] 6

One way:

Array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

   =MATCH(TRUE,(A1:D1>10),FALSE)

If it's possible that no values are >10:

   =IF(MAX(A1:D1)<=10,"",MATCH(TRUE,(A1:D1>10),FALSE))

In article <ASPENCO.10o3il@excelforum-nospam.com>,
 ASPENCO <<ASPENCO.10o3il@excelforum-nospam.com>> wrote:

> I would like to create a formula that looks at a range with a specified
> criterion and when it finds the first entry in that range that meets
> that criterion returns the position of that data in the range.  For
> example, given the following data beginning in A1 and extending to D1:
> 
> 5,7 ,8,11
> 
> I would like to create a formula that looks for a value greater than 10
> in that range and then returns the number 4 to signify that it is the
> fourth entry in that range.  Thanks as always for your help
0
jemcgimpsey1 (104)
1/27/2004 5:18:01 AM
Reply:

Similar Artilces:

Range
Dear All, I am exporting data ( table) from SQL to Excel. I want to export a formula column, where I can specify the range in Code: what is the similar/equivalent code to: =B(Row()) + A(Row() +1)?? [Is there an Eval function in MS Excel?] Thanks. jouj Not quite sure, but are you trying to get the values in cells Bn & Am? if so =INDIRECT("B"&ROW()) + INDIRECT("A"&ROW()+1) -- HTH Bob Phillips "jouj" <jouj@discussions.microsoft.com> wrote in message news:B0CD55EE-B971-49F4-AAEC-BDC5931C6CF2@microsoft.com... > Dear All, > > I am exp...

Named range error
I created a spreasheet with named ranges, then added another worksheet in the file. Some of the fields on the 2nd tab refer to information on the original information and it's causing a number of named range errors. I'd simply go delete them but I can't . . . why not? Help! Thanks. ...

In Excel, changing a range of cells to all caps.
I would like to know how to change a range of cells from mixed- or lower-case to all upper-case in Excel. Hi, in another column enter =upper(A1) I assume that A1 is the cell where you have the text, copy formula down "antonioejones612" wrote: > I would like to know how to change a range of cells from mixed- or lower-case > to all upper-case in Excel. By formula............see UPPER function in help By macro.............. Sub Upper() Dim Cell As Range Application.ScreenUpdating = False For Each Cell In Selection Cell.Formul...

Help with date ranges
Hi all, I've got a sheet where I have some columns with dates in and I want a column with the number of working days between them. I've used the DAYS360 function, which gives a rough indication of the number of days, but it's not very accurate. Can anyone give me an idea of now to do it, a script, a plug in, or a different app I can download to do it? Help really appreciated. -- MattG Look at NETWORKDAYS and WORKDAY in help, they are part of office/excel but need to be installed (Analysis ToolPak) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "MattG" ...

Identify peaks and troughs in range
If I have a range of data that goes up and down in value. Is there any way to identify the cells/values where it changes from increasing to decreasing and vice versa. I know this must be a IF statement, but I need some help. Thanks. Example 1 2 3 4 This is a peak 3 2This is a trough 4 5 6 7 8 This is a peak 7 6 Etc. If you data is in column A, then in B2 enter: =IF(AND((A2>A1),(A2>A3)),"peak","") and copy down In C2 enter: =IF(AND((A2<A1),(A2<A3)),"trough","") and copy down _________________________________________________ Gary's Stud...

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

Downloadable evaluation version of Outlook 2003
does anyone know if it possible to get a downloadable version? Thanks.....Sly Sly wrote: > does anyone know if it possible to get a downloadable > version? > > Thanks.....Sly No. Not full Outlook, only Outlook Express is downloadable along with IE... >-----Original Message----- >does anyone know if it possible to get a downloadable >version? > >Thanks.....Sly >. > ...

print 3 column range in six columns
I have an excel database with three columns as follows: Column: A B C Column Title: Tail # A/C Type Owner There are about 300 records in my database so when I print I have to either manually format my data into more (6) columns or print these three columns over multiple pages. If I format the worksheet manually to print 6 columns then I cannot sort or add records to the end of my list. I have to enter my new information and then cut/paste to the correct location. I would like to be able to continue entering my data in one lo...

Have you evaluated...
Has anyone evaluated Celerant Command Retail or Tomax Retail.net POS solutions? How are they compared to RMS in terms of features and prices? ...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells ...

Copy-Paste ever changing ranges
On Sheet 2 I have ranges in column C, column E and column G all of different lengths and all changing in size as data is added, but all starting in row 3. What I would like to do is to copy the range from Sheet 2, column C to column A in Sheet 3 starting at cell A2, then directly below, paste a copy of the range from sheet2, column E, and then directly below paste a copy of the range from sheet 2, column G. Make sense? I am thinking code on a button would be apt? any thoughts. Sandy By ranges, I assume you mean named ranges. If so, copy this code and paste it into a standard modu...

determining identical ranges
Can someone refresh my memory as to the array formula one would use t determine if the data in two ranges are identical? Also, how would I return an array of the row numbers of the rows i Range A that are not identical in Range B? Thanks Steve Przyborski Boston, Mas -- Message posted from http://www.ExcelForum.com Steve, =IF(AND(A2:A6=B2:B6),"Identical", "Mismatch") Array-entered. Not case sensitive. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "upstate_steve >" <<upstate_steve.156g8e@...

cells evaluation question
Hey I have a spreadsheet with 9 cells (c14:c22) They will have integers entered from 0 to 10 I need a formula to do the following: - Find how many have a value >=5 - Get the total value of the cells with a value >=5 Thanks Simon You want Countif and Sumif, as in: =countif(c14:c22,">=5") =sumif(c14:c22,">=5") Regards, Fred "Simon" <Simon@discussions.microsoft.com> wrote in message news:8D2F8EA3-D774-4A65-9157-274051900E28@microsoft.com... > Hey > > I have a spreadsheet with 9 cells (c14:c22) > They...

Does IF() Evaluate in Pivot Table?
Is this function really evaluated in the calculated field of Pivo Table? IF(SALES > 10000,100,200) It always returns the value of the FALSE argument for the entir column -- Message posted from http://www.ExcelForum.com ...

referencing a named range in another workbook
I get new files each season from our parent company. Often they have different names but the format and named ranges are always the same and have the same name. I have to link my workbook to cells in their workbook each time it's updated using an Index function. Does anyone know if there is a way I can reference a named range in one workbook1 by typing the path of workbook1 in a cell in workbook2 and having yet another cell look up that path to retrieve the indexed data from the workbook1? Currently it looks something like this: =index('workbook1.xls!' rangeA,5,2) What I'd...

Larger range for CSliderCtrl
The default CSliderCtrl stores its nMin and nMax in ints meaning it can represent a range of -32768 to 32767. I want a CSliderCtrl that will support larger ranges, mainly longs and unsigned longs. I've tried sub-classing the CSliderCtrl but do not know where the min and max are stored so cannot override them with a different type. Can you give me some pointers on how I might go about this? Thanks a lot. Cheers, Robin ...

Named Range #2
I've named few ranges through VBA by using this code For i = 1 To 15 ' Define the name for the variables ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i), RefersToR1C1:= _ "=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) & ",'ST&GT DATA'!R1C216,1)" Next i which is working fine, but when i try to refer it in a chart, an error msg is popping up saying "Your formula contains an invalid external reference to a worksheet, Verify that the path, workbook, and range name...

using COUNTIF with an ABS range
I have a range D2:D49, where values could be negative or positive. I need to take the ABS of the rows, and then count the values that fit within ranges like <5%, 5.1-7%, greater than 10%. I am having problems with getting the COUNTIF to work properly. Am I missing something obvious, or perhaps is it better to do this using a function? Any help would be greatly appreciated. regards, Enzo Check out the FREQUENCY function and remember it has to be entered as an array formula. On Feb 13, 11:01=A0am, Enz <enz...@yahoo.ca> wrote: > I have a range D2:D49, where values could be n...

Dynamic ranges
I'm doing one thing wrong and I can't see the solution I believe that my problem is defining the x-axis in the dynaminc range. I can get the number to start at the right point - however I'm getting blanks on the right side of the graph - like it thinks there are numbers to graph but there isn't. Does this make enough sense or do you need more detail I think we need more detail. Like what formula are you using for the dynamic range. What content do you have in the cells. You could do a quick check by going to the Names dialog and placing you cursor in the control that...

2002 evaluation.
Some time ago I ordered an evaluation copy of MS Publisher 2002. The CD came 2 days later, but that's when my hopes for a good evaluation ended. The thing won't install without a Product Key, which Microsoft never provided me with. During the installation I click on the appropriate link, but all I get is a polite info that the Key has been already sent to the e-mail provided, but will be re-sent. Guess what, it never came. I sent an e-mail to MS support asking for help. They replyed promptly with a car salesman like attitude, saying that they are dedicated to providing ...

Range CopyPicture()
Hi, I'm working on an app which i need to copy a range of cells which is programatically generated using the Excel object model. I want to be able to select the output range which consists of charts and cells making up a table. I'm using the CopyPicture() method of Range which i had woring once to copy the charts and cells combination into word. Having run this again it won't pull across the charts now. The areas where these are meant to go are just blank. Has anyone experienced this before and could maybe give me some information on how to resolve this. I know it can be d...

evaluate expression
Just curious. No reason really, it just occurred to me to ask. In vba you can use EVAL to evaluate an expression and in FoxPro you can use the EVALUATE() function. Is there such an animal in SQL 2005 or 2008 T-SQL? For SQL-Server, execution of dynamically build sql strings is allowed via the EXEC command or with the sp_sqlexec extended stored procedure. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Keith G Hic...

How to put ranges in Criteria..
For a SUMIF formula I need to have a numerical range for the criteria. Is this possible? It has to be a solution that lets me add up the numbers in one column IF the criteria is met..... Thanks for all input!! You can use two SUMIFs in a formula, e.g.: =SUMIF(B2:B28,">40")-SUMIF(B2:B28,">80") Steve wrote: > For a SUMIF formula I need to have a numerical range for the criteria. Is > this possible? It has to be a solution that lets me add up the numbers in > one column IF the criteria is met..... > > Thanks for all input!! -- Debra Dalg...

evaluate
I saw a worksheet for training programs in which this formula is written =IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) can the function C6>EDATE(C6,12) can ever be true as the logic is refering to the same cell afd No, I don't see how that could ever be True. -- Rick (MVP - Excel) "afdmello" <afdmello@hotmail.com> wrote in message news:eXrWFES9KHA.3840@TK2MSFTNGP02.phx.gbl... > I saw a worksheet for training programs in which this formula is written > =IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) > > can the function C6&g...