I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A
View 3 RepliesI have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.
I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)
I am trying, without success, to create a formula that will refer to a column of data and do the following in one step:
1. Find the minimum value in the range, and then
2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted.
For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)
What is the syntax for the formula for the minimum number, greater than zero, in a series of numbers.
I have a row of numbers, including some zeros, but I want the minimum number excluding the zeros.
I have 3 columns L, N and P
I would like to return the MIN value(column R) between the columns
except where the MIN = zero then just leave the result as blank.
I have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies View RelatedI am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
I would like to find min value in column B for 1 order number (they are sorted Asc). Values must be shown in column C (currently are in column C expected values).
Example table:
No.
A
B
C
1
Orders
Values
[Code]....
Trying to create a Math formula that allows for Taking advantage of sale pricing.
In Excel the following:
Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =
Extended Price(L3)
Example
2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00
Not quite sure how to handle the Sale price taking over regular pricing
I am trying to do a spreadsheet for my boss and one piece of it has me stumped.
Based on same "die name" I need to return the minumum quantity available.
So, in column D i have a multitude of die names. Some of them match for those that match, I need to return in another cell the smallest quanity available in our die bank (column p).
What I am attempting to do is find the MIN value in Column C where values in Column A are equal.
The data would look like this
A B C D (D:D is where the "MIN" Formula will be)
Scope1 NameA $100
Scope1 NameB $145
Scope1 NameC $115 $100 (I want the min value to show up here)
- (this would trigger a break between scopes, and provide a conditional format separator)
Scope2 NameE $450
Scope2 NameG $345
Scope2 NameX $415 $345
-
So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
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).
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View 4 Replies View RelatedI would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.
In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.
The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.
I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.
how to conditionally format the data in one table based on the criteria specified in another table on a different worksheet.
The Table I'm trying to format has a series of Products and Volumes that'll be available on different dates. Table 1.jpg
The Criteria table has each product and a corresponding Key Data. Table 2.jpg
All I want to be able to do is have the cell corresponding to the criteria to be highlighted but can't for the life of me figure out how to do it. If it makes a difference I'm working offAttachment 338501 a Mac. Excel Version 2011 14.4.3
I have created a userform and it works fine.Following code is assigned to commandbutton to transfer data to sheet "FT".
[Code].....
I want to add that : If value in combobox2 is “ KT” then all entries are copied to sheet3
Otherwise copy everything in sheet”FT”
Other parameters remain same.
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.
Basically I want to show a Data Bar in a certain cell based on the value from another cell.
It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.
As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.
For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.
So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.
If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.
Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.
NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached
Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?
What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.
Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul
[code]......
So for example:
row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July
row3: Feb is latest imput and "next" also 2. Result should be Apr
row8:May is latest input, "next" is 6. Res should be Nov.
Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.
I'm using Excel 2010 on my Mac. I would like to set up the data as follows:
A
B
C
D
E
F
NAME
DATA X
DATA Y
DATA Z
DATA XX
DATA YY
NAME
90%
88.2
410
88.4%
97.8
[Code] ........
Now what I would like to do is have the data in each column highlighted a certain color based on specific ranges. For example in Column B I want anything from 92% - 100% highlighted blue. I would like 83% - 91.9% highlighted purple, and anything under 83% highlighted red. Do I need to have a conditional format entered in each cell? Can I set up a conditional format for the whole column? Once I can figure the formatting in my example the remaining columns will be a breeze since they will be set up in the same manner just different number ranges, same colors will apply.
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it.
Basically the problem is this:
In column "A" I have what I call point numbers for all the data entries.
In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet).
So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc).
What I am trying to do is to come up with a table that shows a summary of the following:
A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89).
Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range.
And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range
You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.
I have a report I am producing that returns some information regarding accounts we deal with. it may be best to explain what I have allready. Firstly I have The total occurences in a specific month and year using the following formula.
View 4 Replies View RelatedPlease see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y".
It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment.
I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use.
I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
I'm having an issue right now with an employee list that I am working on. What I am trying to do is determine the earliest time that an employee signed in, but the list that I can export may have the same employee on it multiple times in a day.
Here is a sample of the data that I'm using:
Emp No. Time In
9900872 9:00am
9900874 9:03am
9900874 11:00am
9900874 11:45am
9900875 10:15am
9900875 11:45am
What I want to get is the minimums only, so that the list would show:
Emp No. Time In
9900872 9:00am
9900874 9:03am
9900875 10:15am
I know how I could do this in VBA, however I have everything else working without using VBA, so if possible I would like to leave it that way. Also, I do not know if it would matter to the solution, but the employee numbers are always sorted numerically, although their clock in times are not.
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
Daily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.
View 5 Replies View RelatedIs it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???