I am using Arrays throughout a large report and each time I move around in the report or simply open the report I have to wait 5 to 10 seconds to wait for the cells to calculate. Is there a way to prevent this from happening other than changing the calculation option to manual.
Is this and array problem? I don't get this when I use multiple vlookups and choose formulas? Any ideas to speed up or prevent all together?
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies View RelatedI have a spreadsheet with data in all different rows.
I'm trying to delete all the blank cells in between.
I tried go to special, blank, and delete cells,
but for some reason, the excel doesn't select all the blank cells.
It only selects a few blank cells, which is really strange.
My requirement is to remove the spaces in the text in each cell in a worksheet. i have used TRIM function for this. Sheet1 will have the imported data and sheet 2 has the formula to TRIM. i have an issue when using TRIM on date format. I have used formula TRIM(sheet1!Ax) for columns 1,2 and 3 and TRIM(TEXT(pcload!D1,"mm/dd/yyyy")) for column 4. But the blanks cells in sheet 1 have a value of 01/00/1900 in sheet2. I need the blank cells to be displayed as blank in sheet2.
Sheet 1:
82909U
AB
121
[Code]....
I have a pivot table that compares year-on-year sales data for accounts. If an account didn't have sales for the earlier year, the custom calculation errors #DIV/0! which I can show as at the account level. Problem is at the subtotal levels, I also get the error when I should be getting a value for the group. The error for the one account is affecting my subtotal.
Is there a way I can Show Empty Cells as a VALUE instead of TEXT of say... 0.001 so I can eliminate the error messages?
Excel Version : 2010
Attached File name : <Eliminating duplicates.xlsx>
I want to eliminate the duplicates and plot without duplicate in the yellow cells.
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of values from a 10 x 10 grid that does not have values in every cell.
(I would also like to eliminate any blank cells from the final column.)
I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of all the values from a 10 x 10 array that does not have values in every cell. (I would also like to eliminate any blank cells from the final column.) I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
how to properly execute that function. One earlier comment was that I was not specifying criteria. I'm not sure from the description and help section of "morefunc" as to what those criteria specs are, other than specifying the whole array. I am attaching a test file with the 10x10 grid & some missing values in A1:J10. Cell L1 has the ARRAY.JOIN function with the entire grid specified, but the output is only the first cell of the grid.
I have a macro I run on thousands of rows of data which occasionally has incorrect line breaks that need to be brought up to the previous row. The macro works perfectly, however I'm trying to speed it up by skipping rows that do not need processing.
The macro inserts a check in column AQ to see if column A meets certain criteria. If it does not, "False" is entered in AQ, indicating the need for processing. As it runs now, the loop steps through each row of AQ to see if it needs further action.
I am trying to come up with a way for it to automatically just jump to "False" instances, skipping sometimes thousands of rows of blank cells that need no processing.
[Code] ......
I'm using a VLOOKUP function to return data from a database query on another page. If the data it is looking for is not available, it displays "#N/A". As data will not always be available, this is an intentional use of the function.
I was just wondering if there is a way to return just a blank cell insted of "#N/A" (Like turning the error message for a broken function off). It just looks like a mess with all of the NAs.
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
I am having a problem with some of the cells in a spreadsheet not calculating.
For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).
I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.
Is there a way that I can speed this process up, using some code when I run certain sections?
There is a copy of what I am doing, so you can see the amount of formula.
I need to find the number of cells containing a specific word. Here's an example:
Site # Visit
1001 12-Aug-2008
1001 LATE
1002 08-Jul-2008
1003 09-Aug-2008
1004 LATE
1005 12-Aug-2008
1005 LATE
I need a formula that will calculate how many "LATE" cells there are.
I work with Excel the more I realize that I'm not that good at it Anyways, I'm in need of a quite advanced formula (for me) that sums up relative relative rows of a specified column. The rows relation depends on the ID-number of the person. Look at my attachment.
In Sheet1 I have an extract of the data I'm working with. It's a medical record of different people in a study. The ID (col. B) seperate the different people. One person can have several perscriptions (spelling?) of medicine. Every perscription has an "fddd", a daily dose (col. L). I want to add up all the daily doses for every seperate ID of my data in Sheet2. So for example, for ID1 I want it to sum L3:L6 in Sheet1 and return it to Sheet2!B2. If the ID is not found I want it to be blank.
See the attatched spreadsheet. I can not seem to figure out a formula that will calculate the totals for each type of data.
My goal is to have a sum for each location for # of audits, Critical Counts, and Major Counts.
At the moment I have a simple formula that calculates the due date of a task
The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date
I now need to add another log in date but use the same due date cell
So now ....
Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3
I have a question maybe this isn't even possible or it is i'm not that high on excel calculations, but I would like to know this.
I have this document:
[url]
I would like to calculate that if i've rated a 5 (cell C) I would like excel to calculate the time (cell B), the thing is that I want to calculate how much time I've wasted watching movies in every rating 1-10.
If I enter anything in a cell on one work book I get calculating cell for about 3-5 seconds.
I don't have any formulas looking at the book. and I have deleted, renames, copy and pasted, exported to text and reimported and still it does it.
The thing that is odd though is that if I press return again it clears the calulation cells message.
I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...
Example
A B
3 6 should equal: 1:2 works OK
2 0 should equal: 2:0 my current result: 1:0
3 0 should equal: 3:0 my current result: 1:0
0 1 should equal: 0:1 works OK
0 0 should equal: Nil works OK
4 1 should equal: 4:1 works OK
Anything with a zero in the first column comes up as 1:0
Formula "(Cell-Cell)/ABS(Cell)"
When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me
I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.
Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number
PercDiffExample.xlsx
I'm trying to calculate total occurrences of a given month. I have included the spreadsheet for reference with totals.
I only want to calculate when the Policy column has a "N". If it has a "Y" I do not need to do anything
If the Policy has a "N" I want the formula to look at the V and P columns and give me a total in the Total Occurrence column. I want it to look at the entire month for a grand total.
If the V or P column has a 4 or less it should equal 0.5, if it has a value is between 4 and 10 it would equal 1. So in the Total Occurrence column should calculate the all of the 0.5 and 1's based on each set of columns and give a total. In the example that I have given in the spreadsheet the total in the Total Occurrence column would be 2 because Jan. 1 has a N and a 8 which equals 1 and Jan. 3 has a N and a 4 which equals 0.5 and Jan. 5 has a N and a 4 which equals 0.5 for a total of 2 occurrences.
I am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".
View 4 Replies View RelatedI have the following on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.
A1= User entered value : a fixed submission date that a piece of work is due to be handed in on.
A2 = User entered value : The date entered if work is handed in on time or early
A3 = User entered value : The date the work was handed in if late
A4 = User entered value : The date the piece of work is due to be handed in if an extension has been granted
A5 = Calculated value : How many days late a piece of work is
Essentially I want the calculation in A5 to reflect how many days late a piece of work is. SUM(A3-A1) would do this of course. If the work is early or on time, A2 is ignored.
Sometimes however students have permission to hand work in late, in which case another calculation is needed to work out how many (if any) days over their extension date (A4) their work is. How do I combine these two calculations into the same cell, so that if A3 is empty it checks A4 to see if the student has an extension date and then returns a 'days late' value if applicable?
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.
This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"
Dim Cmnt
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Row > 3 Then
If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6
End If.....................................
I have what is probably a simple problem for most, but can't figure out what to do.
In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
Hello, I am trying to update a person's finances for this past year. She sent me a workbook that has two sheets with macros. The first sheet is linked to the 2nd. When a name and number is typed into the first sheet it automatically shows up on the 2nd.
My problem is, no matter what number I change on the first sheet under amount, the Total cell on the 2nd sheet gives me the #Value error. I have not changed anything, have not deleted any formulas but no matter what I do, that value error always shows up. I know very little about excel but I thought I knew enough to be able to update this book for her. Is there a special way I should be inputting the numbers on the first sheet so that the value error will show the running total instead of an error?
One of the multitude of areas where I still need drastic improvement is with efficiency. Specifically, avoiding loops.
Are any of you aware of any good, basic level sites, blogs, posts, etc., about better methods or processes? I'm not particularly experienced with programming (I've been a software TESTER for 13 years and just started some programming in recent months), so the more basic the better.