The equation is basically: 10^[A/20]+10^[B/20]+10^[C/20]. Now imagine that there is no input value for C. I need to make it so that it won't add the bit I've highlighted in green.
View 5 Replies
i have 7 columns from A to E of horse racing data.
Col A is Date, B is Time, C is number of horses in the race, D is the result placing E is the Odds
The data is in Date order and then by time then by order of place eg 1st 2nd 3rd etc
I am trying to sort the data by Odds for each race
Pseudo code would be;
Read Cell C2 (the start of the data) to determine the number of runners (example answer 10)
Select range Rows A2:A12 (10 rows from data in C2)
Sort Selection by column E[code]...
I wish the above pseudo code works as it seems so simple
I have a summary sheet with a table and it needs to show how many people have 14 or more hours available based on a few reports. This is split by different levels (i.e Manager, Senior Manager etc) and various different areas.
The reports are placed in seperate sheets and i need the table to be automatically updated via either a formula or macro, rather then the manual process of going through each sheet and having to filter and then count.
I have a user form with a button to clear the entire form on a click. I also have the form seperated into 2 frames, how do I set a button to clear only one of the two frames. Also how do I set a button to clear one text box on a frame? I know it must be simple, but I can't seem to figure it out.
View 3 Replies View RelatedCompanyCodePropertyCount of StegdiwSum of WidgetsCompany XYZ
Example 1XXXXXX10.00Example 1 Total10.00
Example 2ZZZXXX22.00XXXZZZZ22.00Example 2 Total44.00
Example 3QQQZZZ33.00ZZZQQQ33.00QQQXXX33.00Example 3 Total99.00COMPANY XYZ Total1413
I am recreating a pivot table made by a prior user using new data. I have been able to figure out the format. But I would also like to color the sections in the same way, similar to that show above. How to color different sections of a pivot table?
My question is can I use cpu clock or some other means temporarily as an indicator before and after sections of code to display run speed until I find the best optimised code solution.
View 1 Replies View RelatedI have a very simple pie chart, no 3D angles, no shading, no tilting, no exploding (i.e. exploded view).
In fact it is so simple, it is just 2 sections.
I want one section to be GREEN, and the other section to be RED
Green is good, Red is bad, of course, just like traffic lights.
So, how do I get these colours to be green/red. Not worried about which data series (of 2) is a particular colour, I can swop these easily.
In my spreadsheet (27,000 + rows), there are sections (multiple rows) that are duplicates of other sections. A section is a header row followed by other rows of data.
The number of rows in each in section can vary from 5-20 – therefore, you cannot use the number of rows as a criteria.
However, each section has a header row that contains a four digit number. The following is an example of two such sections:
Basic -- Zero Line ==> Zero Line ID=7076 (submitted by Calvin Campbell)
Star Thru
Pass Thru
Trade By
Star Thru
one half Square Thru
[Code] ..........
As you will notice the header row contains an ID number. Every header contains “ID=” followed by the four digit ID number.
I need a macro that will identify the duplicate IDs and their sections and delete the duplicate rows of data.
I mention the following – which may or may not be useful.
- Not only are the header rows duplicates but the content of the entire section is duplicate
- There are no blank rows in the spreadsheet
- Entire rows may be deleted (not just specific cells)
- The data will be in Column A
- There’s only one duplicate of any given ID
There are a total of 2716 sections. It has been determined that 201 of them are duplicates. I can provide the 201 duplicate ID’s if needed.
I want to convert multiple sections that contains formulas to values. Usually i did this by coping range of cells, and paste as Values. But now i have multilple sections and excel doesn't allowe me to copy multiple sections. (i can not use clipboard, because there are more than 2.5 k rows)
View 7 Replies View RelatedI am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
I have one master excel file (masterexcel.xlsx) and 100 small excel files. The small excel files are saved as M30.xlsx, M31.xlsx, M32.xlsx, M33.xlsx, M34.xlsx....
I want to open the M30 file, copy a certain section and paste it into the master excel file, close the M30 file whilst saving the new information in a file called recording. Then open the next file which is M31, copy a certain section and paste it into the master excel file, close the M31 file and then open m32 and so on....
How do I create a loop for the files so it automatically opens the files in the order M30, M31, M32.......and performs the aforementioned actions.
I have attempted the following for m30 but i need to make it a loop for m31, m32, m33, m34, 35 and so on....
[Code] .....
I am trying to rank records from multiple sections of a single array. Since there are over 100 sections I want to use a formula. Using the MATCH function I have the first row for each section of the array (Col D below). The psuedo code of what I'm trying to do is:
RANK(B1,Bfirst row of section:Bfirst row of next section -1)
Example (results in Column C):
A B C D
1 West 100 3 1
2 West 150 1 4
3 West 125 2
4 East 50 3
5 East 75 2
5 East 140 1
I will also need to increment the section as I complete each section of the array. Am I trying to do too much in a single cell??
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
How do I get to see the equation behind Excel Functions? For instance, the
result of pmt() is not making sense to me, I'd like to see what this function
is doing and how it is calculating its value. Trial and Error is rather
time-consuming and tedious.
It looks like this (log(X/Y))/log2. The X and Y are celldirections (E2 or F4 and so on). How do i setup that formula in excell?
View 2 Replies View RelatedCan I use excel to get make an equation for 4 variables (x,y,z,w)
E.g.
a,b,c,d,3,f,g,h,i, would be constants
w = ax + by + cz + dx^2 + ey^2 + fz^2 + gxy + hyz + iyz
I tried regression but the equation looks like
w = ax + by + cz + c
Is it possible on ay other software e.g MATLAB
I have the following formula.
=SUMIF(hfx!A:A,"123456789",hfx!J:J)
very simple.
for the totals in J, if there is a (-) negative number, i would like that excluided from the equation.
Lets say I write an equation on my sheet such as:
='A1'!$H422*AH$138
but instead of hard coding the A1 sheet name, I want that to be a value that I can put in a different cell (lets say cell C1) on my same sheet in such a way that it will reference the value in my other cell (Cell C1) and put its value into that equation.
So if I make the value in C1 = A99 then the equation becomes
='A99'!$H422*AH$138
i am plotting graphs which i need the equation of the line from. i have been manually typing the x^3, X^2, X^1 and X^0 values into 4 cells which is then used elsewhere, but wanted to know if its possible to get some code to do that for me?
View 2 Replies View RelatedBelow is a nonlinear model, together with calibration data for which I would like to find the parameters Ro, Kc, a and b.
R = Ro + Kc * (1/(1 + exp(-(a + b*lnC))
Calibration Data
C R
0.00.3347
4.80.4201
7.70.5141
11.20.6177
14.40.6887
Given the calibration data above, how can I calculate or at least estimate these parameters?
I do not want to use an add-in program, like Solver, in Excel. I do, however, want to be able to use any existing functions, if necessary, in Excel. i'd appreciate it if someone can walk me through the steps.
I will like to ask, if there is a way to get in a cell the result of a simple equation without having to copy the contents of that cell with a = in front for example if i have in one cell
15*2+35*4+30/2
in the next cell to display the result
185
I need to take a number, multiply it by a precentage, & then round it to the nearest multiple of 5.
ex:
A1=142.5
B2=A1*50%,(then rounded to nearest multiple of 5)
if i obtain an eqn by plotting a graph in excel, etc,
y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01
and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.
I need to move a value to another field (with equation) and use the output for the same equation till limit is reached..
View 2 Replies View RelatedHow to add an equation to a drop down selection if you take a look at the picture in the link supplied, what I want to do is: I want the calculator to take the number in D12 then if the drop down box under it the selection is (as it is) too high then subtract 50 (k11) from D12 and display the answer in D14. I have listed the 3 drop down choices in column L, Lap 2 I would do exactly the same and so on [URL] .........
View 6 Replies View RelatedI have a cell on ' Sheet 2' with the following formula:
=MAX('Sheet1'!C3:'Sheet1'C10)
ie. it chooses the maximum value from column C between rows 3 and 10 on "Sheet 1".
Lets say the cell reference C10 is now entered into cell B1 on Sheet 2. How do I modify the above formula to reference B1?
I have tried INDIRECT function but can't get it to work.
I am trying to calculate the equation of a curve which best fits some data. On the x-axis is date and y-axis the readings. The curve is quadratic, so on the chart I am fitting a 2nd order polyomial and displaying the equation. To prove that the equation the trendline gives me is reasonable, I am applying the equation on the x parameter to get the y parameter estimate and looking to see the error between predicted and actual - see attached.
The equation and the output from it are clearly rubbish, even though on the graph the trendline is a reasonable fit - see attached. Has anyone any ideas why? I think it has something to do with using a date on the x-axis but no idea why. Is there a way to correct for this? I also tried using the following I to calculate the coefficients which came up with a reasonable forecast (although linest should only be used for linear data): =LINEST(C3:C15,B3:B15^{1,2,3})
I have the following 2 formulas that determine the top 2 scores and return the respective headers, but they aren't perfect.
=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))
The above returns the max, however doesn't take into account scores that are a tie or 0 values
=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))
The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).
How can I fix both of these equations and also determine the 3rd highest score?
I have two cells (c1 and c2).
c1 contains (2+3*4)*3 without "=" at start.
c2 must contain the result of c1 it means 42.
In the attached file I have a few variants I tried to solve it but they both don't fit.
Formula.xls
I'm familiar with 2d graphs, trendlines, and regression equations. Now I have some tabular data that has 2 input variables and a result. As with my 2d data, I would like Excel to create a polynomial equation of z from the x and y inputs. Can Excel do this, or do I need a plug-in or another software package?
View 12 Replies View Related
