IF Formulas With Minimum Amount
I have this formula that is not quite working...
this works fine =IF($B$1
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Putting In A Minimum Amount In A Cell
I have a row that is a percent of sales with this: =(F4*0.2+G4*0.1)*I4 "I4 will either be 1.0 or 0.5" Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.
View Replies!
View Related
VLookup :: Equal To Or Greater Than The Minimum Amount?
I am revising a spreadsheet to automate some cells and size cable for my job. I am currently using the VLOOKUP command to search a table for the proper ampacity and return a cable size based on the minimum circuit amps. The problem however is the VLOOKUP command searches for a number equal to or less than my circuit amps and returns this cable size. I need it to return a size equal to or greater than the minimum circuit amps. How do I get it to lookup something equal to or greater than the minimum amount?
View Replies!
View Related
Minimum Of 2 SUM Formulas
I am calculating several cells which in certian circumstances I want the amount to be limited to a max amount. I have a cell that will be used to indicate whether or not to limited the amount, but I am not able to come up with a viable formula. Here is my SUM Formula info: My current calculation =SUM(S16:S18,S21,E22:R22) The cell that will drive the restricted amount I7(H or S). If S is used in this cell the amount should be limited to 80. If H is used then the calculation should be exact.
View Replies!
View Related
Maximum And Minimum Formulas Based On Date Range
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 Replies!
View Related
Variable Amount For Global Functions/Formulas
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
If Minimum Below Zero Then Return Minimum
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula) If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing. This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
View Replies!
View Related
Paste Formulas As Values (strip Out Unwanted Formulas)
I have a macro running this code to strip out unwanted formulas and formatting. Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("CDandC").ClearContents Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Columns("A:E") = Columns("A:E").Value ......................... A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View Replies!
View Related
Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)
I have a spreadhseet with various functions on it and what I am trying to do is this. Cell E4 returns a >35 or <35 true or false value Cell G4 is either blank or has "Yes" text type into it. What I am trying to do is get cell F4 to return certain arguments. E4 = >35 and G4 is blank I want it to state "Email Hiring Manager" E4 = ,35 and G4 is blank I want it to state "Wait" I have a basic IF formula that returns this =IF(E4>35,"Email Hiring Manager","Wait") Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of =IF(G4="Yes","Email Agency","Email Hiring Manager") If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency" Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
View Replies!
View Related
Value Corresponding To Minimum Value
i'm trying to reference a cell value in the column to the left of a "min" value: -A-------- B 1200------20 2400------15 2300------18 900-------22 i'm using the "min" function to select min value from column B (15) but only as a means to use the A column value (2400) in another formulahelpful
View Replies!
View Related
Minimum Value Within A % Range
I am looking to create a formula to determine the minimum value within the top 80% of a range, but relative to certain values in other columns. e.g. Column A Country Column B New Customer Column C Revenue I need to determine what the minimum value is for the top 80% of revenue in column C, but only including Country=UK in column A and New Customer=Yes in column B. This will tell me that the top 80% generate at least £x in revenue
View Replies!
View Related
Minimum Positive Value
in a column let'e say A I have the following values Total 11 20 7 -10 30 -5 -25 16 3 21 -8 I whant to have a variable = the row number with the minimum positive value which is in this case row number 9 and the column value 3
View Replies!
View Related
Minimum Of Range With #N/A
I searched and found a couple things dealing with zeroes or other errors, but not the #N/A errors. I have a range of numbers in A1:a100. To make this range linegraph-friendly, I have all blank or zero entries defaulted to appear as #N/A. Now the problem is that I would like to find the minimum number in the range, but the #N/A entries are messing things up.
View Replies!
View Related
Minimum Greater Than Zero
I have a table populated with equations. I need to write a function to find the smallest value in that table. However, I want to ignore the zero values. From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
View Replies!
View Related
Defining A Minimum Value
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem. =IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3) =IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3) Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View Replies!
View Related
Return Minimum Value Above Zero
has any of you happened to make a function that finds the minimum NONZERO value in a data range? (i.e. all data are numbers, all of them are positive, but some of them are zero. The function should take the excel range as input, and return the minimum value above zero).
View Replies!
View Related
Conditional Minimum Value Of Various Cells
I 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.)
View Replies!
View Related
Find A Minimum Query
I have a set of worksheets all of which are identical and covered by first:last sheets What I am trying to do is come to up with a minimum figure for cell G30 With the proviso that it is a minimum of 30 as some of the entries in G30 are less than that figure. =MIN(first:last!G30) is not the answer as it gives an answer of zero.
View Replies!
View Related
Minimum Distance Between Points
I want to know the minimum distance between customers that I visited in each day. If i visit customer 1, 2 and 5 in one day, which distance is smaller? 1-2, 2-5 ou 1-5 and what is the corresponding value? I have in columns DATE, CUSTOMER 1, CUSTOMER 2, ..., CUSTOMER 5 and in each row I put the Day and 1 or blank for each customer (visited / not visited). I also have a mileage chart for each customer in another sheet which is the source for my distance data. I tried INDEX and MATCH but all I get is ZERO... I think it is comparing the same CUSTOMER.
View Replies!
View Related
Complicated Minimum Value Search
i have a spreadsheet that has 300+rows,and the details below b2 is the description and date, b4 to b29 are the item name c3 is total sales volume between c4 to c29, then another section starts from row 30 till row 57, row 58 till row 85 etc till the end (27 rows for each section) i need to search the minimum value in each section and highlight it as red. i know i can search the minimum value by using MIN(b2:B29), but how can i repeat it for each section?
View Replies!
View Related
Minimum Date With Two Conditions
The 1st problem is a formula to retrieve the minimum date with 2 conditions, 1 of the conditions is in the same sheet the other in a different sheet. This is 1 of formats that I have so far {=Min(If(And(Assigned!C$2:C$10,000),(Assigned!D$2:D$10,000=Teams!E2)))} and is not working. I also tried combining with sumproduct but still does not work. I have 2 sheets.On the Team sheet I have the associates names 1 time, on Assigned I have each associate names with all their sales and the date of their sales. I want to know each ones oldest date sale. I know I can sort and subtotal, but that is to much work. The 2nd problem might be easier this is the formula =Index(A2:J32,Match(E40,A2:A32,0),1). On E40, I have the max sale, I then what to pull the date of when that occurred. I have my dates formated as date. I want an exact date of the max sale.
View Replies!
View Related
Minimum Formula Greater Than To Zero
I have a range of weekly sub totals that get entered each week for the year. o8:bn8 As each week are entered I am trying to find the lowest week's production (using the =min formula) that is above 0 (weeks not yet entered appear as 0) the problem is that it keeps defaulting to the next column once a number greater than o is entered.
View Replies!
View Related
Return The Minimum In Column
I have columns of data as per below and if the data in Column A meets a certain condition I want it to return the minimum in Column B. Column A Column B BU1 5.45% BU1 7.00% BU2 10.00% BU1 4.67% BU2 3.50% So, if Column A contains BU1 I want to know the minimum of the BU1 %'s.
View Replies!
View Related
Minimum Non-zero Within Array Formula
I have a load of (multiply duplicated) categories (in col G) and values for them (in col P). Each category has more than one value, but they should be in roughly the same ball-park. I want to rank the categories in a pivot table, by value, not alphabetically. So I wrote this formula: {=MAX(P$2:P$10*(J$2:J$10=2)*(G$2:G$10=G2))} (I also am only interested in entries for which there is a "2" in column J as shown) This takes the category, finds all instances of the same category in col G and returns the max of all of them - so each row now has a "max value for this category" field. This is all great, but what would work a lot better would be grouping them by the minimum value in the column, but of course when I do this I get a zero (from any one of the hundreds of non-matches). I tend to get non-zero mins using small and countif but can't conceive how I would squeeze that into this formula.
View Replies!
View Related
Choosing Cell With Minimum Value
I am working with a spreadsheet that has quite a few products and each product has a column for each vendor of that product with the price associated with that vendor. So, I have five columns total: product, vendor 1 price, vendor 2 price, vendor 3 price, vendor 4 price. I want to have a 6th column that will show the preffered vendor, which of course is selected by the lowest price. I have been able to accomplish this by chaining IF statements together, but it is getting VERY cumbersome for more than 2 vendors. Ideally, I would like to tell Excel to look at the 4 price cells, choose the lowest one, and return a column number, which I can then associate with a vendor.
View Replies!
View Related
MINIMUM If With Multiple Criteria
I am trying to find the minimum date in column B based on the value in column A and the Maximum date in Column C. I already have a calculation for column C that ignores certain values, i am fine using that date.... On the first sheet, I have.... the "summary of data" The date in column C is the Maximum value ignoring 3 certain dates (10/10/2030, 11/11/2011, 12/12/2012) I used this formula to find that value. =MAX(IF((Sheet2!A$3:A$16=A4)*ISNA(MATCH(Sheet2!C$3:C$16,{47766,41255,40858},0)),Sheet2!C$3:C$16)) Now, based on that I need to find in column B the Minimum value based on both the value in Column A (item) and the maximum date already found in colum C (Max)
View Replies!
View Related
Minimum Plus Adding Formula
Average in Cell J8 : J73 Handicap in Cell I8 : I73 What I’m trying to achieve is the Lowest Average Plus handicap – 1 = total Example Cell J8 = 105 J9 = 169 J10 = 126 J11 = 155 Cell I8 = 45 I9 = 35 I10 = 54 I11 = 21 Result 105 + 45 =150 – 1 = 149
View Replies!
View Related
Pick The Minimum Numbers
I need a function like a sumif, but rather than adding together numbers, I want it to pick the minimum. For example, if I had a list of prices for various products, I want the formula to go and find the lowest price for a particular product.
View Replies!
View Related
Minimum Difference In Dates
I am trying choose the nearest date, from todays date out of a small array of numbers, and then also find the number of days between them. Additional to that, i would like it to ignore a date if that is in the past i.e. <now(). So I have a list of dates in column a, and i would like it to show me the number of days till the next closest 1, considering it hasnt passed already. I have tried many different ways and itteration of IF statement to solve this 1 but just cannot do it.
View Replies!
View Related
Find Minimum Value Corresponding To Maximum Value
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions. So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value). A small sample of what the data set looks like: 2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3 Atlanta771773768761749756 Austin770786784779776776 Baltimore947948949950950953 Boston1,6471,6601,6541,6481,6301,613 Chicago998998996985984986
View Replies!
View Related
Minimum Temperature Between Two Dates
I'd like to try and use an array formula to return the minimum temperature between two dates. Say Column A holds a list of dates. Column B holds the temperatures for each of those dates in column A. Lets say that I want to return the minimum temperature between 10th Jan 2007 and 12th Jan 2007 with the daily temperatures being 9,10 and 11 respectively. I used the following array formula to return the maximum temperature between two dates and it succesfully returned 11: {=MAX( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )} However when I try and use the MIN forumla the answer I get was 0 when it should have returned 9: {=MIN( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )}
View Replies!
View Related
Minimum Value With 2 Conditions/Criteria
Really struggling with this one. Can't seem to figure this out on my own. I'm trying to populate a value from a table based on two given values. The first given value will be an exact match, and the next given value sholud fall within a range. In real life terms, given the category of a security and the months to the maturity date, I'm trying to return a percentage rate to use for another calculation. The rate is given in a Table (Column I in the attached). See attached for example
View Replies!
View Related
Find Minimum Value From A Data Group
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.
View Replies!
View Related
Finding Minimum Value Using Multiple Criterias
I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in. The available orders column and processed orders look something like this: A B C D Time, Available, Processed, Start processing 5 2 0 2 10 0 0 0 15 0 0 0 20 0 0 0 25 5 0 0 30 0 0 0 35 0 0 0 40 0 0 0 45 4 2 4 50 0 4 0 55 7 0 5 60 6 5 6 Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed. A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?
View Replies!
View Related
Highlight Minimum Cell In A Range
I have 4 cells and I want to highlight the lowest valued cell. At the moment I have the conditional formatting formula of =AND($C$5=MIN($C$5:$C$10),$C$5>0) However if C5 = 2 and C9 = 0 then C5 doesnt get highlighted as 0 is lower than 2. However I do want it to be highlighted. How can I ignore the zeros in the range im testing?
View Replies!
View Related
Calculate Price Based On Minimum
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
View Replies!
View Related
|
TRACKER
