I have three different equations i am trying to combine to one line so I do not have to use three columns.
Equation 1.
If recommend quantity is < 100, and the Number of times part number used is one, then Recomnend quantity was used
Ex =IF(F277 100 and the Number of times part number used is greater then one and less then 10, then Recommend quantity was used
Ex =IF(G277100,*and(E277>1),F277*2.0)
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
File I have attached or made a link for here Untitled-2.jpg
I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.
I have two line charts in Excel 2007.
One chart has the values going down from upper left to lower right
The other chart has the values going up from lower left to upper right
Is there anyway to merge these charts to see if they intersect at all?
I am trying to create a 100% stacked column which has a superimposed line chart over the top of it.
Basically % of yes and no for two separate datasets, but which share the same characteristics. The x-axis is months of the year.
CSP Yes
CSPNo
All Yes
All No
Apr-13
6
19
[code]....
I can create 2 separate charts. One for the yes/no of one set as a 100% stacked column. I then use 'no fill' on the No column to stop this from being seen.
I can create a 100% line chart for the second data type, and 'no fill' the 100% line that appears.
However, I can't merge the two charts to show the Yes % for one dataset as bars and the Yes % of the other as a line.
I am using Excel 2010 on XP.
y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4
Above is the polynomial equation of a trend line as shown on my chart, I have extracted the numbers into separate cells using LINEST, so now I have in cells A20 to E20 the numbers 1.083333333, -7.333333, 16.416667, -6.16667 and 4 respectively.With a "y" value of 25 how do I go about solving for a value of "x".
I've been stubbornly struggling with this on and off for a couple of weeks now and I'm not getting any closer to working it out and the websites I've found explaining the procedure all seem to go over my head by the time I get into the fourth or fifth paragraph.
I am given the function
y = a x + b
And I am supposed to ask the user to enter a value for a and b twice
So I could have 2 equations and solve them
The problem is I am not sure how to solve the 2 equations using VBA?
how the Process to solve the following problem:
1A = 15x + 32y + 5z
1B = 6x + 2y + 8z
1C = 3x + 11y + 19z ...
I was wondering if the solver was the only way to solve implicit equations. I am looking for something that will automatically solve an implicit equation after the appropriate information is entered into the referenced cells.....
View 9 Replies View RelatedI don't actually own Excel as yet - but I'm wondering if someone could tell me whether Excel is capable of solving what I'm told are 'polynomial equations'. An example of the sort of equation I hope to solve is: d/(1.18 + d) + d/(1.86 + d) + d/(9 + d) + d/(8.8 + d) = 2
d=? {I happen to know d = 3.65(approx) in this example btw}
Often there will be many more than four terms...
I am trying to solve a set of simultaneous equations by using SolverOk function in VBA.
I dont want to use the 'SetCell' option so, I wrote the following
SolverOk MaxMinVal:=1, ByChange:= Range("V34:V36")
SolverAdd CellRef:=Range("AK4:AK6"), Relation:=2, FormulaText:="0"
SolverSolve
but I get an internal memory error. However, if I use the solver dialog box directly from excel and not through a macro and keep the 'Set Target Cell' as empty, I get the right solution.
I'm trying to find definitions/commands to make excel find the best combinations of several defined scopes and restrictions to make an output number as high as possible.
This is by using a combination of several different equations, maybe excel has a magic trick for all the variables and formulas.
Attached File: forum_heatexchanger.xlsx
I have a system of 3 equations with 3 unknowns that I would like to solve in Excel.
The equations are
24.792 = A*e^(B/(100-C))
9.857 = A*e^(B/(70-C))
4.590 = A*e^(B/(40-C))
Where e is the exponential function.
I have two cells with the following equations:
H3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,13,FALSE)," ")
I3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,10,FALSE)," ")
In an adjacent cell, I am trying to use an if statement
J3=if(and(H3<1,I3<1),"yes","no")
I am not getting a return in cell J3.
Can excel solve an equation for x with two variables.
Example: 102=125000(X-910)/50000.
I need excel to solve for X
The variables are 102 and 125000
is it possible to export a trendline equation from a graph to a cell, or at least to export the coefficients? If yes, how?
View 4 Replies View RelatedI am trying to analytically solve an equation to establish a physical value. Unfortunately, it's difficult to do the algebra, so it needs to be done this way.
I have an experimental value for this in "J2", so what I want to do is change F2 to that "I2" is equal to "J2"
Is it possible to get excel to match these values - I have about 200 different cells of this?
The formula is in cell "I2"
=B2*((1-(C2/D2)*((F2/G2)^1.2))^-1.5125)
I am about to take a corporate finance course. The textbook uses a ton of basic (complex to me) equations to calculate interest rates, bond premiums, dividend reinvestment scenarios, etc.
I know that Excel has a bunch of specific functions to calculate interest, NPV, etc. but I am NOT interested in using those because I would like to have Excel "do the math" once I enter the formulas (or parts of formulas) correctly.
how to enter the equations below into Excel without using built-in formulas (I'm also not looking to use the Equation editor because I actually to calculate the answers, not just have the visual look/feel of the equation).
1R2 = [(1 + .0194)(1 + .03)]1/2 - 1
If done correctly, 1R2 in the above should equal 2.47%. I am totally fine doing parts of this in separate cells if necessary.
And something more complicated like the below. I wrote out the word "delta" because I don't know how to create the "triangle" symbol for delta on this forum post. According to the text, for securities with semiannual receipt (compounding) of interest, it is represented as:
-D = (delta P / P) / ((delta r sub b) / (1 + (r sub b)/2)))
Or in English, I believe that can be expressed as "negative D equals the quotient of delta P divided by P (in the numerator) divided by the following: delta r sub b divided by the quantity 1 plus r sub b divided by 2"
And finally
V sub b = 1000 / [1 + (.10/2)]2(4) + 50 times the following compound bracketed expression [1 - (1 / [1 + (.10/2)]2(4)] divided by (.10/2)]
I need to automate the attached as per the following (by not recording macro unfortunately).
Sheet one has a dump of data. First, I need to change the data format from yyyymmdd to dd/mm/yyyy. Then I need to filter for region EU and only copy the filtered data from columns A, C-D, F and the difference from columns I-J to Sheet 2. Then in Sheet 2, I need another column to calculate the difference per unit.
Any way to exclude colored cells from addition in a column. I will attach a simple example document.
I am building a spreadsheet to keep track of my monthly spending, and debt balance. I keep track of the total amount of bills I pay each month. As I pay them, their respective line is highlighted green in the spreadsheet. I would like to create a macro/formula which would sum the remaining cells in the column which are not highlighted to display the remaining monies which need to be paid out. I'm not sure if this matters but I am using office 2010.
billSample.xlsx
I had a question about adding numbers, sort of like algebra equations (best description I can think of) into a spread sheet. Example:
Cell A1: 8
Cell A2: 8l
Cell A3: 8ls
Cell A4: 8l
Cell A5: 8
Cell A6: -where I want the total to be displayed in the following way: 16+16l+8l
Is this even possible to calculate by grouping like numbers?
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedOur small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.
=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),
When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.
B C D
Atex ID#(unique id) Campaign name Site
Row 7 1st occurrence of IO# 777
Row 8 2nd occurrence of IO# 777
Row 9 3rd occurrence of IO# 777
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
