Conditional IF formula using multi-dimensional arrays

I am attempting to find a value in a 30 column (1000+ row) array that is 
conditioned on finding an exact value match in adjacent column and a 
not-to-exceed value in the same row in the 30 column array.  If this were 
limited to single column arrays, I would not have a problem, but the issue 
arrises because I do not know which column the not-to-exceed value will be in 
so I must use the full 30 column array.  Here is my formula, which I am 
hoping someone would be able to help me with:

{=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))}

Thanks for your help,

0
Utf
4/13/2010 2:04:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1272 Views

Similar Articles

[PageSpeed] 14

Your formula should work. Here is my data - a little smaller than yours
4	1	28	1	54
39	2	48	53	21
x	3	34	19	56
x	4	37	98	27
x	5	50	56	15
x	6	67	72	12
x	4	82	96	47
x	6	49	62	12
x	4	24	98	42
x	5	34	67	72

The 4 and 39 are in A1 and A2 (the x's are to just pace holders)
I typed this formula
=MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))
and completed it with CTRL+SHIFT+ENTER. Excel automatically enclosed it in 
braces
{=MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))}  I DID NOT type the { }
The formula gave me the answer 37
There are three rows with 4 in column B, with these values in the other 
columns
37	98	27
82	96	47
24	98	42
I want the largest of these that does not exceed 39 (value in A2)
Clearly this is 37.
If I change A2 to 50, the formula correctly returns 47.
This formula also gets the correct answer but does not require to be entered 
with CTRL+SHIFT+ENTER
=SUMPRODUCT(MAX((B1:B10=A1)*(C1:E10<=A2)*C1:E10))

Have I read your question correctly?
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"iperlovsky" <iperlovsky@discussions.microsoft.com> wrote in message 
news:69D0C3C4-7BE0-4F44-AC0F-5CDF51A8DA87@microsoft.com...
> I am attempting to find a value in a 30 column (1000+ row) array that is
> conditioned on finding an exact value match in adjacent column and a
> not-to-exceed value in the same row in the 30 column array.  If this were
> limited to single column arrays, I would not have a problem, but the issue
> arrises because I do not know which column the not-to-exceed value will be 
> in
> so I must use the full 30 column array.  Here is my formula, which I am
> hoping someone would be able to help me with:
>
> {=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))}
>
> Thanks for your help,
> 
0
Bernard
4/13/2010 3:33:50 PM
Bernard, thanks for your help. Your explanation is very clear and it works 
with my data set. I now realize a had a data quirk initially.

IP

"Bernard Liengme" wrote:

> Your formula should work. Here is my data - a little smaller than yours
> 4	1	28	1	54
> 39	2	48	53	21
> x	3	34	19	56
> x	4	37	98	27
> x	5	50	56	15
> x	6	67	72	12
> x	4	82	96	47
> x	6	49	62	12
> x	4	24	98	42
> x	5	34	67	72
> 
> The 4 and 39 are in A1 and A2 (the x's are to just pace holders)
> I typed this formula
> =MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))
> and completed it with CTRL+SHIFT+ENTER. Excel automatically enclosed it in 
> braces
> {=MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))}  I DID NOT type the { }
> The formula gave me the answer 37
> There are three rows with 4 in column B, with these values in the other 
> columns
> 37	98	27
> 82	96	47
> 24	98	42
> I want the largest of these that does not exceed 39 (value in A2)
> Clearly this is 37.
> If I change A2 to 50, the formula correctly returns 47.
> This formula also gets the correct answer but does not require to be entered 
> with CTRL+SHIFT+ENTER
> =SUMPRODUCT(MAX((B1:B10=A1)*(C1:E10<=A2)*C1:E10))
> 
> Have I read your question correctly?
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> "iperlovsky" <iperlovsky@discussions.microsoft.com> wrote in message 
> news:69D0C3C4-7BE0-4F44-AC0F-5CDF51A8DA87@microsoft.com...
> > I am attempting to find a value in a 30 column (1000+ row) array that is
> > conditioned on finding an exact value match in adjacent column and a
> > not-to-exceed value in the same row in the 30 column array.  If this were
> > limited to single column arrays, I would not have a problem, but the issue
> > arrises because I do not know which column the not-to-exceed value will be 
> > in
> > so I must use the full 30 column array.  Here is my formula, which I am
> > hoping someone would be able to help me with:
> >
> > {=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))}
> >
> > Thanks for your help,
> > 
> .
> 
0
Utf
4/13/2010 4:15:01 PM
Reply:

Similar Artilces:

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Can a Component written in VBScript return an array of Objects?
Can a Component written in VBScript return an array of different Object types? In other words the elements of the array are not the same type. -- 100% Accurate Display Screen OCR http://www.OCR4Screen.com Peter Olcott schrieb: > Can a Component written in VBScript return an array of different Object > types? In other words the elements of the array are not the same type. > Yes; all data in VBScript is stored in Variants, so you may even mix plain and oop 'objects'. The retured thingy is just another Variant of subtype array. So just create/fill your array an...

Find Highest Score In List Formula
Hello all, I'm looking to return the highest score for a users with multiple scores in a list of other users with multiple scores. Thank you, Ron Say the data is like: frank 56 joe 9 frank 74 frank 101 jim 143 jim 146 joe 200 frank 164 joe 135 joe 127 joe 177 jim 10 jim 135 jim 53 frank 190 joe 109 jim 193 jim 29 jim 8 jim 107 joe 93 joe 9 jim 153 jim 186 joe 36 jim 174 jim 141 frank 55 jim 92 frank 141 joe 15 frank 5 frank 34 joe 161 jim 103 joe 88 and we want the max score for frank: =MAX(IF(A1:A36="frank",B1:B36,""...

Simple Formula
I have a formula, bt4/37 (bt4 = 6) and it returns 5. However, my calculator and an Access database returns 16. Can someone tell me why Excel returns 5? thanks. -- Kat3n hi, Either I'm reading this post incorrectly or you have a broken calculator and are gettting results out of excel & access that are equally incorrect. 6/37= 0.162 recurring So if we assume that your result of 16 is a typo and you meant .16 there must be something your not telling us about the formula your using in Excel. How is the 6 derived in BT4 ? What is the format of BT4 ? Post the pr...

Conditional Footnote
Hello, I am working on a report where the user wants to see a footnote at the bottom of the page only if a certain section of the report is visible on the page. The sections are: Page Header Group Header 1 Group Header 2 Detail Group Footer 1 Group Footer 2 Page Footer They only want the footnote to appear at the bottom of the page and only if the Group Footer 1 is visible on the page they are viewing. Is this possible? Thanks. That indicates that you are already hiding certain sections somehow. You can set the footnote equal to the visibility of the section like for instance: me.fo...

Developing a project with a multi developer environment.
Hi, is it posible for Developing a project with a multi developer working on the same database at the same time?. Thank's ... If you have multiple users in the database, you need to split it. You can then work on your local copy of the front end while others are using their own local copy (connected to the shared back end), and distribute the update to them when you have it properly developed, debugged and tested. If splitting is new, see: Split your MDB file into data and application at: http://allenbrowne.com/ser-01.html -- Allen Browne - Microsoft MVP. Perth, Weste...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

Translating Formulas to Functions
I'm hoping someone here can set me on the right path via an example so that I can complete this onerous task. Currently I have a spreadsheet for fantasy football that I maintain. The Workbook (Excel 2003) has the following worksheets Sheet 1 = Named Totals Sheets 2 - 18 = Named Week1, Week2, Week3, etc to Week17 Sheet19 = Status Sheet 20 = Roster Roster has 5 columns, 1 for each player position (QB, RB, WR, K, Def) Under each column is listed the person or team for that slot. Status has 18 columns and 44 rows. Some rows are blank or just contain a description. Column 1...

ANN: New version of Database Workbench, the multi-DBMS IDE now available!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular multi-DBMS development tool: " Database Workbench 4.0 Pro" With this version we're reached a milestone: Database Workbench is now fully Unicode enabled and offers new tools to increase your productivity. There have been numerous improvements to existing tools and the user interface making it even better than before. For more information, see http://www.upscene.com/ Database Workbench supports: - Borland InterBase ( 4.x - 9.x ) - Firebird ( 1.x, 2.x ) - MS SQL...

Save as CSV isn't saving comma delimited, but is using semi-colon.
Save as CSV isn't saving comma delimited, but is using semi-colon... How do I get it to save using commas only (,)? (I'm using semicolons for other delimitations and so can't just do a global replace.) Thanks so much for your help! Hi Excel uses your Windows regional settings for saving CSV files. Also ho should an importing program of this CSV file know whether it is the list separator or one of your other separators? -- Regards Frank Kabel Frankfurt, Germany CCinItly wrote: > Save as CSV isn't saving comma delimited, but is using semi-colon... > How do I get it ...

What if formulas and calculations
I need to create a calculation based upon a number of fields e.g. A1 = Y or N B1 = Y or N C1 = 32 D1 = a number E1 is for the result Required: If A1 = Y and B1 = Y then E1 = C1 * D1 else E1 = D1 All I can get is FALSE if either A1 or B1 is not Y but is both are Y then I get the correct result =IF(AND(A1="Y",B1="Y"),C1*D1,D1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "BJS" <BJS@discussions.microsoft.com> wrote in message news:60CBD837-9F0A-4AB9-88CC-7493705AAA33@microsoft.com... >I need to create a calculation based upon a number of fie...

easy to use templates
Hi We have outlook templates that people fill in each morning and send to a distribution list. But they are not that easy to fill out as you have to place the curser in the proper spot to type then do that throughout the entire document. It is easy to overlook items. Is there a way to set up a template that you can just hit tab or enter and it automatically goes to the next area that you need to type information? -- Thank-you! Ruth What exactly are you using now? In other words, what do you mean by "templates"? Also, do you meet the prerequisites for using...