Q I would like to calculate the sum of a range but eliminate the highest 5 cells in the range.
A Assuming the range is A1:A100, use this formula:
=SUMIF(A1:A100,"<"&LARGE(A1:A100,5),A1:A100)
Q I have a list with 50,000 names in column A.
I need to select every 15th name and place an "x" in column B indicating the select.
A Place this in column B and copy down:
=IF(INT(ROW()/15)=ROW()/15,"x","")
Q Can one add bullets to text in a cell?
A Yes, when you are entering data, ALT+0149 will enter a bullet symbol.
Q I need to add a column of figures that contain negative numbers. I need the result to add every figure as though it were a positive, e.g. 10, 12, -5, 8. The result must be 35, NOT 25.
A =SUMPRODUCT((A1:A10),SIGN(A1:A10))
Q How do I get the name of the worksheet using a formula?
A =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
Q I'm using Excel to manipulate data I receive from a test unit based on time and temperatures. I take readings every 5 minutes on four identical independent components on the test unit. When one of the component fails, I want to be able to SUM and COUNT the entries up to that point so I can get averages.
My ideal thought is to use the formula "=SUM(AB1:AB{C18})" where cell C18 contains the total number of readings I took when the component failed. I already have the answer to cell "C18" set up to be the total number of readings taken up to failure for one of the components. But I can't figure out how to automatically enter that value into my formula.
For example, if component #1 fails at 1381 readings, I want formula#1 for that component to be--- "SUM (AB1:AB1381). I want the formula I use to automatically plug the results of another cell (C18="1381") into itself as the second number. If component #2 fails at 906 readings, formula#2 for that component will be "SUM(AC1:AC906)", etc.
A Long question, short answer =SUM(OFFSET(AB1,,,C18))
Q I am trying to format a report with dates from an AS400 system. The problem is, the format is yymmdd - no breaks and in the wrong order. I've been trying to convert it to mm/dd/yy without much success. I need it to be recognized by Excel as a date in order to do calculations with.
A Assuming your date is in cell A1, use this formula:
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Thanks to Laurence Devlin who suggested an even easier solution:
Select the column or the range of dates in the column. Select Data/Text to columns. In the Column data format , select Date and choose the option YMD.
This will format the cells as dates and save using an extra column.
Thanks for the improvement Laurence!
Q I have a table where I want to find a value in the header (first row), look for a value under that column, and then return the value in the first column of the table.
A This one's really, really hard to explain without a picture.(did I mention it was hard to explain without a picture).
Q I've got a number of minutes, say 2500 minutes. I want to be able to display the hours and minutes associated, that is convert 2500 to 41:40.
A This formula will convert whole numbers to hours and minutes:
=A1/1440
NOTE - don't forget to custom format the cell containing the formula to [h]:mm
Q I need to convert numbers from + to - How do I do this.
A In an empty cell put -1. Copy this, select the numbers you want to convert, select Edit|Paste|Special and click on Multiply.
Q What formula can I use to
the number of occurrences of a character (or characters) in a cell.
A =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1), where the character(s) you are looking to count is in cell B1 and the cell is A1.
This answer is not my baby (much as I'd like to have been the one who developed this). Full credit for this answer goes to Aladin Akyurek and Bob Umlas and my humblest apologies goes to any other individual(s) I might have missed giving credit to.
Q What formula can I use to calculate a series of dates in a column such that each date is one month different, but the same date in the month. For example, January 10, 2001, February 10, 2001, etc.?
A Assuming your start date is in cell A1,
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Q I have a column of dates and I would like to have the corresponding day of the week next to it. It does not matter if it is in the same column or in it's own column. How can I do this?
A Assuming your data is in column A, put this formula in cell B1:
=A1
and then custom format that cell to "dddd". Custom format by selecting Format|Cells from the main menu. Select the "Number" tab in the message box. Select "Custom" from "Category" and type "dddd" (without the quotation marks) in the "Type" box.
Q I have a line graph and I want to add a trend line to it. The problem is that some of my data points are zeroes so my trend line is skewed by these points. I have tried using IF statements to make these points blank or spaces but nothing works. Any Ideas?
A In your IF statement use the NA function instead of returning a blank or space. For example,
=IF(A1<>0,A1,NA())
Q Using ctrl+F ("find"), it's impossible to find cells that contain a plain asterisk. Any Ideas on how to do this?
A Use the tilde(~) before the wildcard character to search for. For example, if you want to find "*" using CTRL+F, put ~* in the "Find What" box.
Q If I try to enter this message in one cell ="today : " &TODAY(), the result is: today : 37113.
How can I get the right result: Today : 08/10/01?
A Use ="today : " & Text(TODAY(),"mm/dd/yy")
Q Instead of hard coding the prior year end date, I would like to create a formula that is derived from a month-end date during the year.
column A: 6/30/01
column B: 12/31/00 (need a formula).
and next year when column A is 2/28/02, then column B s/b 12/31/01. I can't figure it out.
A Try this in column B
=DATE(YEAR(A1)-1,12,31)
Q I accidentally filled out an alternate startup location. When I open Excel, automatically all documents of the folder are opening. Since this is a folder on a network drive that contains 15 GB it takes about a century to open Excel. I can't stop this so I have to wait x hours in order to start using Excel. How can i change this?
A You can change your alternate startup location through Tools|Options, from the main menu. Select the general tab and you'll find the alternate startup location at the bottom.
Q I have a spreadsheet that has data arranged with columns and rows one way to make the entry and editing of information easier, and am trying to make a linked sheet with that information transposed to make it easier to gleen usful information. I have tried copy and pasting special links into a second sheet then copying and pasting special formulas transpose into a third, but in the third the links are not transposed, they are just like the first sheet. What am I doing wrong? I have no use for the second sheet except as an intermediate step to produce the third since Excel dosn't seem to want to let me copy, paste special, transpose and paste link at the same time.
A you can transpose your numbers via a formula. Here's how:
Let's assume you have data in 20 columns in A1:T1 that you want to transpose to 20 rows starting at A4. Highlight the cells from A4 to A23 (20 data points) and input the following formula without hitting the enter key:
=TRANSPOSE(A1:T1)
After you have input the formula, press CTRL+SHIFT+ENTER to enter it as array formula.
Q Each time I open a worksheet it asks if I want to update the file. I always say yes because I do have active links that I want to update from. However, the sheet also tries to update from another source that I don't have any references to. I've used the Find function to try and locate the file but the results are negative. I cannot locate any reference to the file (workbook1.xls) and I'm not sure what to try next.
A Have a look at the names in the spreadsheet (via Insert|Name|Define), I'll bet your mystery links are there.
Q I am trying to figure out how to convert a number where the "thousandths" amount is indicated by: a 2 for 0.0025, a 4 for 0.005 and a 6 to represent 0.0075. Also, I need to move the decimal place to the left.
For example, 2036 is converted into 2.0375.
A Assuming your data is in A1, use the following formula:
=INT(A1/1000)+VALUE(MID(A1,2,2))/100+VALUE(RIGHT(A1,1))*12.5/10000
Q I have a list of data which starts from the 1st of every month to the end of every month. The date will be the row, and Sales is the column. I need to add the data up by weeks...in other words add up 7 data points and put it in another cell as week 1(i.e from 1 Jan to 7 Jan as 1 week)...then repeating the task for 8th Jan to 15th Jan as week 2 and so on. The program must also know when to terminate the summation proccess like 30 days for Nov and 31 days for other months like Dec and Jan.
A Assuming your data has dates in column A, and that Excel recognizes them as dates, create two new columns: one for the month (say in column C) and another for the week number (say in column D).
In cell C1 enter the following formula, =MONTH(A1), and copy that formula all the way down to the bottom of your spreadsheet.
In cell D1 enter the following formula, =WEEKDAY(A1,2). This will give you the value of the weekday where Monday equals 1. Then in cell D2 enter the following formula, =IF(WEEKDAY(A2,2)=1,D1+1,D1), then copy that formula all the way down to the bottom of your spreadsheet.
You now have two columns, one for the month number and one for the week number. You can then insert subtotals based on those values.
Q What I am looking for is the ability to select every 5th or 10th(for e.g.) record out of say 5000 records that I have.
A Use the Indirect Function. Using the following assumptions:
Input the following formula in cell A2; =INDIRECT("'Sheet2'!"&"R"&(ROW()-1)*$A$1&"C"&COLUMN(),0)
Q I would like to converting decimals to foot/inch. For example, 29.56' to 29'-6 3/4"
A Use this formula (assumes that you have the number in cell A1 and that it is a number, not text).
=INT(A1)&"' - "&TEXT((A1-INT(A1))*12,"#_??/??")&""""
Copyright ©
2001 by Barrie R. Davidson
Last updated March, 2003