I would like to write a formula to average cells F27 - F11007 in a spreadsheet for every 96 values. That is, I want the average of F27:F122, F123:F218, F219:F314 and so on up to F10912:F11007. Ideally I would be able to pull down the fill handle and perpetuate the formula since this is a lot of data.
I've been trying to use INDIRECT and OFFSET formulas but I get a #REF! or #VALUE error.
I have created a quick flowchart in Excel 2007 using the shapes and elbow/line connectors. All looks ok when on screen but when I move to the print preview, the elbow connectors used to loop back (horizontal, vertical and horizontal in opposite direction) in the process jump out of position.
If I print, it prints as per the print-preview.
Have alook at the screengrab showing regular view and print-preview.
Is there any way to fix the position of these lines?
Supplementary question: is there a straighforward way to creat a line break or crosssing?
I m having trouble getting 1 cell to reference 2 others and generate a answer bassed off of the information provided,,,im having a hard time explaining this so maybe you can loog at the attached sheet and offer some advise,,,i have directions on the sheet ,with what i need it to do.
View 4 Replies View Relatedkindly help in jumping locked cell in a proteced worksheet..
View 9 Replies View RelatedI have a list box where I choose a product brand, my choice leads me to a second box where I choose a supplier then I want to use a button to move to the sheet with the product list for that supplier.
This page is my menu page were the user selects what he/she wants to do. The button then takes them to the data page
Based on the drop down boxes there can be 4 choices of supplier and I do not want 4 buttons.
If supplier = " ACE" I need sheet ACElist
if supplier = " Himway" I need sheet Himwaylist and so on
Maybe I am suffering from information overload been working on this for too long, but I amn not seeing the solution.
I have an excel spreadsheet that goes from row 48 all the way to 1988, with no rows in between, and I cannot right click and delete the row, it acts like it is deleting it, then it re-adds the row
View 3 Replies View RelatedI have a loop that looks like so:
X= 1
Do
FUNCTION(A)
If Condition 1 is True Then...Make Cell B Blue
FUNCTION (B)
X=X+1
Loop Until X = 100
I want to make it so the "If Then" statement, if true, will cause the loop to skip FUnction B and head back to Function A with X equal to X+1. What do I put in the if statement to make this happen. NEXT X or something?
I've created a macro that runs for about 3 seconds. While the macro runs, the active worksheet display jumps around because of the various cell references being addressed in the macro.
Is there a way to stop the jumping around? How can a simple "Progress" screen be displayed while the macro is running?
I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.
If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.
I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.
How can I stop my subs from jumping from one to another?
Code:
Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Worksheets("TRF").Rows("36:41").Hidden = False
Worksheets("TRF").Rows("42:64").Hidden = True
Worksheets("TRF").Rows("65:76").Hidden = True
CheckBox19.Value = False
[Code] .........
I am trying to write a macro for transposing one row into multiple columns where the starting point for each column will be 15 cells starting from B4. I want to replicate the transpose for 200 rows.
View 4 Replies View RelatedHow do i change my data file from 1' intervals to 5' intervals?
The way it is now The way I need it
8300 8300
8301 8305
8302 8310
8303
8304
8305
8306
8307
8308
8309
8310
thru 11,000
My sheet consists of the following Data in the table ( to be increased to 99 rows or events )
I need to find the Next Date Occurence till the End_date
Next_Date1 = Start_date + Interval
Next_Date2 = Next_date1 + Interval
and so on till Next_Datexx is greater than End_date
I need a VBA code to fill the adjacent columns ie Next_date1 to Next_Date99 ( till the end_date), only if the Status is "DONE". If the Status is blank then the relevant colums must not fill.
I can do it with formulas, but need the VBA code.
Also the number of rows can be increased to 99 rows
Event
Start_date
End_date
Interval
[Code].....
I'm trying to make a histogram with the data analysis tool. The problem is that whenever I make a chart it doesnt show my intervals correctly. It puts them under the bars instead of between them.
View 3 Replies View RelatedI'm making use of a histogram to visualize my data. The only problem is that 90% of the data has a value which lies close to eachother but 10% is very different.
For example:
Interval-----Frequency
0-10------5
10-20-----7
20-30-----6
30-40-----0
40-50-----0
50-60-----0
60-70-----0
70-80-----0
80-90-----0
90-100----0
100-110---2
Now the histogram I will obtain won't look very good. Is there a way I can have an interval like 40 - ... which resembles everything above 40?
I'm trying to determine how much time our agents are spending for their lunch/breaks during 30 minute intervals. Range A8:A200 lists the agent's names, Range B8:B200 lists their lunch/break start times, Range C8:C200 lists their lunch/break end times, and Column E lists the times (8:00, 8:30, 9:00, etc.). If an agent starts their break at 10:57 (Column B) and ends at 11:10 (Column C), the value next to 10:30 (Column E) would have 0:03:00 and the value next to 11:00 would have 0:10:00. But would need to sum all the agents which took a break/lunch between 10:30-11:00, and 11:00-11:30.
View 2 Replies View RelatedIs there a formula that can identify all the consecutive intervals that have value below 90%.
View 1 Replies View RelatedI think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:
Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.
I have posted earlier thread on similar problem but i did not get much response.I am really having a tough time to record a macro, where my columns will be selected with an interval and then will be pasted in to another sheet....
View 6 Replies View RelatedBelow is the code i want to be run evey 2 minutes Thanks
Sub WebData()
Dim wSU As Worksheet
Dim wSR As Worksheet
Dim wSS As Worksheet
Dim iForRow As Integer
Dim iLastRow As Integer
Dim sURL As String
08:00 26calls
12:00 50calls
16:30 16calls
The problem is the data is output as above just outputing intervals with data.
I need to convert this data to 15 minute intervals as below:
08:00 13calls
08:15 13calls
12:00 25calls
12:30 25calls
16:30 8calls
16:45 8calls
Does Solver provide confidence intervals around its parameter estimates?
View 9 Replies View RelatedI have a range, intervals and need to find out how many observations are in those intervals...
RANGEINTERVALFREQUENCE-0.10773-0.10773-0.11 ?-0.10144-0.11 -0.05 -0.0983-0.05 0.01 -0.085110.01 0.07 -0.0810.07 0.13 -0.078760.13 0.19 -0.07580.19 0.25 -0.071360.25 0.31 -0.070750.31 0.37 -0.06930.37 0.43 -0.068420.43 0.49 -0.068060.49 0.55 -0.067390.55 0.61 -0.060030.61 0.67 -0.059350.67 0.73 -0.057470.73 0.79 -0.05290.79 0.85 -0.052570.85 0.91 -0.05190.91 0.97 -0.051340.97 1.03 -0.050011.03 1.09 -0.048051.09 1.15 -0.046941.15 1.21 -0.046411.21 1.27 -0.045311.27 1.33 -0.045291.33 1.39 -0.045061.39 1.45 -0.045021.45 1.51 -0.044341.51 1.57 -0.043321.57 1.63 -0.042231.63 1.69 -0.041661.69 1.75 -0.041441.75 1.81 -0.040051.81 1.87 -0.038661.87 1.93 -0.038561.93 1.99 -0.037811.99 2.05 -0.037112.05 2.11 -0.036272.11 2.17 -0.033292.17 2.23 -0.032872.23 2.29 -0.032672.29 2.35 -0.031322.35 2.41 -0.029372.41 2.47 -0.029262.47 2.53 -0.028292.53 2.59 -0.028222.59 2.65
I am trying to do is calculate (sum) results data for time intervals. For example, between 0500-0559hrs then 0600-0659hrs and so on. Data is collected at 1 minute intervals and may have a result of 0 or anywhere up to 30.
Column A has all time data e.g. 0500,0501,0502 etc
Column B has sum of instances e.g. 0,1,2,3 etc
I want to create 15 minute time intervals from the data. I attached the file for reference. I would like to sum the data column that corresponds to each interval. I created an example using the formulas and I want to be able to do that automatically either using VBA or the formulas.
View 5 Replies View RelatedI want to set something up so the following text, is repeated at set intervals down the sheet, a number of times defined by an integer input box at the top of the sheet. Next to each new text array, I'd like to define a dynamic name range, calling each an increment above the one before. So:
Question Title
Question no.
Assessment Target
Level
No. of Marks
With name ranges being, (for e.g.) P1QT, P2QT, P3QT. For three sets of papers for the "Question Title" range.
I am working on a spread sheet and am trying to use rolling averages of the last 3 months. I am using worksheets for each month with a final worksheet containing the 3 mo. rolling average. I tried using a formula with OFFSET and it worked fine if all the data was in 1 worksheet, but did not work for multiple worksheets averaging to another worksheet in the same workbook.
View 12 Replies View RelatedI am trying to work out the average volume of our daily downloads. I have used Vlookup to automate this. When there is no data it returns #N/A which is no problem. The problem arises when i try to work out the average for the month or peaks (MAX sum).
I have tried several variations of sumif/if/isna etc etc and am not having much luck. Below is an example of what i am trying to average out:
#N/A620#N/A
That is 4 different days download data - I want it to ignore the 0's and #N/A and return the average for the days we actually had a download.
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.
View 2 Replies View RelatedI have a spread sheet with col's that I want to average their totals, but would like them to be rounded up or down.
View 3 Replies View RelatedI am using the formula =AVERAGE('Year 13'!BV:BV) to work out the average value in column BV.
However, I would like the value to change if I filter one of the other columns, is there any way to do this?