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.
I have this formula that is not quite working...
this works fine =IF($B$1
I want to find out the minimum $ amount in a row with multiple columns but some columns will also have numbers that cannot be converted to string.
What formula could I use to only find the lowest $ 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 9 Replies View RelatedIm 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.
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 trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
View 14 Replies View RelatedI 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",""))
How can I stop #DIV/0! in a cell which has a formula relating to a cell which will have a number inputted but at the moment is empty, without me putting a '0' in?
View 9 Replies View RelatedWhen I try to run the macro, an error comes up saying application-defined or object-defined error. The following code is the line in which I receive the error.
VB : tool.Worksheets("ALL").Cells(row1, "J").Formula = "='[TRB Database.xlsm]TRB Database'!$I$" & A
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.
I have a sheet with 2 colomns: client number and document number, as follows:
Client number Document number
1234 100012
1234 100042
4321 102233
4321 249123
4321 533213
4567 134123
7890 123451
7890 125675
7890 357353
I need to have a unique client numnber on 1 row with all his documents in one cell, as follows:
client number: document number
1234 100012, 100042
4321 102233, 249123, 533213
4567 134123
7890 123451, 125675, 357353
This seems simple but I can't figure it out. I want to grab the name of a worksheet and place the name in cell A1.
View 9 Replies View RelatedIt 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?
I am trying to put text form 3 cells into 1 string. I got the 1st 2 but the 3rd I only want to extra the first part of the cell up to the 1st comma. This is what i have so far:
[Code] ......
For example cell E81 would contain "abc, def, ghi" . I want to add to the string above only "abc" and not the entire cell content.
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
Putting Text From A Textbox To A Cell On A Sheet. how do you do this?
View 5 Replies View Relatedi am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.
A B C D
% of Availability
0.00%
80.00%
Need to trimming certain information contained in 1 cell and putting them each in a column. I could do left, mid & right but this time there is inconsistency in the content of the cell.
I've attached a file which will further give you an idea exactly what I want (formula to be in Column M to P).
How to store a value in variable after concatenation of two values and putting it into the same cell.
Let assume, in cell A1, we have value 1 (numeric). And in code i have a variable with stored value as "%".
Now i want to concatenate 1 and % and put it back into cell A1 as 1%.
I have a written a code, but seems to be wrong one.
Sub Percentage()
Per = "%"
lr = Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
For i = 10 To Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 4).Value = "p" Then
[Code] ........
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
I'd like to be able to unlock the whole of a workbook by putting the password into a specific cell and then the whole thing locks again when it is saved. I have been able to find the coding to do this for a worksheet but I'd really like to be able to unlock the whole project.
View 7 Replies View RelatedI want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
If the result in cell i1 less than 1 to change it to 1
View 2 Replies View RelatedI 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.
I am trying to write a macro where the cell value will be decreased by 1 each time the macro is run, down to a minimum value of 0
The code I have to do the reduction is below, and Im guessing its a simple one liner to check the value before applying the reduction:
Sub DecreaseValues()
'
' Decrease values by one Macro
'
'
Range("B29").Select
[Code] ........
I would like to generate an average of Row 1 to Current Row in a column. The Average column is the column that I would like to know how to generate.
Date
Value
Average
08/04/2012
5
5
08/04/2012
10
7.5
[code]....
I got close with =AVERAGE(OFFSET($D$1,COUNT($D$1:$D2),0,COUNT($D$1:$D2))), but after a couple rows of it being correct, the average starts to fall.
Using the above:
Value
Average
Should be
7.07
7.07
7.07
7.23
7.120
7.15
[code]....
I'm trying to analyze the pricing we get from our shipping courier. Although the courier gives us incentive discounts, they also have a minimum charge that supersedes the discount. In other words, suppose the "Minimum charge" is set at $6.24. If the published "list rate" on a package is $10 but we get a 50% discount on all shipments, instead of paying $5, we pay $6.24. Basically, between the post-discount amount and the "minimum charge" we pay whichever is greater/higher.
Now, to the Excel part: I have cells with simple formulas applying our % discount to our carrier's published list rates to calculate the resulting price (i.e, where cell A1 contains published list shipping rate and cell B1 is our discount %, so in cell C1, I have a formula: =A1*B1). I would like know how, if the result of the formula in cell C1 is less than $6.24, that the cell could display $6.24 rather than the actual result?