I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
View 4 RepliesAttached 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 have created a worksheet with headers on each column each has different and used the =MIN function to find the lowest value. All good so far.... Now instead of returning the value I would like it to return the header text.
For Example.
Four columns Tom Paul Harry Bob
Tom has a value of 10 in the cell below.
Paul has a value of 5 in the cell below. and so on with Harry and Bob
Using the =MIN(A3:D3) I get the lowest value (in this case)5. I want to the returned value as Paul instead of 5.
i have the sheet which have the table which look like
Person task done
A 5
B 4
C 1
D 1
now i want is there is any way to find minum value in task done colium and return the person name and like in this exapmle, 2 person have done have done minium work.. it tell me both those name
I have 3 columns 1st one has numbers that reference a wellbore.
2nd have a depth
3rd has a number value (a mud weight) volume
Well API example Depth Weight
42050102600000 10000 10.2
42050102600000 9500 9.2
42050102600000 6500 8.5
42050105000000 12000 11.0
42050105000000 7500 6.0
42050105000000 10200 9.5
42050110100000 8000 8.0
42050110100000 9500 9.2
I want the formula to give me based on the well API number the max depth and the weight associated to that max depth. And the minimum depth and the weight associated to that minimum depth. I'm not sure how to say it in a formula. I don't want to use VBA.
Is there anyway I can do the below formula without it being an array formula?
View 2 Replies View RelatedTo highlight largest and smallest values in a worksheet I have used large and small function to calculate and then and index to highlight name attached
View 2 Replies View RelatedActually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly.
View 8 Replies View Related I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.
Here is what I invision(cells are for example purposes)
=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))
So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.
I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.
I have an IF Function that if C4 says HUD then it returns the infor in a range of cells on the HUD worksheet, which is a separate worksheet from where this formula is {=IF(C4="HUD",HUD!A1:C100,"")}. I have also used =IF($C$4="HUD",HUD!A1,"").
But now, on the cells that it is returning, it puts 0 in cells that are blank on the other worksheet. What can I add to this formula to eliminate that?
Both of these functions are working otherwise. They are just returning 0 in cells that were blank.
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button.
Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View Relatedtrying to create a 'countdown' to time of macro completion (based on a static performance time of 1:48). What I want to do is compare a time (ProgTime) set as static at the beginning of the code with 'TIME' and return the difference in seconds. I use the following code:
[Code] ....
When I step through, I can see that ProgTime and Time have different values and yet 'Test' always returns a value of '0'. I tried using 'Now' instead of 'Time' and the same result ('0') occurs. I have experimented with declaring 'Test' as an integer, a long and a date and the result is always the same! I've tried using 'Seconds() around the datediff to ensure an answer in seconds, I've tried 'format' around it with multiple decimal places to make sure I'm not missing a tiny value: Nothing seems to do the trick!
I am new to VBA programming in Excel (currently 2003) so hopefully this isn't a "dumb" question.
I would like to re-arrange a group of data from a table of several rows and columns to a single column where the data from the first row is translated to a column then the second row is appended to the end of that data ....
ie:
( 1 ) ( 4 ) ( 7 )
( 2 ) ( 5 ) ( 8 )
( 3 ) ( 6 ) ( 9 )
to
( 1 )
( 4 )
( 7 )
( 2 )
( 5 )
( 8 )
( 3 )....
I thought a user defined formula would be a good place to start but am having problems getting the address of the cell that the function resides in to use to calculate the offset from a fixed cell (to be entered into the function as a argument). In other words, how do I get the address of the call that a UDF is entered in into the UDF? activecell.address is returning the cell that was last clicked not the cell were the UDF resides.
Secondly is there a better way to achive the results that I am looking for other than through a function? The size of the table will vary from worksheet to worksheet. I would also like to add an argumnet that lets me exclude cells with certain content from the resultant list.
I have data concerning documents people have to write, which is updated weekly. Is there a way of looking up a name and then returning all the documents that they have to write. I have tried a vlookup, but this only returns the first entry and not the lot. Also i would prefer the document data for each person to be returned in to one cell.
View 3 Replies View RelatedI am working on a spreadsheet that deals with times such as a work schedule, how every the schedule cells are in a time format, and my if function will only return "check"(which is false). my code works if it remove the time formatted cells but i want to keep the document in its orginal format.
=IF(Mngmt!F16="PH","",IF(Mngmt!F16="OFF ","",IF(Mngmt!F16="VAC ","",IF(Mngmt!F16="8:00:00 AM", "MIKE", "CHECK"))))
The bold and under line portion is where my problem is at.
Its looking to the mngmt tab to find f16 when it equal 8:00:00 AM (scheduled time in time format) but returns ""check"(false).
I have a function that works out which is the last column containing data on a worksheet, but for some reason it is alwys retuning 0.
View 14 Replies View RelatedMy problem is that I am getting the N/A error in cell M3 if L3 is empty. I would like the forumla to 'ignore' cell L3 if it is empty. I do not want a zero put in there. I have looked up information on ISBLANK function but the more I read the more confused I am getting
View 6 Replies View RelatedI have two columns A & B. If I enter "Short" into Col A, I want "Long" to automatically appear in Col B, and vice versa. This is the code I have created so far. =IF(C4="Short","Long",IF(C4="Long","Short",IF(C4="",""))). This works but when I paste the formula into all cells in Col B, I get "FALSE" showing up in every cell. How can I stop the "FALSE" from populating every cell in Col B while Col A remains blank?
View 4 Replies View RelatedI have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number].
I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title.
Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
I have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
I'm trying to compare three formula results and return the largest number. Ex.:
2217922
The first three numbers are formula results and the last is the following formula:
=IF(O5>P5,O5,IF(P5>Q5,P5,Q5))
Unfortunately, with the list below, you can see the last number (the comparison formula) isn't always the greater of the three:
2217922
2017920
01799
01399
01399
01299
01299
01299
01299
01199
01199
01099
0088
0077
0077
I've also tried other formulas, with no success:
=LARGE(O5:Q5,1)
=MAX(O5:Q5)
=IF(MAX(O5:Q5)=O5,O5,IF(MAX(O5:Q5)=P5,P5,IF(MAX(O5:Q5)=Q5,Q5,"Error")))
What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?
In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies View RelatedA client has a registration workbook for each student which contains 3 types of worksheets, A) Transcript B) Registration and C) Grade sheets. Each file has only 1 Transcript sheet and 1 to many pairs of Registration and Grade sheets.
I have a GPA user defined function which obviously can only be used on a Grade sheet. I would like to return an Error if the user trys to use the udf on a non Grade sheet.
Currently I am just returning 1/0 but the smart tag returns #Name. Can I control the text displayed somehow?
I'm currently working on a program in which some sort of recursive looping seems logical.
The relevant part of the code I've currently written looks like this:
Sub hello()
Dim Max_stream As Integer
Dim j, jj As Integer
Dim P_sum, P_min As Double
Dim antal_in, antal_ut, rad_in, rad_ut, ut1, ut2 As Integer
Application. ScreenUpdating = False
Max_stream = WorksheetFunction.Max(Worksheets("Data"). Range("B65536").End(xlUp).Value)
For j = 1 To Max_stream
The problem with the code above is the last If, in which the function "rekursivloop" calls itself. I don't know if this type of formulation is correct, but for the cases the If condition is TRUE (ie ut1 <> ""), the function does not return any answer. Does anyone see an error in the logic or have some example for how to write similar recursive loops?
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 need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status
05/08/2012 10:30:12
INV
05/17/2012 08:27:37
INV
and so on...................
I am currently using the Match and index functions to search for specific criteria and return specific data based on that criteria. The problem is the formula is returning the first value at the beginning of the month rather then the end of the month value that I am asking for. I have my criteria set to zero so it should be a perfect match. with the live worksheet I have several tabs for different years. I want to take the end of month balance on each of those year tabs for each month in each fiscal year and add them together. My formula only has the one month because I am unable to get it to work much less adding in all of the years information. I have the employees choosing EOM for the last transaction posted during that month and that calculates the month number in the field next to it. I was thinking I could use the match to look for a specific month in that column and return the balance adjacent to it at that point.
View 7 Replies View RelatedIn an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
VB:
ActiveSheet.Range("$A$1:$I$566").AutoFilter Field:=1, Criteria1:= _
">=11/11/2012 22:13", Operator:=xlAnd, Criteria2:="<=11/12/2012 6:47"
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
VB:
[FONT=Consolas][SIZE=2][COLOR=#0000ff]xlsRange1.AutoFilter(Field:=1, Criteria1:=">=11/11/2012 22:13", Operator:=Excel.XlAutoFilterOperator.xlAnd, Criteria1:="<=11/12/2012 6:47")
This returns 1 instead of 4 expected.