MAXIMUM Function Limit: Return A Value?
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Limit Returned Value To A Maximum/Minimum
Given this formula =(H2+( SUM(K53:DB53))) which simply takes an existing value and adds to it the sum of a series of values. I am happy if the value returned is any number less than 28. However if the value returned from the above formula is >28 I want the number returned to be shown as 28.
View Replies!
View Related
Work Around To The 7 Function Limit With The IF Function
I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory). I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.
View Replies!
View Related
Return MAXIMUM Based On Criteria
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 Replies!
View Related
Return Maximum Date Or Blank
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View Replies!
View Related
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
Return Cell Address With Maximum Value
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4) It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
View Replies!
View Related
Limit The Search Range In FIND() Function
I am currently using the simple code block below to jump to a cell on another sheet based on a user-inputted value to a cell on the current sheet. Although jump is too strong a word at the moment...walk slowly might be more appropriate. The problem is that the sheet that contains the target cell hosts a very sizable used range and the FIND function can sometimes take almost a minute before finding and focussing on the required cell. While I do not believe that a search range below that of sheet level for the FIND() function is possible, the cell that I am looking to jump to resides in a one-column, sorted, dynamic named range (=Bookings!JobID) on the target sheet, so I am sure that it must be possible to find and go the target cell in a much quicker timeframe than that being delivered at the moment. Just can't find something suitable at the moment.
View Replies!
View Related
Index Function - Limit On String Length
I have a spreadsheet with 2 sheets. The first just links to another file and pulls in the contents of a particular sheet, cell for cell. The second sheet is a formatted report that I'm creating to put that data into a different format for presenting to others. I'm using the Index and Match function along with range names. All is fine except where the string that should be returned is very long. When that happens, I get "######" all the way across the cell. It doesn't matter how wide I make the column or how tall I make the row, the "#s" do not go away. Is there a limit on the number of characters the Index function can return?
View Replies!
View Related
Return Data Corresponding To Minimum & Maximum
Please 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.
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
VBA Macro To Return Maximum Value In Text Form..
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet GL_WeldBendHeader 0.0001700.0115100.000500 0.0000000.0007800.000000 0.0002400.0110700.000480 0.0000000.0006600.000000 0.0000000.0037700.000000 0.0000000.0000100.000000 0.0002800.0108700.000480 0.0000000.0007100.000000 0.0002000.0112900.000490 0.0000000.0008400.000000 000.00002 00.000310.00001 000.00004 00.00030.00002 000.00001 00.000270.00003 000.00002 00.000260.00004
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
Return Values In Range Based Upon Relationship To Maximum Value
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 Replies!
View Related
Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum
I 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.
View Replies!
View Related
Maximum Range For Sum() Function?
I am trying to sum 44,582 cells in a column, the values in all of them are either 0 or 1, but I always get a value of 0. It works when I bring this right down to 1000 or 1500 cells but it can't seem to sum the entire range. This is Excel 2004 for Mac OSX Version 11.3.5
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell. for example: if cell A1 = 07/11/2009 I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date. I have tried stuff like: =IF(A1=TODAY()-14,"True","False") but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View Replies!
View Related
Function Return 0 Value
Is it possible to add some code that will make the function return 0 if it would otherwise return #VALUE!. Without getting to involved here is the main piece of code. Public Function FixedCosts(RscNum As Integer, InstYear As Long, CalcYear As Long, MW As Double) FixedCosts = numMonths * MW * Adjust * (CapCarryCostMonthly + OtherFixedMonthly + TransCap) / 1000
View Replies!
View Related
Return Variable From Function
I've searched for an example of function that returns a variable, however, I'm unable to find one other than the one just has return variable on the end. I was wondering what I have wrong in the following code. This is placed in a module to make it available to all my worksheets. Public Function findLR(Worksheet, Column) As Integer LR = Worksheet.Cells(Rows.count, Column).End(xlUp).Row return LR End Function
View Replies!
View Related
Return Function As An Argument
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View Replies!
View Related
IF Function To Return A False Value
I am trying to use the IF function to return a false value if the combined sum of a row is not the same as either of the 1st 2 cells. If that makes any sense! This is the formula I would like to use. =IF(SUM(g3:g9)=g1 or g2 then y if true and x if false. I thought it would be easy but I can't figure out how to do it
View Replies!
View Related
If Function To Return No. Based On 2 Cells?
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12). What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400. Is there such a formula or do I have to enter manually the amounts into an If function?
View Replies!
View Related
Function To Return Last Modified File
I don't really know VBA more than just piecing together other people's work (so please dumb down your responses as much as possible). So I would like to have a function that displays a filename within a cell. The filename I need to display will start with "cost" and have a date at the end (not always the same number of characters). I need to search a folder for all files that contain that text and return the last modified one. I found this VBA code that returns the last modified file, but only filters based on the extension (which I would like to keep in the code as well).
View Replies!
View Related
IF Function To Return No Data As A Result
I've created a Database using excel which feeds data to a pivot table I created based on it. My objective is to create a new item in this pivot table (Pivot Table > Formulas > Calculated Item) which calculates the average data for the past 6 months. The formula I used in order to get a result is this: =IF(((May+June+July+August+September+October)/6)=0;;(May+June+July+August+September+October)/6) and the problem is that the answer is 0 when there is no data for all 6 months (for example, there is a customer who hasn't baught anything during the past 6 months), the pivot table displays 0, instead of not showing this customer at all. My goal is for the IF function not to return any data if the result is 0, so that the pivot table doesn't show items with no data. I also tried: =IF(((May+June+July+August+September+October)/6)=0;"";(May+June+July+August+September+October)/6) and also using ;" "; but these formulas return #VALUE! in the pivot table, given that the Data is "SUM OF Sales" and "" is not a number. How can I get the formula not to show anything at all? In addition to this, I was wondering if it is possible to make this formula more automated, so that it calculates the average of the past 6 months, taking into account some type of reference month (rather than having to edit the formula each time a new month is analyzed).
View Replies!
View Related
Function Won't Return Value
I'm trying to get a "simple" function to return a number and not the #NAME?error! I'm writing the following vba code. It's located in the sheet1 vba code area. On the spreadsheet itself, on sheet1, column C, there is data from row 1 down to row 55. I have tried formatting the Column as General, text,etc. Function Asset() As String Asset = Range("C1").End(xlDown).Row End Function When I type into the spreadsheet =Asset() , I get the #NAME? instead of the number 55. When I step through in debug mode, Asset shows Asset="55" , but my function doesn't return it!
View Replies!
View Related
Return No Value If The Function Evaluates To An #REF
I am using an array formula like this in cells A2:H100 of a worksheet: =INDEX('Source Data'!$A$2:$Q$5000,SMALL(IF(('Source Data'!$I$2:$I$5000>=$O$1)*('Source Data'!$I$2:$I$5000<=$P$1)*('Source Data'!$M$2:$M$5000>0),ROW('Source Data'!$A$2:$Q$5000)-ROW('Source Data'!$A$2)+1,ROW($Q$5000)+1),ROW()-1),1) The function must be present in all 99 rows to accommodate potential data, but there is rarely enough data to populate the entire sheet, leaving #REF! errors in some cells. Putting aside that using this many array formulae is sloppy programming, how do I craft the function above to return no value if the function evaluates to an #REF! error?
View Replies!
View Related
MATCH Function Inconsistent In Return Value
I am using Excel 2002 and trying to do something which should be simple with MATCH. I have an array of numbers ordered, starting at 0 and incrementing by 0.05 up to 20. I am then using the MATCH function to find the relative position of numbers 0,1,2,3,4 and 5. However, for number 0,1 and 2 the return value is one less than what it should be, for the numbers 3, 4 and 5 it is working as expected. By the way, the match_type I am using is the default 1. Also I have formatted everything to be numbers and tried various things already, so am beginning to wonder is this is a known issue.
View Replies!
View Related
If Function: Return A Drop Down List
I am trying to create an if function that returns a drop down list (which i have already created in another cell) to the cell if a certain condition is met. When i try, the if function only returns the option that has been selected in the drop down lists cell, instead of the entire drop down list. Is there a way to get the If function to return a working drop down box?
View Replies!
View Related
|
TRACKER
