Calculate Sum Where Column Contains Intermittent Data?
Jun 13, 2014K
L
M
N
L
L
-2.33
ADVERTISEMENT
Add Intermittent Cells To Column?
Feb 3, 2014I am creating a report which lists gifts during a two week time period. Most donors have only one gift, listed in column C and D. Some donors have donated twice or three timies in the two week period. I would like these entries, which are intermittent in Cloumns H-I and K-L, to be automatically moved under the corresponding cells in columns C and D. This way an individuals two or three donations all end up in one column.
View 4 Replies View RelatedVBA To Autofill Formula Over Column Data Calculate And Replace Original Data
Nov 14, 2008Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
Vba Form Crashes Intermittent
Feb 6, 2009I have written a fairly large spread sheet, and I have consistently had excel crashes when the first form initialises at startup.
However if I resave the workbook over itself or sometimes as another name it will then work. Once it works - it will always work while I dont restart my computer. The next day the same workbook will crash on form startup. Once this happens t always crashes on startup. If it works OK first up it always works. However if I edit it the process may happen all over again. I am 100% their is no error in the code as in certain stages it has worked for months. It seams like some bug in excel?
Has anyone ever had this issue?
Find Intermittent High Value
Jul 8, 2007I am analysing market data to determine when trades should take place and when they should be closed. This works well. But I need to find the intermittent high value between the dates for purchase and the closing trades, and then insert these values in column S in the attached worksheet.
I don't know if a function can find these values or if a macro is needed (or if I should amend the existing macro that currently finds the
closing trades).
This macro is included in the attached spreadsheet. It Is:
....
VBA UserForm Code Causes Intermittent Freezing
Feb 6, 2009I have a multipage userform with about 7 pages. When page 5 is activated, it caused excel to completely lock up. This does not happen everytime but randomly, and I can't seem to figure out what is causing this.
Here is the code -
If Me.MultiPage1.Value = 5 Then
Call LaborCosts
End If
Sub LaborCosts()
If Not Range("A46").Text = "" Then
frmpricingtemplate.Label29.Visible = True
frmpricingtemplate.TextBox46.Visible = True
frmpricingtemplate.Label29.Caption = Range("A46").Text
Else
Intermittent Error Creating Worksheet
May 3, 2007I searched the forum for an answer to my problem since I'm positive its a FAQ. Well I was right and many other users have asked a similiar question. My problem is this: I am looking to find the average of a set of numbers in column T that meets certain criteria. I was suggested by other user to use this function which finds me the total number. =SUMPRODUCT(--($E$1:$E$400="S"),--($H$1:$H$400=17),--($U$1:$U$400<>"BEBLT"),--($U$1:$U$400<>"NVLIV"),--($T$1:$T400 > 0),$T$1:$T$400)
I have tried inputing Count, Countif, Average in place of Sumproduct as well as Count enclosing the entire sumproduct function. Nothing seems to work for me. I have also tried using a SQL statement in my VBA to provide a count but it doesn't seem to produce the required result. The above function works like a charm for finding the sum, but I need to find the average of this number thus I am looking for the Count equivilant. I'm sure this is a simple problem and I'm still a rookie when it comes to functions in excel.
Intermittent Crash When Running Macro
Apr 9, 2008I have having alot of frustation with a big group of Macro's I have written,
all the macros work and run fine most of the time but they crash Excel on occasion and the only way I have been able to fix it is to go into Visual Basic and opening the project up (it is password protected) but not making any changes, after doing this it seems to magically stop crashing, for a while, then doing it again.
I can't work out how or why that would make a difference,
I dont expect you to try and work out a problem in the code or anything like that cause it is too big and would be unreasonable of me to ask,
What I would like to know is if anyone else is aware of this sort of issue, why it happens and if there is any good cure for it?
I am running excel 2003 if that also makes any difference.
Intermittent Print Overflow Error
Apr 10, 2008I've created a price sheet that I distribute to customers that is very customer specific. I have to add spacing, logo's, borders and highlighting depending on the products being listed. Because of all of this specialization I'm controlling the page breaks and overflow. I have a counter to keep the current line number of the report and another to determine the row within the current page. I've designed my form to have 77 lines per page and I call a pagebreak/overflow routine when I hit line 72 of each page. Works perfectly when I test on my PC and works about 80% of the time when I send to customers, but I have about 20% who have problems with page size and overflow. In working with them I've found that it's not just the printed copy, but also when they print preview on screen. The first few lines of the second page print at the bottom of the first, so the top of page two is cut off. Then 4 lines from page three print at the bottom of two and so on...
View 3 Replies View RelatedVBA Code Causing Intermittent Crashes
Aug 12, 2008I have a set of userforms, two of which are big with 4 pages and many buttons, labels and textboxes that run or are involved in code being run from user input to the userform.
For some reason certain codes are cauing excel to fully crash, executing these codes will make excel crash each time and the only way to prevent it (that I have found) is to open the userform in VB editor and then close VB editor again, the code will then work perfectly for a while and then it will start crashing again (it is normally a few saves and open/closes later that this occurs).
Logically thinking, opening the userform in VB must reset something that gets corrupted or set incorrectly....
Line Chart With Intermittent Zero Values - How To Display?
Mar 16, 2009I am charting the following data (for a lot of countries, and the data changes based on a pull down menu which selects the country).
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145n.a.150140166
Country3142154n.a.170160
What I don't want is the line on the chart plummeting to zero when there is a "-", i.e. no value.
If I chart this instead:
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145[.....]150140166
Country3142154[.....]170160
..., where [.....] means a blank cell, then the 145 does now show up at all.
Rolling Look-backs In Excel With Intermittent Zero Value Cells Throughout
Nov 10, 2013I've got a spreadsheet that lists employee hours worked by week, I'm trying to create a function that would give an automatic look-back of both 90 days and 6 months. I've found some useful answers in other threads, but they tend to hinge on a zero value cell signaling the latest week (not) worked; however, for this purpose, some employees may have zero value fields for some weeks but have worked others.
I'm looking for a formula that can be uniformly applied to all employees in the spreadsheet and average their weekly hours for the past 90 or 180 days without having to update the formula each week new hours are entered.
Intermittent Run-time Error '75' Loading Form
Jan 4, 2010Intermittent run-time error '75'
Could not find the specified object.
Debug shows VBA Code that simply loads a form called controls
Sub loadcontrols()
Load Controls
Controls.Show
End Sub
Generally works upon restart. Can load and unload the form numerous times. Seems loading the form after a few minutes results in the form failing to load generating the error. Found no consistent cause thus far.
Results in a hard crash: Restart Excel as only option.
The Form Code for Controls loads a modeless transaprent form. Has been working for months. Don't know if this error is new or has been there all along.
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function ReleaseCapture Lib "user32" () As Long
Private Const GWL_STYLE As Long = (-16)
Private wHandle As Long 'For moving the form
Private m_objCFormSkin As CFormSkin 'For transparancy
Workbook Causing Intermittent Freeze & Not Responding
Dec 31, 2008I recently added some command buttons to 6 excel files each with about 20 sheets. most of the sheets got 6 new buttons. After I added the buttons I would get memory errors when I treied to open other files. So I upgraded to Office 2003 and Excel 2003 because I read that it had increased memory available. It did not seem to help much, and now on average about evry say 10 minutes Excel hangs for about 1o seconds, sometimes saying "Not Responding" up the top. Then it starts working again. MY PC has Server 2003 and 1GHz and 512MB of Ram. Here is some info:
=INFO("memavail")1048576
=INFO("memused")50769256
=INFO("totmem") 51817832
=INFO("numfile") 173
=INFO("osversion")Windows (32-bit) NT 5.02
=INFO("release") 11.0
I do not think the PC memory or chip is being stressed it seems to be Excel's memory?
Vlookup Won't Calculate: Pull Data From "ILComp" Column B
Jun 11, 2009for some reason my VLOOKUP formula isn't calculating. i want the VLOOKUP to appear in column E of "2ILMaster." I want to pull data from "ILComp" column B. i've tried formatting both columns as number and general, and the formula still doesn't work.
View 2 Replies View RelatedCalculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
Oct 2, 2008I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
Calculate Average For Column Based On Moving Start Column?
May 3, 2013I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
Find Duplicates In Column A And Calculate Difference Between Times In Column B
Apr 4, 2009I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
Formula To Calculate Average Values In Column B Where Value In Column A
Mar 11, 2013I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
How To Calculate Sum On Column B When Column A Shows 2929
Mar 6, 2012I have Column A and B
I want to calculate the total of Column B where by there Column A shows figure "2929".
Calculate Certain Items In A Column
Nov 3, 201247.00% Defense
Defense
83.00% Defense
answer here
54.00% Attack
26.00% Attack
19.00% Defense
I am looking for a formula that will calculate all the Defense in column A if Defense is in B1 and calculates all the attack in column A if Attack is in B1
Calculate Totals In Column AB
Aug 21, 2013I have a large spreadsheet, almost 9000 rows. I need to calculate the totals in column AB. There is a blank row seperating each group I need to total. Is there a faster and easier way for this to be done without adding the formula manually to each row? Here is a small example - I need to total the data under Total_USG
TOTAL_USG
LETTER_SMPLX
LETTER_DPLX
LEGAL_SMPLX
LEGAL_DPLX
154
162
[code]....
Calculate Column Total Via Vba
Apr 5, 2007I've got an excel spreadsheet that is using imported figures from another source. I'm just trying to create a macro that will locate the last cell in Column N, Then will total all the cells above it. I've got the below code to select the last cell in Column N.
myrow = Range("N1:N" & Range("N65536").End(xlUp).Row).Count
For i = 2 To myrow
Range("N" & i).Select
But I can't figure out how to do the sum part. I've tried to record a macro but to no avail. Since the number of rows will change each day, I need to somehow catch the range. This is part of a bigger project, but if I can get this part working, I think I'll be home free. Attached is a small example.
Calculate Hours: Sum Up The Minutes In The Particular Column??
Oct 19, 2008I want to Sum up the minutes in the particular Column, so that at the end with i put formala, the output would be X Hrs X Mins, one more thing i would like to do in it is , I want to copy paste the value of formula in next column, for eg the formula is in A6, i want value of that formula in B6.
View 4 Replies View RelatedArray To Calculate The Mean Score For A Column
Dec 3, 2012I tried to use an array to calculate the mean score for a column: if a cell has a value 'A', then it is assigned a value 1, if 'B', then 2, if 'C', then 3.
But this resulted an error.
File attached here. Example.xls
Calculate MTD Value Based On Column Reference
Feb 12, 2009I have some financial data that has been exported via sql from out financial system that lists transactions for each month in a column with 12 columns representing each month of the year in Sheet1. (My financial year runs July - June). The column headings are MONTHLY_TYR_BALANCE_01 thru to MONTHLY_TYR_BALANCE_12 where MONTHLY_TYR_BALANCE_01 = July, MONTHLY_TYR_BALANCE_02 = August etc.
To the right of this data I wanted to have one column that will list the MTD data each month ie in July this should reference to the column headed MONTHLY_TYR_BALANCE_01, and in August this should reference to the column headed MONTHLY_TYR_BALANCE_02
I can get the end user to type the month number into a field and reference back to this but I just can't think how to convert this to a column reference that I can then use.
Using DATEDIF To Calculate Average Age Of Column
Jan 5, 2014I have a DATEDIF column which I have used to calculate the age of a group of people from their dates of birth. This has produced results in cells which look like this: Age is 44 years, 1 Months and 28 Days
Where I am stuck is that I need to calculate the average of this column so I get the average of the total group. Is this possible?
Calculate Sum Of A Column If Month And Year Is Correct
Jun 19, 2014So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.
Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.
Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.
So, I want to sum all the "x" for February 2014.
I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.
See attached file for example and for my =sumproduct formula.
Excelforumexample1.xlsx
Using Countif To Calculate The Number Of Days In A Column
Dec 16, 2009I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.
View 4 Replies View RelatedCalculate Contents Of Column Where There Are Empty Cells
Jan 16, 2013In the Wheelchair 1, 2, 3 & 4 sheets in the attached workbook, I would like to do a simple calculation of the contents of column 'H'. Instead of it adding up to just a few £'s I'm getting the result of something like -£5880.00. I think I know whay is wrong, but I don't know the solution to it. Column 'H' populates when a date is put into Column 'E' So if cells if cells in Column 'E' are empty then column 'H' doesn't do the sum.
What I think I need is a bit of formula to put into column 'H' to tell it to ignore blank spaces and count what is there.
Wheelchair Hire-Sample New (1)(1) (2).xlsm
