I'd like to minipulate a single value from 2 different points - for example: I have a weight of 8lbs. on tab B and would like to change that weight using a spin control and also be able to change the same weight of 8lbs. using a spin control on TabD.
View 3 Replies
I am using old macro code in excel written several years ago. I am trying to educate myself to update and improve the macros I am using but not making much progress. This macro is used to record data from a DDE link to a Data Acquisition System. The D-AQ system sends the data points to r5c3:r5c9 typically. The macro then records the data points in those cells to the next row in sequence on a specific time interval. However, the macro is recording duplicate entries for each set of data points. Sometimes just two, but has been as many as four duplicates. I have a remove duplicates macro to run, after the fact, but preventing them from occurring in the first place is the goal. I included the application.wait method at 10 second intervals after the application .OnTime method, but it isn't preventing the duplicates.
Dim delta_time As Date
Dim Count As Integer
Dim n_rows As Integer
Dim start_row As Integer
Dim col_mov As Integer
Sub initialize_button_set()
start_row = 7
Count = 0
n_rows = Worksheets("sheet1").Cells(1, 2).Value
delta_time = Worksheets("sheet1").Cells(2, 2).Value
Application.OnTime Now + delta_time, "get_data"
Rem Worksheets("sheet1").Buttons.Add 275, 0, 72, 36
Worksheets("sheet1").Buttons.Caption = "STOP"
Worksheets("sheet1").Buttons.OnAction = "Halt"
End Sub...........................
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
Currently I'm working on a prediction game using excel. Can excel do the tabulation itself(Points awarded) if I just key in the result? Can anyone help me? Thanks in advance.
Examples below,
Result 3-1
Name Predicted Scored Points
Alex 3-1 5
Brian 4-1 4
Charlie 2-0 3
Derrick 3-3 1
Eric 0-2 0
Point System:
1 point for correct goals/no goal by either both teams
3 points for correct result ( Win, Lose, or Draw - say result is 3-1, 3 points if you predicted a Chelsea 2-0 win)
5 points for correct match results
No points for incorrect score and results.
I have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........
I am doing a golf predictor where I am using all 3 sheets in a spredsheet.
In sheet 1 I have the teams of players my friends have picked.
Sheet 2 I have the prgressive scores for each team done on a weekly basis.
Sheet 3 I have the names of 56 golfers and the points each can win for finishing anywhere between 1st and 22nd in a competition.
What I would like to know is there anyway I can replace the values 1st to 22nd with points (i.e., 1st = 400 2nd = 340 3rd =310 and so on down to 22nd) also is there a way I can transfer values between sheets.
I m trying make a spreadsheet up to fine the following i have also included a file
raymond ran 11 seconds so he gets 150 points which is .6 of a second better than his Personal best time (PB's) but matt ran 13.6 seconds but it is 1.1 seconds slower than his Personal best time so he is minus 275 for every .100 of a second they get 25 points added or taken off
Need a direction for this type of calculation.
View 2 Replies View Relatedim doing a excell sheet for a fantasy league that i ahve created. I have a "total" cell to combine the total points for each player. I then used the "rank" formula to rank the highest points to that player. My question is...can i show like how many points behind everyone else is from who ever the "rank 1" is in?
Not sure if you can do 2 formuals in same cell or not. maybe i could have rank and how many points are behind in same cell
Example. lets say
Player points rank
Tim 5000 1
Jimmy 4000 2 -1000
Sara 3500 3 -1500
This may sound like a dumb question, but hang with me. I have two series in an xy plot. One is a line (withOUT markers) and one series is just markers (no line).
The line without markers is created in a way such that it forms a box and whiskers plot. The other series, are trial values that go into the box and whiskers.
I have desperately tried to get the second series (just points) to plot BEHIND the first series (line, no markers). Changing the series order does not work unless both series have lines. I have tried this in a variety of ways (including writing a macro which draws the markers first, and then the lines), but that doesn't work either!
I'm constructing a basic report in excel.
I'd like to add a bullet point for each paragrah, however, excel doesnt seem to have the option Format/paragraph like in MS Word.
I have a data for sedimentation along a pipe. I measured sediment concentrations at different lengths of the pipe, after different intervals of time. So I have 35 concentration values, take at 5 different points along the pipe, at 7 different times. Is it possible to plot a chart with these 35 points, with depth on y-axis, time on x-axis? These 35 points are percent removals (0-100). .
View 4 Replies View RelatedI have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
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.
I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.
Column A of the file shows an amount, when summed, give a total of -3.5725E-09.
Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.
Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.
How to add numbers such as if a person has 15 points how do I input two points and have them added up to 17 points does it require multiple sheets or is there a command to do this seemingly simple operation.
View 4 Replies View RelatedI have a sheet named SymbolList in which I list stock symbols in the first column, and have row headings in the first row. I use the following sub to get stock quotes from yahoo. I developed a loop that works in steps of 200 because yahoo has a limit of 200 stock symbols that can be acquired in each request. The loop should go through the whole list in loops of 200 until the last row or if by mistake there is an empty row. The problem I face is that the loop does not work in a consistent manner, for example if I use 200 as the step limit, the sub skips all loops and only gets the last one, if I use a limit of 100 the sub skips the first loop and then proceeds, it only works well at a limit of 50.
I have a spreadsheet that I have been using for some time.
Suddenly, in just the Worksheet_change event procedure, break points will not work.
In the same sheet break points do work for other events.
I've tried everything.
I have a data and it has been plotted into a graph. Here's what i have: I have 2 points, point A and point B with caps, 1.00 and 2.00 respectively. So now i need to draw 3 parallel lines, i.e from the caps and the main point itself. How do i do that? Manually will be of course selecting a line from the shapes. Any suggestions?
View 14 Replies View RelatedI have a spread sheet in which I enter players names and the place they
finished for a certain event. I would like the sheet to calculate their
points according to where they finish and assign it to them along with a
formula that calculates a bonus point value according to the number of
players in a certain event.
1st place-450 pts + 30% bonus
2nd place-300 pts + 20% bonus
3rd place-180 pts + 12% bonus
4th place-120pts + 8% bonus
5th place-105 pts + 7% bonus
6th place 90 pts + 6% bonus
7th place 75 pts + 5% bonus
8th place 60pts + 4% bonus
9th-16th place 15 pts + 1% bonus
16th place 15 points
Bonus point formula is (# players X 5)x %
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to be
in every cell in that column.
NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0
I figured out the rank formula, but I can't figure out how to assign the
points.
What I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.
My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.
If I replace the #N/As with say 1, then it messes up the trend data points.
It seems like I need to be using the trend command, but any solution would be welcome.
I use data in column A:B to draw stacked line chart . My question: I want to add column C Evaluation on every point on stacked line chart.
Sheet1
*
A
B
C
1
*
Sales
evaluation
[Code] ...
I'm trying to write a macro that will format my chart data based on the corresponding cell value. I have the following code, which runs without error, but the formatting on data points doesn't change. I can't seem to get this to actually change the color of the data point in the chart.
Code:
Sub FormatChart()
Dim wb As Workbook
Dim ws As Worksheet
Dim wks As Worksheet
Dim y As Long
Dim j As Long
'this routine will format the charts on worksheets "SL11730" & "SL14100" 'so that data points equal to or above 98% will be green filled and 'all other points will be red filled
'set loop counter to two (contract (4100) & retail (1730))
j = 1
'start loop
Do Until j = 3
If j = 1 Then strwkb = "SL11730"
If j = 2 Then strwkb = "SL14100"
[code].....
I have 3 Columns... I would like the column2 row1 total all the values of column3 using stopping when there is a another value in column1...
Column1
Column2
Column3
MasterItem1
SumOfMasterItemMinions(1,2,3,4)
Minion1
Minion2
Minion3
Minion4
MasterItem2
SumOfMasterItemMinions(5,6,7)
Minion5
Minion6
Minion7
But i don't know how many Minions there could be as they're selectable...
i have a vlookup formula that i am using and i have two question
my first is something that i noticed while reading mrexcels book and comparing to what i was told here.
i was refered to use column()+0 instead of what mrexcel says of col(the number)-how many to get the number i want.
now my setup is like this
in a1 i have a sheet named anaheim batting totals in a2-a16 i have week1-week16. now i have my formula that i enter in b2 that uses the reference in a1 to look up in the range in sheet week1( i manually entered the range location.) i then copy paste it down to b17 with the dbl click trick and then manually go into each of those formulas and change week1 to week2 to week 3 etc. i want to be able to reference the A column title(whichever week appears) and the a1 cell in the same vlookup formula. in essence to say lookup a1 in this range ex.a1:l450 in the sheet that is in the corresponding a column,i.e. week1.16 all set the same with the same range. the rest i can figure out, but i would like to have one formula to copy to all cells of my chart.
I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?
For example:
1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75
I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.
i have a spred sheet that i am trying to give them scores from highest 10 down to 6 then anyone under will get 5
However if there is a tie for first they will each get 10 then the next will only get 8
ex)
a1 b2( this would be there score
3.45 10
3.45 10
3.40 8
3.20 7
2 6
1.99 5
1.89 5
1.5 5
1 5
I have a line chart that has 4 points. Is it possible to make the data points point to the next point? If so how can I do that?
