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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
Putting Text In A Referenced Cell
In my excel sheet I have a list of where they go, which are basically excel cells.. i.e. A1 is the top left corner. What I am trying to figure out how to do is, is there a way that I can get excel to see "A1" and then put it in A1 of another sheet on the bottom. Thus I get the same grid I have on the wall, making a map of my things. Also, I could just cut and paste, but the problem is I'm working on organizing the stuff on the wall, and if I do it the way I want to, when I move things, they should automatically be moved on the chart. So basically I need it to look at a cell for the reference, choose text from another cell (which will be concaternated(sp) text), and then place it in the referenced cell in the chart sheet.
View Replies!
View Related
Putting Formula In Cell With VBA
I tried to put the following formula into a cell using vba, but I received alot of errors. here is the formula I am trying to put into a certain cell and I will use a variable to replace the row number in the formula =IF(AD323<>"Open","",IF(OR(AND($AI323=TODAY(),$AJ323<>"Done"),AND($AN323=TODAY(),$AO323<>"Done"),AND ($AS323=TODAY(),$AT323<>"Done")),"Yes",""))
View Replies!
View Related
Putting Multiple Entries In One Cell
It lists single clients on single lines, with various column fields that have autofilters (such as where th client came from, when they arrived, who is dealing with them, are they complete and so forth). The final entry I need to include is a list of th policies (it is a financial business) they need us to consider, split into three columns of life policies, pensions and investments. My problem is that I am unable to put more than one policy in each of those columns which can then be sorted through the filters. How am I going to achieve this, yet still keep one client per line?
View Replies!
View Related
Putting Formula In A Cell Which References Other Sheets
I am writing a VBA macro in excel. I have several sheets in this one workbook........one sheet for each day in a given month. Sheets for each day are labled as 3_1, 3_2 for march 1st and march 2nd respectively. I also have 4 sheets for the 4 weeks in a given month. The weekly sheets are labeled week1, week2 etc.... What i am trying to do is this: In the week1 sheet i am trying to "put" a formula in say cell 9,12 which sums up the same cell in the first 7 days of the month. But i am having issues. Does anyone have a good way to do this via VBA? David
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
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
Minimum Date + Blank Cell
I have a column that indicates the earliest date that a project will start. There is a Project Minimum Date, Activity Dates, Sub Activity Dates and Sub Sub Activity Dates. When a Sub Sub Activity Date is entered, it goes up to the Sub Activity Date and so on. I have a formula =SMALL($E6:$E224,COUNTIF($E$6:$E$224,0)+1) that will select the earliest date out of the sub sub activities and sub activities but if one of the cells is blank in the sub activity or activity, then a #NUM! error is returned.
View Replies!
View Related
Variable Cell Reference Based On Minimum Difference
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 Replies!
View Related
If Statement To Accumulate An Amount In A Cell
In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance. Cell A1 = Food Cell B1 = £2 Cell H1 = (looks and if Food) adds £2 to cell
View Replies!
View Related
Limit The Amount Of Characters Per Cell
Is there a way of limiting the amount of characters you can enter in each cell? Also is there a way to apply this to an existing sheet that has cells with more characters in it than I would like? ie. If an existing cell has 25 characters in it, could i cut it down to the first 10?
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
VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time. Here is the ....
View Replies!
View Related
Formula To Put An Amount Into A Cell Next To Only Unique Item
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet. I can get a grand total by summing the unique items by using this formula: =SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970))) and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take." However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
View Replies!
View Related
Cell That Doesn't Reach The Desired Amount
function that add to any cell that doesn't reach the disired amount. If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it. For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.
View Replies!
View Related
Cell Format: Only Numbers (no Date) With Any Amount Of Decimals
I need a cell to restrict the input: -Only numbers are allowed. -No date posible. -Any amount of decimals (they must all be shown in the cell). I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May". I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Operational Model - Shift Start Time Plus Amount Of Hours Worked In One Cell.
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that? For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell). Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
View Replies!
View Related
Putting Another Code In The Same Sub
putting another code in the same sub. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set rng = Range("O2:O501") If Intersect(Target, rng) Is Nothing Then Exit Sub If Target.Value "X" Then With Target .Value = "X" With .Font .Name = "Arial" .FontStyle = "Bold" .Size = 14 .ColorIndex = 3 End With Plus this one Set rng = Range("P2:P501") If Intersect(Target, rng) Is Nothing Then Exit Sub If Target.Value "X" Then With Target .Value = "X" With .Font .Name = "Arial" .FontStyle = "Bold" .Size = 14 .ColorIndex = 5 End With
View Replies!
View Related
Putting A Table Into A Userform
I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell. I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
View Replies!
View Related
Putting 2 Conditions In IF Statment
I would like to check whether 2 conditions are satisfy in an if statement. One condition is of data type text and it is stored in a variable calls search1 whereas the other condition is of data type date and i intend to store into a variable calls search2. Could someone tell how to put search 2 into the below code. Basically, i want it to be like if Cell A is equal to search1 and CellB is equal to search2 then return value that is associated with the search in cell c ActiveSheet.Cells(r, 13).FormulaArray = "=MIN(IF(Sheet1!$A$1:$A$20=""" & search1& """,Sheet1!$C$1:$C$20))"
View Replies!
View Related
Putting Input Into A Macro
I would like to write a macro that would start by asking me something along the lines of, "Enter student # you want to print a report about." The # I enter would need to be stored as a variable in the VB code, and then my code would need to use the variable to do some search and replace operations. (Replace all instances of 10 (the default student number) with the variable I have specified). I don't really know how to even start going about this. How do I get Excel to ask me for a variable, and then use that variable in code?
View Replies!
View Related
Putting Spinbuttons In A Worksheet
The code below places a spinbutton on the worksheet. The linked cell = F10 This works ok. The orientation is vertical and max is 100 However I would like to set the orientation to horizontal and the max value to 1500. Which commands must be added for this in the macro below? I work with Excel2000.
View Replies!
View Related
Stripping Out Columns Then Putting Back In
Here is a simple exercise that is necessary only because of the large amounts of data involved. I have a spreadsheet of columns with budget and actual data. I need to strip out the budget columns (marked as budget) and total column. then add back in at a later date. It all has to stay in the same spreadsheet. I know it sounds strange but believe me its necessary. I was thinking of moving them to the right side of the spreadsheet. Then back at the later step. I have attached a simple spreadsheet showing the data on sheet1 and how it should look when stripped on sheet 2. and the later step would move them back to sheet3(which is the same as sheet 1).
View Replies!
View Related
Putting Text At The End Of Formula Results
I'm trying to enter relatively simple drug calculation formulas in Excel where the result is appended with the proper units to administer. For example, if I'm calculating for units of regular insulin with a formula such as: =0.75*f2 I'd like the cell to make the above calculation, and append it with "Units Reg Insulin" or something similar. Thus far, I've just been putting the units as text in adjacent cells but the result looks a bit messy. Is this possible? I'm using Excel 2007 and the Help index does not seem to offer much guidance about this.
View Replies!
View Related
Macro Putting In The Formula As Text Instead Of Results
My macro contains a VLookup formula that is giving me the text of the formula instead of the results. A portion of the macro is, as follows: Sheets("Survey Results").Select Range("c1").Value = "Resolving Group" Range("c2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Survey Invitation'!C[-2]:C[-1],2,FALSE)" Range("C2").Select Selection.AutoFill Destination:=Range("C2:C900"), Type:=xlFillDefault Range("C2:C900").Select Range("C2").Select The VLookup statement appears in place of the result when the macro is run. This is Excel 2007 and it runs to the end without error.
View Replies!
View Related
|
TRACKER
