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


ADVERTISEMENT

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

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

Sum Multiple Columns Based On Multiple Variables?

Jan 28, 2014

I'm looking to find a formula to calculate the red cell (actually all the cells in the "type" fields) by determining:

1) If the Product in the table matches the one in row 3
2) If the dates in table columns H, J, L, N and P took place in the month before the billing date
3) If those two conditions are met, sum the matching columns in I, K, M, O and Q.

For example for the red cell would currently equal $11,380.02 as the only revenue collection dates in range would be cells I1 through I3.

View 7 Replies View Related

Sumif Based On Multiple Variables

Jun 21, 2006

I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.

Here is the sheet that the information will be drawn from:

Part#QTYREP
4011A
4023A
4031B
401 1 A

Here is the sheet that the information will be output:

Rep
Part#AB
4012
4023
4031

View 5 Replies View Related

Calculating Number Based On Multiple Variables?

Jan 24, 2014

What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected

My excel data is set up like this

(A).....................(b)......................(c)..................(d)........................(e).........................(F)
.......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg
1. City
2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50

3. Winnipeg....................................$3.20..................$3.75..................$3.6..................$3.21

4. Saskatoon..................................$5.4..................$5.2..................$4.90....................$4.70

(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)

I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:

..................A............................ B..............................C.
..................Weight..................City..................Charge per 100kg
24...............800..................Toronto..................$5.50

View 5 Replies View Related

Maximum If With Multiple Criteria

Dec 17, 2009

I want to find the maximum in column C if Column A = b but I don't want the number if it equals 11 or 9. So, the answer should be 7, but I don't know how to create the correct formula..I'm sure I have to nest a couple of things into an array, but I just can't seem to get it to work.

ABC
a17
a34
b27
b711
b109
c93
c86
d78
e44
e92
e31
f27

View 6 Replies View Related

Maximum With Multiple Conditions

Nov 17, 2007

I have problem How to find Max of a column but instead see the conttent of the previous besde column on two criteria. and some one "UMH" from another forum helped and guided me much but unfortunately still my problem remains. http://www.access-programmers.co.uk/...d.php?t=138130. I attach the sample

View 4 Replies View Related

Using 2 Variables To Return Multiple Items From Multiple Sheets

Feb 14, 2009

I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.

I find writing out what I want helps some times so let me try it here.

So my variables are:

Product (there are 22 products)
Supply Less than (inset number)

These are the two criteria I want to use to produce a result.

The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.

What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.

The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.

View 9 Replies View Related

IF Statement Using Multiple Variables To Give Multiple Outcomes

Feb 6, 2009

I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:

A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)

D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))

I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.

View 2 Replies View Related

Declare Multiple Subs On 1 Line Just Like Multiple Variables

May 25, 2014

I'm making a Form with multiple pages and on every page there are atleast 36 Textboxes.

[Amount] [Description] [price]

I want to run a small sub updating the price on changing the amount. My code now looks like this:

Code:
Private Sub TextBox1_Change()
Call UpdatePrice
End Sub

Private Sub TextBox2_Change()
Call UpdatePrice
End Sub

Private Sub TextBox3_Change()
Call UpdatePrice
End Sub

I have to do this on 8 pages, 24 to 36 times, which makes the code extremely long. Is there a better way to do this?

Something like:

Code:
Private Sub TextBox1_Change(), TextBox2_Change() etc...
Call UpdatePrice
End sub

View 2 Replies View Related

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 View Related

Multiple VLOOKUP Conditions And MAXIMUM Value

Aug 16, 2009

I have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.

Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.

View 3 Replies View Related

Return Maximum Value Among Multiple Records With Same Key Using VBA

Mar 1, 2013

I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).

Eg :

sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r

So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.

View 6 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

Identify Multiple Maximum Peaks In Data Set

Oct 8, 2008

I have a set of data about with approx 7500 cells all contained in one column. The data has a series of peaks that happen, and I need to identify each peak value and place it in a cell. The peak does not occur at regular intervals but they are somewhat regular, as in within 130-230 data points. So, if one could find the first peak in the first 200 cells, identify it and store it somewhere, then look in the next 200, store it, etc. I don't know how to do that in excel.

View 4 Replies View Related

How To Return The Maximum Value In A Multiple Results LOOKUP

Dec 11, 2008

What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.

Please see attachment.

Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)

View 6 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

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

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

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

Multiple Sum If Variables

Feb 5, 2010

Can I put two variables into a SumIF? forexample I want to sum Column C if Column A is equal to Apples and Column B is equal to Oranges, then sum Column C. Is there a quick formula?

View 3 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

Multiple Variables To Calculate A Value

Jun 26, 2014

I am looking for a syntax to ask a question with three variables

As an example
If
Blade size = 114 (only 3 blade size choices)
and
the Panel Size is between 500 and 700 ( This will also be dependant on the blade size)
and
the tilt bar is clearview (Three choices)
then
the result will be x = 182

The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202

Screenshot 2014-06-26 14.21.41.png
Screenshot 2014-06-26 14.23.53.png
Screenshot 2014-06-26 14.26.11.png

View 3 Replies View Related

Vloookup With Multiple Variables ...

Sep 30, 2009

I need to count up the scores from a questionnaire I have developed, which I am having trouble coming up with a formula for.

I have 5 different types of sales channels in column A. In columns C, E and G there is the call method they use (inbound, outbound or both) with a 'Y' / 'N' option next to it. Basically, I need a formula that if a sales channel has a Y next to Inbound, then the value 'Inbound' to be returned to the destination sale and if it has a Y next to Outbound then the value 'Outbound' returned to the destination cell and so on.

I have attached a spreadsheet to make my problem easier to understand.

View 7 Replies View Related

Multiple Variables Count

Oct 28, 2009

I'm trying to use two variable to calculate a count.

In my attachment I have Name, Balance, and Disabled for the data.

I would like to find out how many names has a negative balance and have a "Yes" for Disabled

In the Summary part, I can find out the count of who is disabled, but can incorporate the negative balance.

View 4 Replies View Related

Testing Multiple Variables At Once

Feb 18, 2010

This code is functional but very redundant and I figured that has to be an easier way to go about this.

View 5 Replies View Related







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