I am looking over spreadsheets in a new job, and I am coming across formulas I am unfamiliar with?
For example, =(G16*G17/C13-G24)*hrs and =I25*elec.
hrs and elec??
After using the "Trace Precedents" feature, the "hrs" and "elec" are still a mystery to me. Are these variables that the creator of the spreadsheet has defined? They don't appear to be named data sets because they exist in just a few cells.
I have an excel file with 2 sheets. Sheet 1 has a column that contains formulas (ie (18299*11151)/20067 ) Those numbers are IDs referencing questions stored in Sheet 2. What I would like to do is find a way to look up those questions and place them into the formula instead of the ID numbers.
I need to know if it is possible to combine these 2 formulas (1. (RIGHT(C23,0)&RIGHT(C23,4)) and 2. LEFT(E23,2)&":"&RIGHT(E23,2) into a single cell. I have a date time group that is in text format. I need to take the last 4 digits (the time) and pull it out into another. Then take that cell and convert it into an actual time ie 2:30.
example: 01may2014 0831 first formula pulls the 0831 into another cell. then the 2nd formula converts it into a time 8:31.
After this occurs, I can then subtract times from different categories in my spreadsheet.
My, problem, is that I cannot combine the two formulas. They work great by themselves. I am just trying to make formulas more compact and hopefully more efficient.
I Need to Understand Running Total Sum Formulas. I'm using Excel 2010. and I'm typing in the basic correct formula to arrive at a running total for each ajacent cell to the left of it.
I put in the correct formula in Cell (B1) of Sum=(A1)
I then put in the correct formula in Cell (B2) Sum=(A1:A2) I then highlight the A1 part of the formula with an F4 Key to lock it in.
I then drag the B2 Cell all the way down the excel page to capture all of my running total coming from the (A) Column just to the left of my formula.
Why even though I'm getting the answers I want in the running total does it put an error message in each of the correct answers in each of the cells in the (B) column where I put my formulas.
I tryed to delete the error message spot but I don't know how to delete all of the error spots without going into each cell one by one.
How can I get rid of all the error message out of each cell without having to click on each cell one by one to do it when I might have over 500 or more cells to click on.
I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.
The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.
I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.
I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.
There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.
I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.
is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.
Still adjusting to 2010 Excel...How do you use the automatic fill in feature of formulas; e.g. when typing =convert....a fx function appears....not sure how to use it
I am trying to find the frequency of lottery numbers that occur within 10-day periods (see row 7). Instead of having to manually write each formula for each column, is there a way I can automate it, so that each column will "advance" 10 days?
Excel 2010 A B C D E F G H I J K
1 ct 0 1 2 3 4 5 6 7 8 9
[Code] .........
Array FormulasCell Formula
B8:B17 {=FREQUENCY(B3:HNR3,ball)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself
Conditional Formatting 1 cell with 3 different formulas
I am monitoring tank levels using a program called "PI". I need to know if the tank is rising, lowering or staying the same.
I am using conditional formatting to turn red if high, blueif low and yellow if stays the same. Column B, F and J are tank volumns, Column C is in feet and D is in inches.
A B C D E F G H I J K L 1 TIME LEVEL FT IN TIME LEVEL FT IN TIME LEVEL FT IN 2 7:00 3628 18 11 9:00 3456 18 0 11:00 3321 17 3
AB AC AD 1305 5600 3600 LOW HIGH CURRENT
I started using formals =$B$2=$AD$2 COLOR YELLOW =$B$2>$AC$2 COLOR RED =$B$2
I am trying to create an inventory list that automatically updates the supply quantity when items are taken away from inventory. I have created entry cells where the quantity of items taken out of inventory can be entered (example, cell F2 of the attachment), and the new overall inventory count is adjusted accordingly for each stock of items (example, cell B3). These automated adjustments are repeated for each subsequent row for every time items have been taken from inventory and recorded.
I managed to get it to do what I originally intended with exception of the fact that it still present values on rows where there were no activity - or quantity of items taken from out of inventory (row #6 and beyond of the attachment). Is there a way of "hiding" these values without removing the formula, or possibly set conditional formatting where no values are displayed if no changes in inventory has been entered?
Is there a way in Excel 2010 that the administrator of a excel document can not only hide some columns but lock the columns by some kind of password so it remains hidden? I know user can hide/unhide columns but can some columns be hiddens and locked with passwords?
Secondly can formulas like IF and vlookups statements be hidden and locked with some kind of password?
In maybe via VBA or by some other means, is this possible?
The end user should only be able to type and select certain cells only.
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example: My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
I've got two columns with data. The first is text, and the second is numbers. So I want to be able to enter a certain criteria that would be contained in the text, and have excel return a list of the rows of text that contain that criteria and that have the highest 3 values in the corresponding column. I can use multiple cells to do this if need be (i.e. one cell for the text with the highest value, one for the second highest value, and a third for the next highest value).
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A B C
1 john brown Brown True
2 Cathy Smith
3 Brown excavating company
4 XYZ Corp
5 Brown Advisors Inc.
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula: =IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula: ={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)} But it would only return the first occurrence of the match which in this case would be row 1, or "1"
I need to put a number as a text. It is 14 digits long (35071245130000). If I change it from a number to a text, it re-formats to a scientific number (3.50712E+6).
I can not use the ' in front of the number because it makes all of my vlookups mess up.
The reason I need this changed is because I have a several databases/excel spreadsheets that are being linked through Access. The number is stored as text in the other databases. Because of this, the spreadsheet with the number is not recognizing the number version and the text version as the same.
How to make excel show this full number when formatted as text?
I have Excel 2010. I do not remember having this problem with other versions at my previous employer.
When I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.
When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).
I received one excel spreadsheet office 2010 every week from one on the vendor. it has over 55000 rows. I got columns N, O, P & Q. these columns has some value which I use for my pivot tables. However the values in this columns are stores as a text. I select the little small box (information box ) appears on the right side of the cell and right click on that where I get an option to change it to number. This is working well.
However I have over 55000 rows , it takes a lot of times to change all these cells in four columns ( almost over 220,000 cells). some times more than an hours.
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
i have the text as string data in column A and it is just a text not date format. i want to convert this using formula text function to get result shown in column B and C. is this doable?
Excel 2010ABC1DateRevised DateRevised Date 2Fri 4 Feb 2011Friday, February 04, 20112/4/20113Fri 5 Oct 20074Fri 28 Apr 20065Fri 30 Sep 20056Fri 23 May 20087Fri 3 Feb 20068Fri 30 Sep 20119Fri 11 May 200710Tue 1 Jan 201311Fri 13 Aug 201012Tue 25 Jun 201313Fri 8 Jun 201214Fri 25 Mar 201115Fri 12 Feb 201016Fri 5 Sep 200817Fri 6 Nov 200918Fri 6 Mar 200919Fri 21 Aug 200920Fri 6 Jan 201221Fri 5 Jan 200722Fri 15 Jan 201023Fri 12 Jun 200924Fri 7 Jul 200625Fri 21 Oct 201126Fri 31 Oct 2008Sheet4
In a field containing this: Bergamot, Orange*(11,16) I want to put a space before the asterisk. There are hundreds of these in the spreadsheet with other text before and after the text to be replaced. I made sure the field is defined as a text field.
I searched on "*(" and said replaced with " *(" and ended up with " *(11,16)" - the Bergamot, Orange was removed from the field.
I have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.
I am using excel 2010. When I click on cells on one column, something like text box will pop up with information related to that column. How can I get rid of this text box. How to disable this text box which I think it has a link between the text box and cells on that column.
I'm using Excel 2010 and have run into the following problem. I have data in a text editor that I need to separate. I copy and paste this data into Cell A1, then do a "Text to Col" function that separates the data correctly. Now I have other data to paste into other cells on the same sheet. Here's the problem....The "Text to Col" function runs automatically when I paste other data into any other cell on the WS. How do I turn this off without closing the WS?
I am trying to format a cell (in Excel 2010) as a phone number. The problemis that the data comes over from our download as text. Is there an easier way then using the 'SUBSTITUTE' function to change format to a true phone number (so that we can mail merge into WORD)? Since it is extracted out as a text, using the 'phone number' format (under special) will not work. Using the 'SUBSTITUTE' formula to remove the parenthesis', the dash and the space work but is cumbersome.
Below is an example of the text format that is being extracted: (706) 378-7585
I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
[Code] ....
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
I am using Excel 2010 and I want to import data from a text file, but the problem is that the data is more than 1048576 rows of excel. Is there any solution though codes if one sheet fills up and import remaining data to other sheets ?
