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.
I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet
Base Terms
Phrase
Result
car
red
van
blue
bus
red
blue
There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue
How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?
I have a load of postcodes over 8 different tabs, the problem is the format of the postcode is wrong. I basically need to delete the first gap of each cell to make the postcode valid -
DL 7 9
DL 8 1
DL 8 2
DL 8 4
DL 8 5
DL 9 3
DL 9 4
DL 10 4
DL 10 5
DL 10 6
DL 10 7
DL 11 7
You see I need to have it like DL7 9, or DL10 7, but i'm not sure how, I've attached the file so you can have a look.
The solution can be either in VBA or conditional formatting, if possible.I have product names on column A and weeks as from column B where I have the quantity sold. So, every week I'll have an additional column.
A B C D E ...
Product Week1 Week2 Week3 Week4...
What I need:
If the cell is filled, highlight it in green.
If the gap (empty cells) between weeks is =1, highlight it in yellow
If the gap (empty cells) between weeks is >1 but <2, highlight it in orange
If the gap (empty cells) between weeks is >2, highlight it in red
The attached example better illustrates the needs : Example.xlsx
How can I graph merged cells, without graphing gaps or spaces of the skipped cells?
View 12 Replies View RelatedI have a workbook that is linked to 2 text files and updates just fine for me but when someone else opens it it does not calculate the new information just keeps the old. New text files are ran from our database to excel every night. When the workbook is opened the formulas should recalculate based on the updated info in the text files. Problem is when I open the file it performs perfectly and updates as it is supposed to but when my colleague opens the file it is not updating and does not show the new updates. I have checked to make sure the calculation is set to automatic. I have also confirmed that we are both working with 2003 version. I even had her open the text files and close and reopen the workbook and no go. I hat her do ctrl+alt+f9, even ctrl+alt+shift+f9. Had her click on the function, hit enter and nothing. Even tried find and replace = with = to wake up excel and nada.
View 9 Replies View RelatedI am having a little trouble with IF. My pest control sales people are paid on straight commission. In addition to paying them a sliding percentage of the total contract value I pay them 50% of everything above $100 they charge on the initial service.
calculating these formulas?
Attached is the payroll document.
I am working in an excel file with many sheets. I have vlookups on several pages that refer to a different page in my file. However, I am finding that the vlookups are not autocalculating. They are fixed on a value until I click into the cell and hit 'enter'
View 9 Replies View RelatedWhen i copy the formulas below in an excell sheet i have made the references absolute, but i want the sheet name to change as i move through each sheet in the workbook (if it is greater than sheet3)
The column of formulas needs to insert a new column and add formulas to it in the next location. Ie (B4:B16 to C4:C16). B4 Being the heading/sheet name.
Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
I am trying to get the formula for calculating yearly rental imcome. The range is 10 years and the interest is 34%. The first year payment is 42,000 and the 10th year payment is 56,000. I can't figure out how to do the other years. The principal is 325,000 and the sale value is 425,000.
View 4 Replies View RelatedI need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this
'Find row number for total row (2 rows down from last row of data)
endrow = Range("a35").End(xlDown).Row
sumrow = endrow + 2
'Set sum formula for the "e" column
Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")"
'copy total formulas to columns f thru r...............................
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI'm having a problem with a list that I've created. The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.
View 2 Replies View RelatedI have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...
View 10 Replies View RelatedI have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9
A2:
A3:
A4:
A5: -0.09
A6:
A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$
Rank
Should be
2,128.34
1
1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
Code:
=SUM(1/COUNTIF(A$2:A$35000;A$2:A$35000)*(A$2:A$35000>A2))+1
I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly.
I have a file that contains addresses in column C. I need to find any gaps in addresses.
Ex:
211 Corbin Dr
213 Corbin Drive
214 Corbin Drive
123 Apple Drive
124 Apple Dr
124 Apple Dr
127 Apple Drive
I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.
Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.
I have a sheet of over 40000 rows, I attach a sample. Column a is called dam and column b is called damsire. Each dam has only one 1 damsire. Both column a and column b are sorted ascending. Unfortunately there are big gaps in column b. some of these can be filled in as we have the information e.g. b38 and b39 should be Manila..as the row 37 tells you the correct damsire. Similiarly b49 could be filled in as shernazar. I want to create a new column which contains a formula to fill in these blanks. of course some of the blanks cant be filled in as the information is not there e.g. b23 to b28.
View 3 Replies View Relatedhow I can format this timeline better (it was a to,e;ome template created by someone smart on this forum) - so that there isn't a huge gap between 1892 and then 1977... and then so the rest of the data isn't scrunched together.
View 4 Replies View RelatedI'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work
Is there a formula to count gaps? If you see the sheet below, I want to count maximum gaps in range A1:J12 and put that count in column L.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL12=ABCDEFGHIJKL1X XX XXX 22X X X 33 X X 64X X 85X X X X 26 X X 47 X X 48 X X 59 X X 610 1011 X X X X X 112X 9Sheet1 [HtmlMaker 2.42]
To see the formula in the cells just click on the cells hyperlink or click the Name box. DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get two shapes to butt up to each other. Unfortunately the shapes either leave a small gap or a slight overlay. I have tried using Ctrl + arrow key to move in small increments, but that didn't work. I have also tried adjusting the width of the rows, but the rows jump backwards or forwards to a number instead of staying with the number I entered. I want to create a seamless shape out of many different shapes.
View 6 Replies View RelatedI have attached a work sheet where I have part of a formula working.
Although what I am trying to achieve is in the example in column B.
It is possible to fill in the gaps as in column B with the task between the time frames.?
I get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.
I have been browsing here off and on, and have found many excellent answers. I use Excel to process data on time series, as an adjunct to consultancy work on statistical analysis of industrial data . Usually the data has irregular gaps, e.g., daily data might have 2-10 day gaps. If I want to take, say, 7-day averages, SKIPPING OVER gaps longer than 2 days(say), is there an easy way to do this (I don't really know VBA,and it is not worth my time to try and write long code for this, which will eventually be done by some professional programmers)!
View 9 Replies View RelatedInstead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?
View 6 Replies View RelatedCode:.....
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.
remove gaps for missing values in my column chart. I have tried to adjust series overlap and gap width, but the missing values are still showing as gaps. I have attached the sheet
View 1 Replies View Related