Conditional Max value

Hi,

i have 2 columns and i'm trying to calculate the conditonal maximum from
column one.

These are the columns :

      47 7
      44 7
      71 7
      58 7
      214 4
      22 4
      54 7
      1 7
      45 7
      21 7


and i try to find a formula that gives the maximum in column one, where
column 2 has value 7

in this case this would be 58

Marc


0
marc777 (2)
8/26/2003 1:46:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
359 Views

Similar Articles

[PageSpeed] 50

Marc,

Use the following array formula:

=MAX(IF(B1:B10=7,A1:A10,FALSE))

Change the range references to match your data.

This is an array formula, so you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces {}.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com    chip@cpearson.com





"Marc" <marc@mobile-post.com> wrote in message
news:3f4b6548$0$298$ba620e4c@reader0.news.skynet.be...
> Hi,
>
> i have 2 columns and i'm trying to calculate the conditonal maximum from
> column one.
>
> These are the columns :
>
>       47 7
>       44 7
>       71 7
>       58 7
>       214 4
>       22 4
>       54 7
>       1 7
>       45 7
>       21 7
>
>
> and i try to find a formula that gives the maximum in column one, where
> column 2 has value 7
>
> in this case this would be 58
>
> Marc
>
>


0
chip1 (1821)
8/26/2003 1:54:54 PM
Woehoe, works like spagetti with an Italian.

Thanks !
Marc

"Chip Pearson" <chip@cpearson.com> wrote in message
news:OT47im9aDHA.1640@TK2MSFTNGP10.phx.gbl...
> Marc,
>
> Use the following array formula:
>
> =MAX(IF(B1:B10=7,A1:A10,FALSE))
>
> Change the range references to match your data.
>
> This is an array formula, so you must press CTRL+SHIFT+ENTER rather than
> just ENTER when you first enter the formula and whenever you edit it
later.
> If you do this properly, Excel will display the formula enclosed in curly
> braces {}.
>
>
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> www.cpearson.com    chip@cpearson.com
>
>
>
>
>
> "Marc" <marc@mobile-post.com> wrote in message
> news:3f4b6548$0$298$ba620e4c@reader0.news.skynet.be...
> > Hi,
> >
> > i have 2 columns and i'm trying to calculate the conditonal maximum from
> > column one.
> >
> > These are the columns :
> >
> >       47 7
> >       44 7
> >       71 7
> >       58 7
> >       214 4
> >       22 4
> >       54 7
> >       1 7
> >       45 7
> >       21 7
> >
> >
> > and i try to find a formula that gives the maximum in column one, where
> > column 2 has value 7
> >
> > in this case this would be 58
> >
> > Marc
> >
> >
>
>


0
marc777 (2)
8/26/2003 2:02:36 PM
Reply:

Similar Artilces:

Adding the value of cells >than 4.5
I am working with a sheet containing numbers in cells between 1-6, I only need the numbers 4.5 or greater, and average those numbers. what is the formula for it to determine say cells A1:A30 only the ones >4.5 + the average Try one of these: =SUMIF(A1:A30,">=4.5")/COUNTIF(A1:A30,">=4.5") Or this array formula** : =AVERAGE(IF(A1:A30>=4.5,A1:A30)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mellybean" <Mellybean@discussions.microsoft.com> wrote in mess...

conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 & 2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say 30, the answer would be 4. if the number was say 80, the result would be NA. Can anyone help me with this problem? Thank you and kind regards JV ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** This can probably be done easier =INDEX(LARGE(ROW(INDIRECT("1:"&...

Conditional format to cells containing a #DIV/0! error
I would like to apply a conditional format to an entire spreadsheet. In particular, if a cell returns and error such as #DIV/0! I would the selected font color to be white. What do I have to enter in the conditional formatting menu? If I set the value of the cell equal to #DIV/0!, the format does not work. A solution that I found could be to use the GoTo functions to select the cells with errors and then manually apply the color, but since I am creating a template at a later point the empty cells that return the divided by zero error may contain a number. Basically I would like the cells t...

paste values Excel 2007
Is there a way to setup a hot key to paste values in Excel 2007? Also, is there a way to change the icon buttons? Thx You can add the "Paste Values" icon to your quick access toolbar. I placed mine next to a "Copy" icon. Only custom icons can be modified to my knowledge. There may be ways developed by independent users. Squeaky "Diamond" wrote: > Is there a way to setup a hot key to paste values in Excel 2007? Also, is > there a way to change the icon buttons? > > Thx Re changing the icon buttons - used to be possible in...

Getting a value from the last record
Hello, I have an inventory database. I have the sum of apples in my last record and want to subtract apples today from it. The value is the last record number from a table: Total apples (in last record) = 25 (new record) sell 5 apples = total apples = 20 me.total_apples = me.sell_apples + (last record me.total_apples) How can I get that last records me.total_apples. I do not have an open internet access so I can not go see alan browns web page only microsoft web pages. Thanks How will Access know what the last records is? Do they contain a DateTime tracking fiel...

Excel; Can I Filter based on a cell value?
Is it possible to filter a list based on a cell value? What I want to do is for the user to type in a criteria in cell F1 and G1 (Dept and Acct No's) and click a button that will launch a macro that will filter the list and display only entries that have Dept and Acct No's that match those entered in F1 and G1. I have looked through the help files in Excel and can't find a way to do this. If this is not possible is it possible to set something up so that cells that do not fit the criteria are deleted from the spreadsheet somehow? It is ok if non-matching entries are de...

set a value to another cell
how to set(not get) a cell value to another cell? is it possible? A cell formula can get, but only a macro can set. Some exceptions (manually): Scenarios, Goal Seek, Subtotals, Text to Columns and Advanced Filter, none of which is applicable to your situation. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "news.microsoft.com" <whoknows@microsft.com> wrote in message news:%23kjDDZRoIHA.264@TK2MSFTNGP05.phx.gbl... > how to set(not get) a cell value to another cell? > is it pos...

Copy a currency value from one control to another
I have a text box (called ExtendedCost), that has a sum statement in it. I want to copy the value on the exit control event to another text box control (called WithdrawlAmount) on the same form and write the value into WithdrawlAmount box on the Enter Event for the control box, so people don't have to input the same number into the control box. Is there an easy way to do this? If it's the same calculated value, just use the same expression. Generally, don't store calculated values. Just perform the calculation as needed. If you need more help with this, describ...

Limiting subform entries based on mainform field value
Hi, I am sure that someone has already asked (and got an answer) to my question, but I was not able to find the exact answer by searching through the previous topics. Big part of it is probably the fact that I am fairly new to Access, and probably was not searching for the right key words. But at any rate, here is my question. I am trying to build a form (with a subform) that allows consultants within a company to record their client consultations. So, here are the details. I have a mainform with the following fields: Clients (ClientID, Client Name), Client's membership status (Status) ...

Formula to go into sub folders of main folder and get values from
I was hoping to have a formula similar to the one I have posted. This way it can go into the main folder, look through all other folders, find all report cards, and sum the value at the address so that I can have a grand total carried to my data base. Is this possible? Test 1 through 3 are folders all containing 1 file called 'report card'. Although called Test, these folders are actually different peoples names. =SUM('C:\NORTH BAY TIMMINS\TEST1,TEST2,TEST3\[REPORTCARD.XLS]Report Card'!$D$14) I wish to have a formula that will go into all the sub folders ...

'advanced filter/unique values only' inaccurate counts
i often have to use filters to reduce a column of numbers to just it's unique values. simple enough, but unfortunately excel's advanced filter doesn't always do the job and leaves out values or just doesn't filter correctly. i'm hoping there's a simple explanation for this and the filter can be made more reliable. any thoughts? The filters are reliable, so there may be a problem with the data. For example, a space at the end of a text string can cause two cells that look the same to be extracted by the filter. Perhaps you can give an example of what's left out, ...

Displaying add'l value in Combo box
I have a combo box that uses a table as its row source. When the combo box displays, I would like it to display an add'l value not in the table, e.g. "<All>" I find the easiest way is to provide a listfill function. Here's mine: Public Function AddAllToList(c As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant ' ListFill function - use as RowSourceType for listbox ' Set listbox.rowsource to correct sql ' Set listbox.tag to (eg) 2;<<All>> to override defaults: ' 1;( All ) Static rs As Recordset...

How to get 2 Max values?
I have following 2 tables (T1, T2) and rows: T1 (Column: Id) ---- 1 2 3 4 T2 (Columns: Id, Value) ---- 1, 1 1, 2 1, 3 1, 4 2, 1 2, 2 2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1 4, 2 4, 3 4, 4 The result-set I need is: --------------------------------------- 1, 3 1, 4 2, 3 2, 4 3, 3 3, 4 4, 3 4, 4 i.e for each Id from T1, I need 2 max values from T2. How do I write the query? Will this work? Select t1.Id, ( Select t2.Value From t2 Where t2.Id = t1.Id Order By Value Desc Limit 2 ) From t1 I keep getting subquery returns more than one...

Finding the Value in the "last" cell?
I have an excel file that has a vba macro that grabs data from someplace else every few minutes and writes the data to the next empty line on a sheet "n". SInce the macro just appends the info to the sheet when event x is triggered I never know what the last line (row number) of the sheet will be. I need to have cell a1 (for example) give me the data is whatever is *currently* the most recent (or latest appended) data from column E. Thus, if the macro five minutes ago was on row 245 and E245 had the text, "FRED" and now event x triggers the macro so a new line of data is w...

Conditional Delete
I want to delete all cells that contain specific text. Any help would be appreciated! I'd select that column and do Data|Filter|autofilter Then use the dropdown and choose custom contains: (type in your value) And delete the visible rows Then data|Filter|autofilter (to remove the filter) ======= You may be able to chose the value you want from the dropdown, too--avoiding the Custom stuff. gsxith3@embarqmail.com wrote: > > I want to delete all cells that contain specific text. Any help would > be appreciated! -- Dave Peterson Edit>Find what: yourtext Find all. ...

Determining Change in a cell Value....
Please i need some Help... Number (Col A) Start (Col B) End (Col C) Tp1203 01/20/2009 02/01/2009 Tp1203 01/20/2009 02/01/2009 Tp1203 01/20/2009 02/01/2009 Sb507 05/20/2009 06/10/2009 Sd702 07/08/2010 08/20/2010 Tp1203 01/20/2009 02/01/2009 Tp5203 01/10/2010 01/30/2010 Sb507 05/20/2009 06/10/2009 Sb507 05/20/2009 06/10/2009 This is what I want to have as my final result Tp1203 01/20/2009 02/01/2009 Sb507 05/20/2009 06/10/2009 Sd702 07/08/2010 08/20/2010 Tp5203 01/10/2010 01/30/2010 How do I check the values in ...

Giving Null a value in a VBA stamtement
Hi all, I am writing some VBA code in Access03 in a fields after update event. The field types are decimal (18,1). I am trying to write a SQL statement which looks like this: Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg = ([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]! [SizeUGAliquotSize1])+([tblStorageShip]! [NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]); And if one of the fields is a NULL i get a NULL return for that record. Can I somehow set NULL to euqal 0 for this set of code instruction so anytime it wencounters a null while exec...

Workflow : Can't see fields in dynamic values
Hi everybody, I have two entities , one of them is having picklist values. I want from workflow to update the another entity ( a particular field) , when i chose a certain value in the picklist . I can not see all the fields in Dynamic values. I am unable to see the fields of Owner and Account (these are lookup fields). If Account and Roles. Job function = Account Manager then update Account. Account manager= Account and Role. Owner ( i can not find Owner in dynamic fields) any suggestion ? ...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

Autofill: can it detect change in value?
Is there a way to autofill down a column, where the autofill detects a new value and uses that value, until the next time it hits a new value? Simple example: I want this: To look like this: Dave Dave Dave Dave Bill Bill Bill John John Try the below 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'>Special> from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter ...

Change axis values
Excel 2002. Spreadsheet chart plots a cycloid, with x=r(t-SIN(t)) and y=r(1-COS(t)). The chart produced is correct. However, I would like the x-axis to display the values of angles (either degrees or radians) rather than the values calculated by x=r(t-SIN(t)). Ideas?? TIA Paddy I'm not sure the request makes sense, but here is what I would do. I would keep the same XY plot you currently have, hide the X axis (set everything on the Format Axis/Patterns tab to none), and generate a series that has points where you want ticks and labels, and use this as your X axis. Here are a...

PDF Form Field Values
Is there a way using vba to simply extract the user input values from a PDF form so they can be 'Uploaded' into a database? Thank you, QB I don't know about the PDF per se, but you can certainly do this in Word. http://gregmaxey.mvps.org/Extract_Form_Data.htm If you have MANY PDF files that you need to do this for, look into converting them Word docs: http://www.pdftoword.com/ Then follow the steps in the first link I posted. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "QB" wrote: ...

Error: Value in Formula of wrong data type
I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

Typed in value changes?
Hi Folks , I am sure that I must have done something screwy and changed some options around but I have not been able to figure out what the problem is. The issue is that whenever I type a value into a cell it automatically divides that number by 100 and that is the value that is then displayed in the cell (ie typing 3 into the cell results in 0.03 being displayed). This just happened all of a sudden and it does not matter if I am in a file I previously created or a new file and it does not matter what column or row I type the value in. It is worth noting that if I open up previously...

Inserting default value in part of input mask
I have a DB where user enters CourseName and Then CourseNumber (Primary key and Unique).. I run a code to check and display a custom error message if the value in CourseNumber is duplicate. The CourseName are actually abbreviations like AFF or PSCRB, or MFA etc... CourseNumber contains three parts i.e. CourseSerialNumberInCurrentyear +CourseName+last2digitsofCurrentYrneeds for example 001AFF09 or 029MFA09 or 010MFA10.. because of different user using the db they enter the same course no. in different formats like 001AFF09 or 001AFF2009 or 001/AFF/09 or AFF/ 001/09 and so many w...