Array Formula

Dear Experts,

Please find the following formula
=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).

how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array 
Result?

because if i do "EVALUATE FORMULA" it will result either 0 or 1.
Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the 
records of AC.DATE.

thank you for your kind help and guidance.

TIA.

respectfully,
andri

0
Utf
2/10/2010 1:59:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
653 Views

Similar Articles

[PageSpeed] 54

On Wed, 10 Feb 2010 05:59:01 -0800, Andri <Andri@discussions.microsoft.com>
wrote:

>Dear Experts,
>
>Please find the following formula
>=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).
>
>how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array 
>Result?

=(AC.TR.NMT="X1234")*(AC.STATUS=1)

>
>because if i do "EVALUATE FORMULA" it will result either 0 or 1.
>Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the 
>records of AC.DATE.
>
>thank you for your kind help and guidance.
>
>TIA.
>
>respectfully,
>andri

Your formula is incorrect as written.  In particular,
 (AND(AC.TR.NMT="X1234"),AC.STATUS=1)

Note that the AND portion:  AND(AC.TR.NMT="X1234")  will return TRUE if ANY
entry in AC.TR.NMT = X1234.

Also, your opening and closing parentheses do not match.

The combination of those two issues make it difficult to tell exactly what you
want.

If you want to return the LARGEst(k) date corresponding to an entry of X1234 in
AC.TR.NMT that is on the same row as a 1 in AC.STATUS, then try this formula:

This formula must be **array-entered**:

=LARGE((AC.TR.NMT="X1234")*(AC.STATUS=1)*AC.DATE,1)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>.  If you did this
correctly, Excel will place braces {...} around the formula.

If you want something else, please be more specific.
--ron
0
Ron
2/10/2010 2:24:16 PM
=LARGE(IF((Range1=criteria1)*(Range2=criteria2),DateRange))

ctrl+shift+enter, not just enter


"Andri" wrote:

> Dear Experts,
> 
> Please find the following formula
> =LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).
> 
> how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array 
> Result?
> 
> because if i do "EVALUATE FORMULA" it will result either 0 or 1.
> Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the 
> records of AC.DATE.
> 
> thank you for your kind help and guidance.
> 
> TIA.
> 
> respectfully,
> andri
> 
0
Utf
2/10/2010 2:25:01 PM
>=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).

MAX(....) does the same thing as LARGE(...,1)

Try it like this (array entered**):

=MAX(IF(AC.TR.NMT="X1234",IF(AC.STATUS=1,AC.DATE)))

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


"Andri" <Andri@discussions.microsoft.com> wrote in message 
news:8E0A35C8-5EB5-4ADE-A1B0-B33F45DC82B1@microsoft.com...
> Dear Experts,
>
> Please find the following formula
> =LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).
>
> how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array
> Result?
>
> because if i do "EVALUATE FORMULA" it will result either 0 or 1.
> Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the
> records of AC.DATE.
>
> thank you for your kind help and guidance.
>
> TIA.
>
> respectfully,
> andri
> 


0
T
2/10/2010 5:32:56 PM
Hello Andri,

If you need your results for all possible values of AC.TR.NMT you
might want to consider using my UDF Pstat:
http://sulprobil.com/html/pstat.html

Regards,
Bernd
0
Bernd
2/10/2010 9:21:39 PM
Reply:

Similar Artilces:

"Array Index Out of Bounds"
I recently purchased "Office Professional Ed 2003." With it came "Business Contact Manager." When I double click to open a particular contact or business contact, I get the above "subject" message. Is there a reason, fix, or solution for this? Thanks, John ...

formula Memory
Hello, Those who like to store complex formulae for future use, can install my Add-In: 'Formula Memory'. This Add-In adds a command to the Help menu, allowing to save or reenter those formulas by just a few clicks of the mouse. Also check my sheets selector. http://users.skynet.be/onderland/Excel.htm 18- SheetsSelector 19- FormuleMemory Luc ...

no formula calculation
In the worksheet no formulas are calculated anymore. When I put the following in a cell: =A1+B1 This text is displayed, no calculation is made. Even when I put the following in, =1+3 no calculation is made. Everything is displayed as is. What is the problem?? Is the cell formatted as General (or some number)? If it's formatted as Text, change it to General, then select the cell and hit F2. Then Enter. You don't have a space character before the leading equal sign? And you don't have tools|options|view tab|formulas checked, do you? Junior Trimon wrote: > > In th...

Nesting formulas
How do I nest these formulas into one cell: =IF(MID(D6,4,1)="-", IF(M6="J",TEXT(0,"0000000"),TEXT("0"&LEFT(D6,2)&MID (D6,4,4),"0000000") IF(M6="G","XXXXXX","yyyyy") IF(M6="U",TEXT(0,"0000000"),TEXT(0,"0000000") ...

additem to combobox with an array
hi, i'm trying to add itemns to an multicolumn combobox (3 columns) using an array. if i just try to add 1 column there is no problems, but when i try t add more columns to the combobox it wont work anymore. tips anyone? tia jock -- jock ----------------------------------------------------------------------- jocke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=76 View this thread: http://www.excelforum.com/showthread.php?threadid=47167 Did you change the .columncount property? I made a small userform and picked up some values off a worksheet to create...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

Need a FORMULA to round off
I have a question, quite simple but complicated for a beginner like me. Say I have a figure .... 16 in CELL A1 and in C1 5.05263158 C2 6.73684211 C3 4.21052632 but wanted the numbers to be rounded to the nearest Hundredth totaling it to be the number A1? How can I do that? I mean if I just do the math it should look like this C1 5.05 C2 6.75 C3 4.20 Please help and thank you in advance. Hi try =ROUND(value,2) -- Regards Frank Kabel Frankfurt, Germany Koji wrote: > I have a question, quite simple but complicated for a > beginner like me. > > Say I have a figure .... ...

Array+Array
Hi, I have 2 (or more) arrays, how can i combine them into 1? example: Array 1 = "1, 2, 3, 4" Array 2 = "A, B, C, D, E, F" Result: Array_Combined = "1, 2, 3, 4, A, B, C, D, E, F" br, Danny On Jan 7, 9:23=A0am, Danny <dannypct...@gmail.com> wrote: > Hi, > > I have 2 (or more) arrays, how can i combine them into 1? > > example: > Array 1 =3D "1, 2, 3, 4" > Array 2 =3D "A, B, C, D, E, F" > > Result: > Array_Combined =3D "1, 2, 3, 4, A, B, C, D, E, F" > > br, >...

array
Dear programmer I don't understand the array can any one give very easy example to understand the types of array ( I can use command button and text box) Example 'Don't forget to write notes for me ' the next line will define the array. Dim a(2) As String ' the next lines define the data of array a(0) = "1" a(1) = "2" a(2) = "3" And so on Notes: The array is very difficult please give me easy example "a" <A@a.com> wrote in message news:uy9smdVUIHA.4476@TK2MSFTNGP06.phx.gbl... > Dear programmer I don't understa...

how do I create an array
I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3:G11}),""))) please help ...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Accessing the CCD array of the camera using MFC
Hello, I am capturing the images from the camera using VC++ MFC . And each image is of size 1280*1024. When I want to do processing over certain area, actually I want to divide the Image height(1024) into 4 blocks (256,256,256,256) keeping the width (1280) constant. So that I will get the whole image into 4 small images. Can anyone please kindly tell me How can I able to do this ? I am thinking of accessing CCD array, If accessing CCD array is a solution for this.. Please help Thanks in advance James First, you need to have a device driver that connects to the camera. This is not an MFC ...

Date formulas
Hi, I'm doing a timesheet. The month begin on the 21 and end on 20th of each month (don't ask). It'easy to set a listmenu to choose the month, have the first day to start with the 21, having 31 line with =1+precedente line. But for the three last line, idon't know how to have it to stop displaying dates after the 20th, for Febuary and the other 30 days months. An idea anyone? thank In A2: =IF(A1="","",IF(MONTH(A1+1)=MONTH(A1),A1+1,"")) Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel &q...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Identify occurence in "array" which meets condition(s)
Example Scenario: where my formula tests whether the value in column A equals a multiple of 5. The result is displayed in column B. Call No. Meet Formula? 1 2 3 4 5 Y 6 7 8 9 10 Y Total 2 So, from the list above, I know that the 5th and 10th call will meet the formula. Problem: My formula is a lot more complicated than just checking for multiples of 5 and I have to run the formula per row (it will be a v long explanation if you wanna know why). Is there a way such that I just enter "20" in one cell and the formula gives me a result of "5, 10, 15, 20" (us...

array/list
I have defined an CObarray class called CReference, which I've made serialisable. I'd like CReference to contain a list type varailble that contains a list of strings, So I thought I'd use the CStringList class. Thus my CReference looks like class CReference : public CObject { DECLARE_SERIAL(CReference) public: CStringList notesrefs; CStringList notesresources; CString title; ...... ...... } and my serialise looks like void CReference::Serialize(CArchive &ar) { CObject::Serialize(ar); if (ar.IsStoring()) { ar << notesrefs << notesresources << titl...

return a value in one cell based on multiple formulas
I have the following data: gift_id suffix gift_desc size BX02 Chateauneuf - Semi Dry White 750 ml (12) BX07 Cabernet Sauvignon (VDP) 750 ML (12) BX08 Valrose (VDP) 750 ML (12) CH02 NYS White Sparkling 187 ML (24) CH02 NYS White Sparkling 750 ml (12) I would like to return a value in the "suffix" column based on the text in the "size" field. for example =IF(ISNUMBER(SEARCH("750",D2)),-750). I want -750 to appear in the suffix column if partial text "750" appears in the size column and similarly I want ...

To Array or not to Array...
....That is the question! Is there a difference (in terms of processing speed, efficiency, etc.) between using the non-array SUMPRODUCT formula or using the SUM array formula? When used on a large scale, is one faster - or better in some way - than the other? thanks. Hi "" In my limited experience I find that on large worksheets, array formulas are far more processing greedy than SUMPRODUCT and hence take longer. I tend to prefer arrays though! And i find more user friendly with nested IFs (with SUM/COUNT etc) Mathew "hmm" <hmm@hmm.com> wrote in message news:3FE...

Help with IF formula
Hi everyone. Someone on here always has an answer for my question, so thanks in advance. I am running XP Pro SP2, and Office Pro 2003. Column A is for quantity, and the quantity can be positive or negative. Column H is for individual pricing, and Column I is for the price extension (i.e. A*H). Column I is blank unless there is a number to be displayed. I have the following formula set up in I4 now: =IF(A4>0,A4*H4,"") which works OK if A4 is a positive number. What should the formula be so it also works with a negative number? Rember that if A4 is blank, then I4 is also ...

array formula in vba
Hi! I have a worksheet in which I have a following array formula entered in Q1:Q1000 ={IF(MAX(IF($B$1:$B$1000=B2;$P$2:$P$1000))<30;0;1)} I would like to write a macro that would enter such formula into Q1:Q1000, the problem that I can't solve is the number of rows that is changing, sometimes I have 1000 rows, sometimes more. How to adjust the formula under vba to consider the number of rows? TIA Maciek Your formula looks kind of weird. You started in B1 in one portion and P2 in another. And you wanted to put the formula in Q1:Q###. I'm gonna guess that you really meant to st...

Is there a formula for this?
I have an Excel spreadsheet with 2 columns. The first column has a Box number (example: 01245). The second column has the file numbers that are in that particular box (example: 821-873). So, in other words, box #01245 contains files 821, 822, 823, 824, etc. all the way to 873. Obviously it's difficult to find a particular file number if we don't know what file number range it's in. Rather than list each file number separately (Example: first column I would list the box number and then in the 2nd column list the first file #, then I would go down to the next row and ...

formula in a pivot table?
Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

Moving Reference Cells, Keeping Formula Pointers Constant
Is there a way to keep the references in a formula constant when the cells they point to are moved For example On Sheet 1, cell A1 contains the number 50. On Sheet 2, cell A1 contains the formula "=Sheet1!$A$1" If I move the 50 on Sheet 1 to cell B1 (cut-and-paste), the formula on Sheet 2 changes to "=Sheet1!$B$1" Can I force the formula to point to Sheet1!$A$1 even when I move that cell's contents Thanks ~J Hi try =INDIRECT("'sheet1'!A1") -- Regards Frank Kabel Frankfurt, Germany joneill wrote: > Is there a way to keep the references in a fo...

Array
Hello from steved I would like to insert a row but I get a message you cannot change part of an array How do I acheive inserting a row Thankyou. Remove your array formula(s) that intersect the rows you're moving. In article <3dcc01c42a70$53d92520$a501280a@phx.gbl>, "Steved" <anonymous@discussions.microsoft.com> wrote: > Hello from steved > > I would like to insert a row but I get a message > you cannot change part of an array > How do I acheive inserting a row > > Thankyou. Thankyou. >-----Original Message----- >Remove your array fo...