Maximum Based On Condition

Dec 28, 2006

function that can find the maximum value in a range, where the range is defined by the rows containing a specific string, i.e:

In column A:
Alm Rød
Alm Grøn
Alm Blå
Special Rød
Special Grøn
Special Sort
...

In Column B I have values for each of the above string names.

Now I would like to find the maximum value for the range with names starting with "Alm".

View 6 Replies


ADVERTISEMENT

Finding The Maximum Value In A Column Based On A Condition

Nov 7, 2008

This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

View 6 Replies View Related

SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??

Oct 23, 2008

I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

View 4 Replies View Related

How To Count Condition Based On Having Another Condition

Apr 12, 2014

So I have one sheet that needs to pull data through to another sheet (which is a stats summary)

I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.

For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.

View 9 Replies View Related

Maximum Value Based On Multiple Variables

Jul 28, 2009

Maximum value based on multiple variables. Basically what I have is something like this:

View 3 Replies View Related

Return MAXIMUM Based On Criteria

Aug 13, 2008

i have a spreadsheet like the following

A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan

and so on...

So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?

View 5 Replies View Related

Find MAXIMUM, Show Date When MAXIMUM Occurred

Oct 9, 2008

Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.

View 2 Replies View Related

Conditional Maximum (find The Maximum 'value' For Each Individual 'type')

May 15, 2009

I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'

The 'types' are not necessarily next to each other and the data cannot be sorted to do so.

Example:
type value
A 15
B 6
A 21
C 7
B 13

I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.

View 2 Replies View Related

Maximum And Minimum Formulas Based On Date Range

Apr 28, 2009

I have a spreadsheet with two columns - column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order.

I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).

View 2 Replies View Related

Identify/highlight Maximum Value Based On Multiple Criteria

Dec 4, 2009

I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:

INDV DATE TIME FIX ....other attributes

INDV = unique code identifying the individual

The data is sorted by INDV, then by DATE, then by TIME.

I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.

View 3 Replies View Related

How To Search Dataset And Return Maximum Value Based On 3 Using (array)

Dec 6, 2012

I want to search a data set and return the maximum value based on 3 criteria using (array) formulas.

Say the data set contained the following:

Row 1: column headings (date, name, data 1, data 2, data 3, etc)
Column A: dates
Column B: names
Columns C through G: data

I want to find the maximum value for a given name, in a certain month, and a nominated data column (e.g. data 2). For example:

Month = June
Name = Geoff
Data column = Data 2
Max value = ????

All of the data in the data columns is numeric.

View 4 Replies View Related

Return Values In Range Based Upon Relationship To Maximum Value

Jan 22, 2008

I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.

The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.

View 5 Replies View Related

Mark Maximum Value In Range Block Based On 2 Columns

Mar 21, 2008

I have a long list of stock values (col C) arranged in time blocks so;

CREATE TABLES LIKE BELOW?B C M N

19:00 50.00 0 47.1
19:00 51.00 0 47.5
19:00 52.00 1 48.3
19:00 51.00 0 47.3
19:10 36.00 0 45.3 (nb time has changed)

etc

I need a routine that takes the following action;

When the value 1 occurs in col M row .. (it will only occur once in each time block) and N1 on the same row has the highest value [ 1 and 48.3 above] put 1 in column O on the same row else a 0.

If 1 occurs in column N (time block X) and the value in N is not the highest then store a 0.

This needs to be done for each time block.

It feels like this can probably done by an If or vlookup type of routine but I can't come up with one that works across blocks of times.

View 9 Replies View Related

Hide Rows Based On CheckBox Event - Reaching Maximum Limit

Apr 19, 2012

I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).

The checkboxes go from D5:D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].

Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).

I did not know how to do it any cleaner, so I created 142 of these:

Code:
Private Sub CheckBox_D5_Change()
Dim i As Long
For i = 5 To i + 30

[Code]....

It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?

View 9 Replies View Related

Sum Based On Condition

Dec 15, 2009

I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.

B C
abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2

View 2 Replies View Related

Set A Formula Based On Condition

Mar 22, 2009

I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is

< 50 is negative
= 50 is neutral
> 50 is positive

For example
Column A Column B
45 Negative
50 Neutral
80 Positive

View 4 Replies View Related

Inserting Row Based On Condition

May 4, 2009

I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)

View 5 Replies View Related

Insert Row Based On Condition

Aug 12, 2009

I would like to write a code, that can check the name of cell A1 with A2.

If A1=A2, then insert a blank row between these two rows.

I have started with a loop, but somehow it does not work.

Sub AddRows ()

Dim Row As Long
For Row = 1 To 3800

If Cells(Row, 1).Value = Cells(Row + 1, 1) Then
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End If

Next Row

End Sub

View 9 Replies View Related

Delete A Row Based On Condition

Dec 14, 2009

I want to delete a row in excel based on a condition.

The condition is as follows:

I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).

In this case I want the row with orange color filled in column 24 to be deleted.

View 11 Replies View Related

Vlookup Based On 2 Condition

Jan 28, 2009

i looking for function, which will give me a results if its the person good , bad and other (Y). It will look based on column A, but if will be more equal names in this column, it looks in the column surname (B) and return value from column (Y) in this row. But it can be more equal column B.

Here is an example:
Results what i want are in Z1,Z2,Z3

X Y Z
1 sue doe good
2 joe bond very good
3 mark no bad

SOURCE table
A B C
1 john doe good
2 joe black bad
3 sue doe good
4 mark black not bad
5 joe bond very good
6 mark no bad
7 kat doe bad

View 4 Replies View Related

Sum Row Based On Condition Refering To A Different Row

Dec 9, 2009

I have a problem with bringing up the data I need to cells in one row.

In row 1 I have dates:
23-Nov 24-Nov 25-Nov 26-Nov etc.

In row 2 Quantities:
2000 1500 250 750 etc.

In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4

and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?

View 3 Replies View Related

How To Change One Condition Based On Another

Feb 2, 2007

I have a "if" statement that goes something like this:

=if(and(Function1),do this, if(and(Function2), do that, 0)

where Function 1 and Function 2 consist of three conditions each.

Now Function 2 has reference to a particular cell (U23), whose value is dependent on other cell references.

If U23 has some real value, I want the above statement to be executed.

However, if U23 displays a "#N/A", I want only Function1 to be executed. i.e. = if(and(Function1),do this,0)

View 9 Replies View Related

Subtotaling Based On A Condition

Sep 27, 2009

I autofilter a list which contains, among other fields, program numbers in column A, account numbers in column B and dollar amounts in column E. I want to display the filtered/subtotal of column E if I select a certain program. Each program has account numbers ranging from 1000 to 8999.

I used =SUM(IF()) formulas to separately display totals with account numbers below 8000, and >7999 with no problem. But when I got to create formulas to display the subtotal, I encountered difficulty.

I used the following CSE formulas to subtotal sum of column E based on the values in column B, the first formula return 0 (zero), which is incorrect. The second formula appears to ignore the IF statement, and returns the total sum instead of the filtered total.

1. {=IF($B$4:$B$112>7999,SUBTOTAL(9,E$4:E$112),0)}

2.{=IF($B$4:$B$112

View 8 Replies View Related

Delete Row Based On Condition

Jan 19, 2007

i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A

column is "O"
must search threw entire row

View 3 Replies View Related

Count Total Based On Condition.

Jan 18, 2010

I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.

I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003

View 4 Replies View Related

Filter Based On Multiple Or Condition

Nov 6, 2013

Is there a way to filter data based on multiple OR condition with a like parameter.

I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".

View 5 Replies View Related

How To Have One Cell Copy To Another Based On Condition

Mar 10, 2014

I'm trying to set it up so that if a particular cell has any data in it, the contents of a different cell get sent to another. I want the no data/data to serve as a trigger.

As an example, if I put anything into cell A3, I want the contents of cell A1 to copy to H1. I'd also like it to be dynamic so that if I put something into cell B3, the contents of B1 get copied to H1, etc.

View 4 Replies View Related

Insert Row And Values Based Upon Condition

Apr 4, 2014

I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:

In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'

Here's image of before and desired after:

Before:

PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD

After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here

View 1 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I have a workbook sheet change condition that, when triggered runs the following code (amongst others).

[Code] ......

What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.

So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190

View 4 Replies View Related

VBA To Delete Entire Row Based On Condition Met?

Jun 16, 2014

I have table in which against the Column A data values are added in Column B, C and D.

If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?

Note: Column A data begins from Row 15 and below.

Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved