| Subject | Date Asked |
|
| SUMIF with multiple IF statements | 10/29/2009 |
Q: I am using Excel 2007. I am trying to create a SUMIF with multiple if statement variables based on ...
A: Gareth: What you need is the SUMPRODUCT function. The following formula is not exactly what you ...
|
| Trend lines in Excel 2007 | 10/28/2009 |
Q: I would like to make a graph with only a trend line. So I have the data and I know how to make a ...
A: Charles: Good question! 1) Select the line with the actual data points. 2) Right-click on the line ...
|
| rand? generate 1-4 with a twist... | 10/22/2009 |
Q: I am so hoping for some help here, I'm a beginner who has been trying to self teach excel (because ...
A: Isabella: I have uploaded a sample spreadsheet here: ...
|
| excel formula | 9/30/2009 |
Q: I have a list of numeric values in columnA and columnB.I need to compare the values of columnA to ...
A: solayman.inty: I will presume the above values are in cells A1:B5. Place this formula in cell C1: ...
|
| Help | 9/29/2009 |
Q: I have a database which has the categories as follows: A B C D ...
A: Steve: The formula you have chosen is one of the best choices to use. You've done the hard work ...
|
| Finding and referencing a range based on date | 9/28/2009 |
Q: I am working on a spreadsheet to help my employer track data entry accuracy. There are several ...
A: Cari: Hi Cari! The short answer is you need to use SUMPRODUCT. This is a very powerful function ...
|
| Formula | 9/26/2009 |
Q: Would you have an idea for a formula for the following. I want to be able to take a figure on excel ...
A: Chris: Suppose your first price is in cell F1 and it shows 100.00. Place this formula in cell A1 ...
|
| Cross referencing occurances. | 9/24/2009 |
Q: I have a large spreadsheet and need to count the occurrences in two different columns. For example: ...
A: Dominika: This can be done with the SUMPRODUCT formula. Based on your example I suggest the ...
|
| date plus time plus hours worked equals | 9/22/2009 |
Q: I was hoping you maybe able to help. I am trying to locate formula thet will give me the end date ...
A: Brett: I'm not 100% certain, but it sounds like you are adding dates/times, but want to ignore ...
|
| Profit And Loss Graph | 9/21/2009 |
Q: I have a simple Profit and Loss Graph that constantly swings from profit to loss. I would like to ...
A: Dom: This is not something that can be done with Excel using the standard chart options. I have ...
|
| dragging indirect formulas down the page | 8/26/2009 |
Q: This answer of Toms ...
A: Andrew: I must admit I'm confused by why you would want to design your formula the way you did (I ...
|
| Excel overtime formulas | 8/24/2009 |
Q: I'm creating a excel timesheet and I'm trying to figure out how to get it to work out double time as ...
A: Ricci: I apologize if there was a delay in answering your question. I was out of town for a week ...
|
| Excel- days overdue if not completed | 8/23/2009 |
Q: I'm working on the following formula to display if a task is overdue. If the task is overdue I want ...
A: I apologize if there was a delay in answering your question. I was out of town for a week and I ...
|
| Excel Formulas reading left over data from a filter. | 6/30/2009 |
Q: I have a rather large amount of data that I need to be able to filter and examine in various ways. ...
A: Jason: Early versions of Excel do not have AVERAGEIF, so I am going to assume you are using Excel ...
|
| Finding average | 6/27/2009 |
Q: hope you are fine. I've a scenario like this: ID STARTTIME TAKENTIME IN SECONDS 1008 14:45:29 105 ...
A: Ehsan: I hope I understand the question - if not, please provide additional information. I am ...
|
| Formatting | 5/29/2009 |
Q: How do I make a whole row change color based on the data of one cell in that row? I need to change ...
A: Kendra: This can be done with conditional formatting. The following are websites which explains ...
|
| sorting data | 5/28/2009 |
Q: i have inputted thousand of numbers in column A randomly, now i want to sort them in ascending order ...
A: Manoj: This does not require a formula or a macro. Excel has a built in Sort menu option. 1) ...
|
| Formula to get text | 5/26/2009 |
Q: wondering if you can help me with the following formula I have data A1:A10000. I need to extract ...
A: Irfan: You didn't specify if the project number/name is located in the same cell as the text ...
|
| Formula required | 5/25/2009 |
Q: I have a column range A1:A60 which contains the value either True of False.. I want to fill a cell ...
A: Ashish: Here is a formula you can use: =IF(COUNTIF(A1:A60,FALSE)=0,TRUE,FALSE) The above formula ...
|
| Strip leading comma / alternative concatenate method | 5/25/2009 |
Q: I have an excel sheet with columns A-J containing data. However, in any given row, the first column ...
A: Hey Chris: This may take a couple of shots to get it right, but based on your description, I'm ...
|
| Separating data in a cell | 5/24/2009 |
Q: I have text in a cell, separated by commas. The text and number of commas in the cell is variable. ...
A: Ben: 1) Make sure all the cells to the right of the cell in question are blank (because we are ...
|
| Sum question | 5/23/2009 |
Q: I would be much appreciated to your help. My question is to make a summary by adding the qty. for ...
A: The below is the best I can provide...I spent several hours researching this for you. SUMPRODUCT ...
|
| Fraction of Time Range | 5/22/2009 |
Q: I was looking for a sheet that could give me my desired results. We have to Input 5 things. 1. ...
A: I'm not sure what exactly you are looking for, but I have uploaded a new spreadsheet here based on ...
|
| Count specific dates in cell range for current month | 5/22/2009 |
Q: I have failed to build a working formula for 2003, I have a spread sheet and in column E3:E99 I have ...
A: Ron: I apologize for the slightly slow response (I usually like to reply within a few hours; ...
|
| Fraction of Time Range | 5/21/2009 |
Q: I was looking for a sheet that could give me my desired results. We have to Input 5 things. 1. ...
A: Parantap Vyas: First I need to point out that Excel doesn't allow the use of HH:MM:SS:MSMS or ...
|
| Array Formula not working | 3/31/2009 |
Q: I have an array formula in which i am attempting to put in a figure in a cell that is a sum of the ...
A: Paul: Well, I cannot say for certain because an #N/A could be caused by a variety of things. First ...
|
| keyword filter | 3/30/2009 |
Q: I've been looking into trying to create a filter that can be applied across the several columns, not ...
A: James: I believe, if I understand correctly, what you need can be done with "Advanced Filter". ...
|
| Conditional sum of filtered range | 3/28/2009 |
Q: I have seen people using the formula given below, when doing conditional sum for a filtered list. ...
A: Ashutosh Khiré: Well, it is a very complication formula. I will try to explain to the best of my ...
|
| the specified formula cannot be entered because it uses more than 64 levels of nesting | 3/27/2009 |
Q: I truly hope you can help me. I am know a little bit about function in excel, but this one got me. ...
A: Marinda: Is there any particular reason you cannot do this? =IF(F4<0,-15,+15) Beyond the above ...
|
| Excell Times | 3/27/2009 |
Q: I have a text file with times "9:04:00a" or "2:06:00p", i need to format or convert this times to ...
A: Andres: Assuming the file actually has the items entered as time in Excel (and not as text), then ...
|
| QTD,YTD numbers | 3/25/2009 |
Q: I would like to add a QTD recap at the bottom of a financial worksheet (attached). The week and ...
A: Claire: Based on your example it appears the totals are at least numbered. For example, 195,488 ...
|
| Web page Filter | 3/24/2009 |
Q: In April of 2007, you wrote to Sam about how to include a spreadsheet, saved as a "web page with ...
A: Marion: Unfortunately, I don't have a good answer for you. This feature has been removed in Excel ...
|
| how to modify #N/A in percentrank output | 3/22/2009 |
Q: I have an array, let's say 2, 5, 7, 9, 16. In another column I compute the percentrank of each ...
A: Paul: Well, you are going to have to use an IF of some type to perform this task; however, it can ...
|
| Calculating OT for the week | 3/1/2009 |
Q: Here is my dilemma, I have a formula in excel adding total hrs & min worked for a week =SUM(C3:C9) ...
A: Josh: Lets assume your hours worked is located in cell A1. This formula will give you the regular ...
|
| Calculating Hours & Minutes Worked | 2/28/2009 |
Q: I have hours & Minutes worked per day...example A1= 14h 54m A2= 13h 37m A3= 13h 42m A4= 11h 25m A5= ...
A: Josh: Instead of having your formulas in various cells as 14h 54m you should enter the time as ...
|
| Counting when an autofilter is applied. | 2/27/2009 |
Q: How to count data in a column when a autofilter has been used? I have filtered by another column ...
A: Peter: Instead you would use a formula such as this: ...
|
| Vlookup from another workbook - having trouble with the data update | 2/26/2009 |
Q: My hope is for this 2nd workbook to remain closed, so users can see the data updated from this 2nd ...
A: Kathy: The Short: VLOOKUP is a function that will work properly on a workbook that is closed. ...
|
| Formating a database. | 2/25/2009 |
Q: I have a database in excel in a set order, I have now changed my autoresponder for sending out my ...
A: Derek: Based on your description there could be two situations: Situation A: You currently have... ...
|
| opening document | 2/24/2009 |
Q: I have been sent a document which is supposed to be an Excel document ... it has the extension of ...
A: Dianne: XLSX is the extension of an Excel 2007 document. It is likely you have Excel 2003 or ...
|
| Excel Formula Request: Display Entries Entered out of Total Enteries | 2/24/2009 |
Q: Basically, I am trying to have a single cell determine the total number of entries entered and ...
A: Jack: Give this formula a shot and let me know if it does what you are looking for: ...
|
| Counting when an autofilter is applied. | 2/23/2009 |
Q: How to count data in a column when a autofilter has been used? I have filtered by another column ...
A: Peter: Assuming the above data is in columns A and B you would use this array formula for Monthly ...
|
| IF function? | 2/15/2009 |
Q: I'd like to rank players according to their score. The lower the score the better the rank. Also ...
A: Paul: Your formula does the following: If cell D10 is blank then the formula returns an empty cell ...
|
| Rank formula | 2/15/2009 |
Q: I'd like to rank players according to their score. The lower the score the better the rank. Also ...
A: Paul: The solution to your problem will require 2 steps. The first step is a "helper column". The ...
|
| How long does it take to UNMERGE | 2/14/2009 |
Q: I have a tons of data that I need to unmerge in the regular basis. How long exactly does it take to ...
A: Lia: I may not understand the question..and if I didn't just ask again in a different way :). I'm ...
|
| Macros Row Colour | 2/13/2009 |
Q: off hire, damaged etc) At the moment i am just highlighting the row and selecting a colour but the ...
A: Belinda: Lets say "H" and "A" are located in D1:D25 1) Select rows 1 through 25 (or whatever rows ...
|
| Macros Row Colour | 2/13/2009 |
Q: off hire, damaged etc) At the moment i am just highlighting the row and selecting a colour but the ...
A: Belinda: I believe your best bet is Conditional Formatting. You can have Excel automatically color ...
|
| ceiling function | 2/12/2009 |
Q: sir, i want to round off a number say 20.5 to 30 but want 20.01 and 20.44 i.e. less than and equal ...
A: Nabam: The following function will round to the nearest 10: =ROUND(A1,-1) Thus, if you had the ...
|
| Drop Down Lists | 2/12/2009 |
Q: I've been trying to research this for a while now and came across this site. I am creating a client ...
A: Stewart: This can be done but there is a little bit of a trick. The two items you will be using ...
|
| ceiling function | 2/11/2009 |
Q: sir, i want to round off a number say 20.5 to 30 but want 20.01 and 20.44 i.e. less than and equal ...
A: Nabam: Assuming your value is in cell A1 then use this formula: ...
|
| Pivot tables in Excel | 2/9/2009 |
Q: how to create pivot tables in Excel? Thank you.
A: Abhi: That is a VERY general question. The quick answer is you highlight the table of information ...
|
| charting project costs over time | 2/9/2009 |
Q: I have limited knowledge on charting dates in excel. I have created a table showing the "date of ...
A: Jon: Such a graph will need to be built in steps. The first step is to summarize your data into ...
|
| Formula to determine Golf net score | 2/8/2009 |
Q: I'm working on an iteractive golf score card on excel. I need help with the formula to determine the ...
A: Paul: Q#1: When I copy/paste this formula, can i lock A3 in place while having A2 & A4 move with ...
|
| Formula to determine Golf net score | 2/8/2009 |
Q: I'm working on an iteractive golf score card on excel. I need help with the formula to determine the ...
A: Paul: The following formula worked with the 4 examples you provided. Please place this formula in ...
|
| SUMIFS | 2/7/2009 |
Q: I want to Sum the values in a column based on 2 other criteria being a name and within a date range. ...
A: Daniel: Lets assume column A has names like this: Nathan, Daniel, Daniel, Daniel Lets assume ...
|
| Pivot Table | 2/6/2009 |
Q: I want to have a pivot table for Accounts Payables. Basis data consists of Vendor Name, Balance ...
A: Pravin: When you set up your pivot table be sure to drag the Expense Group to the "rows" section of ...
|
| autofill | 2/5/2009 |
Q: For certain application I want to autofill a block of cells (10 rows and 15 ...
A: Mangesh: This could be done with a VLOOKUP formula (lookup the ID in another spreadsheet and return ...
|
| sub-tables in excel | 2/4/2009 |
Q: I'm trying to make a spreadsheet where some rows will have many sub-entries. To explain, lets say I ...
A: Danny: How about this sample file? http://home.swbell.net/nate-sus/excel/fordanny.xls Note #1: ...
|
| sub-tables in excel | 2/3/2009 |
Q: I'm trying to make a spreadsheet where some rows will have many sub-entries. To explain, lets say I ...
A: Danny: Please take a look at the following spreadsheet: ...
|
| Reference an entire pivot table | 2/3/2009 |
Q: Is there a way to reference a whole pivot table? I want to have a pivot table on one sheet, and ...
A: Scott: Even if you use GETPIVOTDATA you are still going to run into problems of the formatting not ...
|
| Students and Remaining Balance | 2/2/2009 |
Q: A pleasure to communicate with you. My question is as follows: I have a Language Training School ...
A: Issa: Before I write another formula to provide what you need I feel I should ask for ...
|
| Students and Remaining Balance | 2/1/2009 |
Q: A pleasure to communicate with you. My question is as follows: I have a Language Training School ...
A: Issa: First I apologize if my answer is delayed - I was out of town this weekend for forgot to set ...
|
| Excel bug - cell comments become uneditable | 1/31/2009 |
Q: First of all thanks for offering your time to help with our Excel questions! Here is mine. ...
A: Tudor: First I apologize if my answer is delayed - I was out of town this weekend for forgot to set ...
|
| Reference an entire pivot table | 1/31/2009 |
Q: Is there a way to reference a whole pivot table? I want to have a pivot table on one sheet, and ...
A: Scott: First I apologize if my answer is delayed - I was out of town this weekend for forgot to set ...
|
| excel formula | 1/30/2009 |
Q: I am trying to create a formula that will cumulate the Sum Delta of more than one data entry columns ...
A: Gerry: I will assume the constants are in A2:A3 and the datas are in B2:C3. Assuming you only ever ...
|
| excel calculations | 1/28/2009 |
Q: I am trying to do a rota and I need to separately calculate the number of A's, B's and N's in any ...
A: Katharine: Lets assume your letters are in column A. Please place this formula in cell B1: ...
|
| purchase order system | 1/28/2009 |
Q: I am trying to make a spreadsheet reference a data set that has our vendor information in it. What ...
A: Meredith: This can be done with vlookup and data validation list boxes. Here are the 2 basic steps ...
|
| tracking employee location | 1/1/2009 |
Q: I have a need to track employees by location IE: employee1 is in Tikrit and employee2 is in Kalsu ...
A: Pete: This certainly isn't a simple task to accomplish; however, it can be done. Unfortunately, it ...
|
| Seperate City, State & Zip into seperate columns | 12/31/2008 |
Q: I have a data base that has the city, state and zip in one column. I need to seperate into 3 ...
A: Barbara: This can be done with the Excel "text to column" wizard. Lets assume all of your data is ...
|
| Excel spreadsheet | 12/30/2008 |
Q: I have data in the following format in an excel cell. John Clinton/5269/326569/Citi Bank explantion ...
A: Vishal: Select the cell (or a single column such as column A) that contains the "/"-delimited data. ...
|
| Conditional Formatting | 12/29/2008 |
Q: I wish to apply conditional formatting showing Red / Amber / Green cells to a Cleared Date column ...
A: Mike: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/formike.xls ...
|
| How to count incidence of a text in Excell | 12/28/2008 |
Q: I am working on a scheduling template in which employees can work a variety of shifts indicated by ...
A: The "--" is certainly undocumented and it is really a "trick". It is most frequently used in ...
|
| Summing cells | 12/28/2008 |
Q: I have an excel sheet with a table of contents (names of customers) in Column A and their sales in ...
A: Humam: You can use the SUMIF function to accomplish this task. Lets assume you have the following ...
|
| VLOOKUP with Nested IFs | 12/27/2008 |
Q: Good Morning, Nathan! I have a workbook with multiple sheets. I am using various VLOOKUP formulas ...
A: Charles: I assume, based on the presentation of your sample formulas, K2 is on the same sheet as ...
|
| How to count incidence of a text in Excell | 12/26/2008 |
Q: I am working on a scheduling template in which employees can work a variety of shifts indicated by ...
A: Larry: I believe I understand what you want. You essentially want to use combinations like these: ...
|
| Filter | 12/26/2008 |
Q: I have a database in Excel that lists the dates for the end of life on equipment leases. What I wnat ...
A: Jamie: This can be done with conditional formatting. Please follow these steps with the assumption ...
|
| import data | 12/24/2008 |
Q: I’m using MS 2003 , I want to import data from text file in my particular Cell , Please brief me ...
A: Sohail: In Excel you can select data->import external data->import data->choose the text file. ...
|
| cell formating | 12/22/2008 |
Q: Everthing works fine, but here is the problem. when I separate this number (106050005) in two ...
A: Behzad: There are two ways to do this... #1 - Lets assume 106050005 is located in cell A1. Place ...
|
| SUMPRODUCT or other solution... | 10/29/2008 |
Q: I am trying to conditionally sum up some fields from a database-like spreadsheet. Basically my info ...
A: Catherine: Try entering the formula as an array formula. This means wheny you confirm the function ...
|
| SUMPRODUCT or other solution... | 10/29/2008 |
Q: I am trying to conditionally sum up some fields from a database-like spreadsheet. Basically my info ...
A: Catherine: You have the right idea; however, I suspect you may have had some glitches or mismatched ...
|
| Date Formula | 10/28/2008 |
Q: Nathan I have a list of dates which relate to entries in my database, what I'm looking for is a ...
A: John: Lets assume you have a series of 12 dates in the range A1:A12 1/1/2008 2/1/2008 3/1/2008 ...
|
| Filters | 10/28/2008 |
Q: I know how to make a filter menu at the top of my collumn, but the problem is the options are kind ...
A: Joseph: Without visual basic programming (which I cannot help you with...that is a completely ...
|
| Top 5 Values | 10/27/2008 |
Q: I have a list of defects in the top row and number of occurrences per week in the subsequent rows. I ...
A: Tom: This will require an array function that uses both LARGE and FREQUENCY. The LARGE function ...
|
| Lookup table | 10/27/2008 |
Q: I'm trying to convert student point totals into letter grades. I have a table with three labeled ...
A: Dave: The VLOOKUP function will do what you need, but you will need to reorganize your list ...
|
| sorting data according to condtions | 10/26/2008 |
Q: I hope you can help. I am fairly new to excel. I need to sort data similar to below using the ...
A: Annette: Assumptions: 1) Names are in A2:A6 2) A's are in B2:B6 3) E's are in C2:C6 4) Total is in ...
|
| Finding the Sum | 10/25/2008 |
Q: I have been working on this equation and I am stuck. I have two columns. One has a 2 letter code and ...
A: Brian: Your dollar signs on the formulas have different placement than any version of Excel I have ...
|
| Using Excel IF command for odd or even numbers | 10/25/2008 |
Q: Is it possible, using the IF command, to specify a certain outcome based on whether the number in a ...
A: Justin: You would want to use a formula similar to this: =IF(ISODD(A1),"ODD","EVEN") This formla ...
|
| Subtraction and IF | 10/24/2008 |
Q: I am looking for a formulae that will look at cells, and subtract the higher value from the lower ...
A: Arno: Sure thing...I believe this modification will do what you need: ...
|
| Minutes/Mile pace | 10/24/2008 |
Q: I am starting a new training regiment running. Let's say I run 3.1 miles today. When I reach 1.29 ...
A: Teddy: Lets assume 1.29 is in cell A1. Lets assume 12:08:49 AM is in cell A2. You can enter a time ...
|
| Subtraction and IF | 10/23/2008 |
Q: I am looking for a formulae that will look at cells, and subtract the higher value from the lower ...
A: Arno: Lets say $500 is in cell A1 and $250 is in cell A2. You would use this formula: ...
|
| complex IF function | 10/22/2008 |
Q: I am trying to build an automatic tournament chart for my club. I can not create a function ...
A: Nenad: Lets assume the following data is in cells A1 through B2 (a 4x4 square): Red 25 Blue 35 ...
|
| Excel Querry | 10/21/2008 |
Q: There is one problem that I am facing in excel. I have one column with cells having different value ...
A: Ankur: I presume you want the cells to be filled with color automatically. This can be done with ...
|
| Bonus Calculations | 9/27/2008 |
Q: I have created a Bonus Spreadsheet that will deduct a certain amount if the employee does not ...
A: Peter: I believe I understand the question; however, you didn't specify what to do with an "N"...I ...
|
| Excel | 8/30/2008 |
Q: How to use Sumif with And combination. If Column L has value "aaa" and Column N has Value "bbb" ...
A: Nagabhushanam: If you want a COUNT of the values in P where L=aaa and N=bbb, then you would use ...
|
| Copying formulas absolutely | 8/28/2008 |
Q: Is it possible in Excel to copy a formula from one cell to another exactly (without changing the ...
A: Oliver: There is no paste special in the sense you describe. Here are your options: 1) Select ...
|
| clustered column char | 8/27/2008 |
Q: I need to create a colored column chart with 3 axis. The left hand side of the "Y" axis needs ...
A: Francisco: If you create a standard bar graph it will put everything on the left axis. You will ...
|
| Remove data labels of zeo values from pie charts | 8/27/2008 |
Q: I am completely out of ideas of how to resolve the follwing proble.. Any help yopu can give will be ...
A: Neela: You have some other options... http://home.swbell.net/nate-sus/excel/forneel.xls 1) I can ...
|
| Remove data labels of zeo values from pie charts | 8/26/2008 |
Q: I am completely out of ideas of how to resolve the follwing proble.. Any help yopu can give will be ...
A: Neel: The easiest way to do this is to use a filtered list. Items that "disappear/hide" when ...
|
| Retrieving Date-Specific Information | 8/25/2008 |
Q: I'll get right to it, my format in my spreadsheet is as follows: Sheet2 A1 B1 C1 D1 ...
A: Seth: I have uploaded an example here: http://home.swbell.net/nate-sus/excel/forseth.xls The ...
|
| Currency table needs to end in .X9 | 8/25/2008 |
Q: 52 to 21.59. I have about 2000 cells that need to be converted so I would like to know how to do it ...
A: Stacia: Lets assume the first occurrence is in cell A1. You would place this formula in cell B1: ...
|
| Excel left lookup | 8/21/2008 |
Q: Having trouble doing a left lookup. I have the following Vlookup in cell (E5) ...
A: Andy: You can use today, but you will need to format it to look like 21-08-2008...that and it will ...
|
| Excel left lookup | 8/21/2008 |
Q: Having trouble doing a left lookup. I have the following Vlookup in cell (E5) ...
A: Andy: VLOOKUP only works when finding items on the right. To look toward the left you will need to ...
|
| Excel, multiple worksheets | 7/31/2008 |
Q: Lotus 1-2-3 had a great feature that I cannot find in Excel. In 1-2-3 when worksheets were grouped ...
A: Larry: I guess I never used the version of 1-2-3 that allowed multiple sheets...or maybe I just ...
|
| Excel consolidation | 7/29/2008 |
Q: I've got a consolidation with the sources all being from worksheets in the same workbook, and would ...
A: Brian: I'm not sure if I have enough information to answer this question. You say you have a ...
|
| formulas | 7/28/2008 |
Q: I am trying to do a count on message traffic. Specifically, a count of message formats that are ...
A: Steve: This is going to be a very generic answer. Let's presume originators are in column ...
|
| Formula to count contiguous, multiple numeric values within range of cells | 7/23/2008 |
Q: Requesting assistance with counting contiguous, multiple numeric values within a range of cells. I ...
A: Teresa: I suggest the SUMPRODUCT formula. It allows for multiple criteria like you are needing. ...
|
| Update data from rows to columns | 7/23/2008 |
Q: I have a spreadsheet of data in which the dates go down the rows and the info in the month goes ...
A: Brad: Let's assume your date is in cells A1 through B3. I am assuming your months are in A column ...
|
| Advanced Formula | 7/22/2008 |
Q: I have two column in excel. Column A has part numbers, and B tells me if they were fixed. I need to ...
A: Anna: Sorry it took so long to get back with you the second time...I've been out of the office for ...
|
| excel func | 7/22/2008 |
Q: i have been writing formulas to make a billing statment from a spreadsheet of data, but ave run into ...
A: Andrew: Excel versions prior to 2007 have a nesting limit of 8 (as you have noticed). Excel 2007 ...
|
| Advanced Formula | 7/21/2008 |
Q: I have two column in excel. Column A has part numbers, and B tells me if they were fixed. I need to ...
A: Anna: I'm going to assume you have Excel 2007 since it was not specified. The following is an ...
|
| Linking workbooks | 6/26/2008 |
Q: There are about 15 separate workbook files (one worksheet per file) that I need to "feed" into a ...
A: Carolina: I really don't have enough info to answer this questions. VLOOKUP is a perfectly valid ...
|
| Baloon Payment Formula | 6/26/2008 |
Q: What is the formula that calculates the Ballon Amount if PV=$80,000., PMT=0, i%=8%, Term=3 years. ...
A: Allen: You would want to use this formula: =FV(0.08/12,36,0,80000) This will give you the future ...
|
| Advanced Filtering | 6/24/2008 |
Q: What is the best way to use advanced filtering to filter the same list onto different worksheets ...
A: Doug: Here is a sample spreadsheet: http://home.swbell.net/nate-sus/excel/advfilter.xls Your best ...
|
| Sum If Formulas | 6/24/2008 |
Q: I have data that is always 6 digits in column A. The 5th digit is always a . The 6th digit is ...
A: Monica: Give this formula a shot: =SUMPRODUCT((RIGHT($A$1:$A$500,1)="5")*($F$1:$F$500)) The above ...
|
| change notification | 6/23/2008 |
Q: I have a spreadsheet that many people are making entries into. Is there anyway I can set it up so ...
A: Joanne: I could go into a long description on how to do this; however, it is probably easier for me ...
|
| re: Followup From A Previous Match Question | 6/21/2008 |
Q: Nathan, Glad to see you back. Hope you had a good vacation. Back on 2-8-07 you answered a question ...
A: Hey Robert: I have uploaded a slightly revised spreadsheet here: ...
|
| ROUND UP | 5/29/2008 |
Q: I want to make an Invoice with several items and I want the toal to be rounded up to the nearest ...
A: Lenin: You will want to use the round function. Suppose 100.25 is located in cell A1...place this ...
|
| HUGE URGENT problem with complex data merging | 5/28/2008 |
Q: I got some data from a platform that we use to log customer transactions and the query resulted with ...
A: Tareq: The best way to do this is with a Pivot Table. Let's start with your beginning data: I ...
|
| Two line graph | 5/27/2008 |
Q: I am desperately trying to figure out the graph(chart) wizard in excel, to my dismay it is much more ...
A: Mike: I have uploaded a sample spreadsheet here: ...
|
| Simple Formula | 5/26/2008 |
Q: I have a question which I'm sure has a simple answer. I'm making a spreadsheet to predict fantasy ...
A: David: I suggest the SUMPRODUCT() formula. Assuming your data is in B4:K5, then these are the ...
|
| excel | 4/28/2008 |
Q: This is what i need : I want to know the average or frequency the product is being bought within ...
A: Joey: Believe it or not, this is kind of a subjective question. Do you want to start your average ...
|
| Excel | 4/28/2008 |
Q: If I have a column with numbers and text (1324gty) what do I use to count the number of letters and ...
A: Andy: Can you give this formula a shot? I know it works in Excel 2007, but it may not work in ...
|
| How to show if a value in one row is less than a number in another cell - for columns | 4/27/2008 |
Q: I have excel 2007 - I have a spreadsheet with a column of numnbers that show the average answer for ...
A: John: What you are describing can be done with conditional formatting (Home Ribbon->conditional ...
|
| IF Function problem! | 4/26/2008 |
Q: I was working with complicated Linked Spreadsheets, for calculating Employee Incentive. In one of ...
A: Chetan: Let's assume the salary is located in cell $A$1. Sales are located in cell $B$1. Here ...
|
| Combo Box | 4/24/2008 |
Q: I have Excel 3000 and have used Combo Boxes before. Each time before, if the entries in the Combo ...
A: Robert: Well, obviously, a combo box should display a scroll bar (as you would expect it to). By ...
|
| Excel and Pivot help | 4/23/2008 |
Q: I have this pivot formula =IF(GETPIVOTDATA("Status",$A$3,"Account #",$A$16) <> "", ...
A: Raj: 1) In your basic formula, change all occurrences of $A$16 with simply $A16. By removing the ...
|
| Excel cell shading | 4/23/2008 |
Q: Hey Nathan, A question for you about Excel. Is it possible to shade specific cells on one sheet ...
A: Clarkey: This can be done with Conditional Formatting. In Excel 2007 this is found on the Home ...
|
| HOW TO DRAW A THRESHOLD LINE | 3/31/2008 |
Q: I am busy plotting a risk graph (Impact and probability)on excel and I need to draw a threshold ...
A: Thandi: If I understand you correctly, the threshold line is just a line you personally specify as ...
|
| KPI's | 3/30/2008 |
Q: One of our major customers has requested that we provide them will some KPI’s. However, to do this ...
A: Mark: I'll break this down with a variety of bullet points... 1) Excel doesn't have an import ...
|
| cleaning data | 3/29/2008 |
Q: I am trying to Vlookup part numbers from two separate sheets. On one sheet the part number is 8 ...
A: Dan: Kewl beans - the other way will look like this: ...
|
| cleaning data | 3/28/2008 |
Q: I am trying to Vlookup part numbers from two separate sheets. On one sheet the part number is 8 ...
A: Dan: 1) Assuming you are trying to VLOOKUP the part number with leading zeroes in the list that ...
|
| EXCEL | 3/28/2008 |
Q: I HAVE DATA (NUMBERS) IN TWO COLUMNS A AND B A REPRESENTS CUSTOMER ID WHILE B REPRESENT TRANSACTIONS ...
A: Eric: I have to make one assumption to answer this question, and that is that your data is in ...
|
| UPPER CASE | 3/28/2008 |
Q: I IMPORTED DATA TO AN EXCELL 2007 WORKSHEET. THE LETTERS ARE A MIX OF UPPER AND LOWER CASE, I WANT ...
A: Mary: If you are looking for a solution to change the entire sheet with just one click of a button, ...
|
| TAB key | 3/27/2008 |
Q: I am using EXCEL 2007. I am in cell A1. Each time I click the Tab key it goes to cell N1. How do I ...
A: Moshe: By default, Excel 2007 will move from cell A1 to B1. Without looking at your spreadsheet I ...
|
| Formula | 3/27/2008 |
Q: I am trying to figure out a formula that will do the following: I have a column of data, lets call ...
A: Dennis: You are looking for the SUMIF() function. It will sum a column of values depending on ...
|
| date replication | 3/26/2008 |
Q: I need the instructions/formula to turn a column of dates into a column of double dates. For every ...
A: Debra: There is no way to do this with a specific formula; however, we can do this with some data ...
|
| Finding a character in a vector | 3/26/2008 |
Q: I have a vector in sheet1, say (a1:a22) that can have either a blank or a single letter in each ...
A: Jack: This may not be exactly what you are looking for, but a Pivot Table will take a list of ...
|
| Excel | 3/26/2008 |
Q: I am working with a spread sheet that is kind of involved. What I want it to do is automatically ...
A: Gloria: Suppose you want to display the word "YES" in cell $B$1 if the value in cell $A$1 is >50. ...
|
| vlookup/hlookup | 3/25/2008 |
Q: I have a report from another application that I drop into Excel. It is a report of sales made by ...
A: Mike: Instead of VLOOKUP and HLOOKUP I think you should shift to INDEX and MATCH. It allows you a ...
|
| Find excel sheets that do not perfectly match | 3/25/2008 |
Q: I have 2 excel sheets: On the first sheet I have un column A a list of names On the second ...
A: Michael: There is no way, with standard Excel formulas, to do what you need. There is always going ...
|
| Merging excel documents. | 3/25/2008 |
Q: I have been tearing mt hair out trying to figure out how to merge existing spreadsheets. I am a ...
A: Dianne: The answer to your question is going to be slightly dependent on your starting data. Thus, ...
|
| OVER WRITE | 3/25/2008 |
Q: WANT TO FREEZE PART OF WORKSHEET I AM WORKING ON SO IT CAN NOT BE OVER WRITTEN BY MYSELF OR ANYONE ...
A: Susan: You will need to do two things (I will assume you are using Excel XP or older; Excel 2007 is ...
|
| Excel Formula... | 3/24/2008 |
Q: We have a problem with the way we receive our lockbox summaries from the bank. On any given day we ...
A: Brian: I have enclosed a sample spreadsheet here: ...
|
| Excel conditional formatting | 3/23/2008 |
Q: Could you please help me out with this........I have two columns of figures and I want any cell in ...
A: Paul: 1) Edit the conditional formatting in the one working cell and ensure there are no $'s ...
|
| Excel conditional formatting | 3/23/2008 |
Q: Could you please help me out with this........I have two columns of figures and I want any cell in ...
A: Paul: This can be done with conditional formatting. 1) Select column B. 2) Click on format-> ...
|
| if statements to create aging report | 3/22/2008 |
Q: I am trying to create an aging report for pass due accounts (30 days, 60 days, and 90 days). My ...
A: Gregg: That is probably because I misunderstood what you are trying to do. I think part of the ...
|
| IF statements | 3/21/2008 |
Q: I'm an engineer and am trying to create a study of various dimensions for our parts that may be ...
A: Rick: Here are my starting values: value for C2(max spec) is 0.645 value for D2(min spec) is 0.595 ...
|
| IF statements | 3/21/2008 |
Q: I'm an engineer and am trying to create a study of various dimensions for our parts that may be ...
A: Rick: I believe you formula in cell G2 is working correctly. I believe the formula in cell H2 is ...
|
| Vlookup command help | 3/21/2008 |
Q: I am using the Vlookup command, in order to search for previous stock prices of the 20 previous ...
A: Matt: Can you give this a shot and let me know if it works? I think it will, but somteimes I do ...
|
| Excel Column Headings on each page | 3/21/2008 |
Q: How do you print a large spreadsheet that goes across several pages with the column headings ...
A: Jeff: You can do this with the following: 1) Click on file->page setup 2) Select the sheet tab 3) ...
|
| Calculating pair of numbers... | 3/20/2008 |
Q: I have a series of numbers in col A and a series of numbers in col B. I want to count how many ...
A: Panos: Can you give this formula a shot? ...
|
| date calculation | 3/20/2008 |
Q: i have start date and end date. from this diffence how i know the how calculate year,month and days. ...
A: Ebi: Assumptions... 12/31/1998 is in cell $A$1 01/01/1998 is in cell $A$2 1) Number of days: ...
|
| Folder formula issue | 3/19/2008 |
Q: I have a folder with 3 documents all linked to doc 1 in the folder. I want to copy the folder to ...
A: Frank: You should still be able to use the search and replace even though they are in deeper ...
|
| Folder formula issue | 3/19/2008 |
Q: I have a folder with 3 documents all linked to doc 1 in the folder. I want to copy the folder to ...
A: Frank: I'm not 100% sure I understand your question, but I believe you are running into this ...
|
| Counting frequency | 3/19/2008 |
Q: I use a spreadsheet that records production time and exception time for each operator across 3 ...
A: Bill: Does this work? =SUM(IF(FREQUENCY(A:C,A:C)>0,1)) The above frequence counts unique in three ...
|
| looking up a value and returning the row the value is found in | 3/18/2008 |
Q: I am trying to lookup a particular numberical value in a column on one worksheet, and return the ...
A: Robby: There are a couple of ways to do what you need. The first way is going to be very time ...
|
| Microsoft excel | 3/18/2008 |
Q: Can you please advise how to count if based in two column criteria e.g. a list of employees and ...
A: Jon: Lets assume column A contains the location (Texas, California, Maine). Lets assume column B ...
|
| conditional formatting question | 3/17/2008 |
Q: I wish to create a conditional formatted field based on the value of another field. For example My ...
A: Justin: Excel has a built-in feature called conditional formatting. For example, suppose you want ...
|
| Excel rounding issue | 3/17/2008 |
Q: Good morning. I'm hoping you can help me figure out how to correctly calculate this formula ...
A: Jennifer: Since you cannot have any rounding error, the easiest way to do this is as follows: We ...
|
| problems with a table and formulas | 3/16/2008 |
Q: Could you please help me? I'm having trouble with the following table (this is only a partial ...
A: Filipe: I have uploaded a sample spreadsheet here: ...
|
| Excel Elapsed Time | 2/29/2008 |
Q: I'm diabetic and I want to determine length of time between 2 times when I check my glucose level I ...
A: John: I am going to assume your list of data appears exactly as above, all in one column. I will ...
|
| Excel - Function value stored in a cell | 2/29/2008 |
Q: I am using Vlookup in a workbook having 9 sheets. 7 of the sheet use the Vlookup, 2 sheets contains ...
A: Venkatesh: That can also be done. We will assume A2:B255 is located in cell D1. Instead of this ...
|
| Conditional Formatting | 2/28/2008 |
Q: My question is this, I have two col's A & B I want the colour of cell 'a' to change depending on ...
A: Michael: This can be done via conditional formatting in Excel 2007. 1) Select the cell you want ...
|
| Excel - Function value stored in a cell | 2/28/2008 |
Q: I am using Vlookup in a workbook having 9 sheets. 7 of the sheet use the Vlookup, 2 sheets contains ...
A: Venkatesh: There are several ways to accomplish what you need. I will provide three of the most ...
|
| 'If', 'and' & lookup combination. | 2/27/2008 |
Q: I need a cell to lookup two criteria and providing they are both met then return a single value if ...
A: For that situation you could use a SUMIF() function. Assume REF is in column A, Name in B, and ...
|
| Concatenating a fixed length | 2/26/2008 |
Q: This is the formula that needs help: ...
A: Brit: I believe you were very close with your idea to use spaces, but they were in just the ...
|
| 'If', 'and' & lookup combination. | 2/26/2008 |
Q: I need a cell to lookup two criteria and providing they are both met then return a single value if ...
A: Adam: You have said the 3rd column contains a value. If this is true, then the spreadsheet below ...
|
| Compare Excel columns | 2/25/2008 |
Q: All I want is to compare columns as explained below,in Excel If column (A=B) then for each matching ...
A: Sharmil: I have uploaded a sample file here: ...
|
| can't figure annual sales by hospital # | 2/24/2008 |
Q: Sir I am trying to figure out a way of summarizing total annual sales by hospital knowing that that ...
A: George: You could replace the formula in cell E2 with the following: ...
|
| Sum Only Positive Numbers In A Column If A Y Appears In Another Column | 2/24/2008 |
Q: Jeff Again. You were so prompt and good on my last question, I thought I would ask you another: I ...
A: Jeff: This can be done with the SUMPRODUCT formula, which is kind of a useful formula for doing a ...
|
| can't figure annual sales by hospital # | 2/24/2008 |
Q: Sir I am trying to figure out a way of summarizing total annual sales by hospital knowing that that ...
A: George: I'm going to make the assumption that you want a total, where the ?????? is, of all SKUs ...
|
| formula array help | 2/22/2008 |
Q: Nathan- I'm trying to create a formula array which will calculate the sum/quantity/averages of ...
A: Duane: You are SOOOOO close with your formula. Instead of SUM you will need to use SUMPRODUCT(). ...
|
| Transposing Non-contiguous Cells | 2/22/2008 |
Q: I was wondering if there was any way (via formula or VBA) to transpose the contents of ...
A: Jeff: Follow these steps and this should work for you - if not, please let me know because it means ...
|
| Nested IF AND formulas | 2/21/2008 |
Q: I need to nest more than 7 IF and formulas in order to flag up overdue invoices. I've got this far ...
A: Marcia: As you may have figured out, Excel has a limitation on nestings. Thus, if you need more ...
|
| Countif | 2/21/2008 |
Q: how can i use count if with this range ...
A: This cannot be done with countif or sumproduct. The problem is you are using a non-contiguous range ...
|
| Excel Graphs | 11/29/2007 |
Q: I went up to Everest Base Camp and have a load of data that I have uploaded from my watch. I cant ...
A: Olly: First - I'm jealous - Someday I would love to climb a tall mountain. I'm into hiking (and ...
|
| Image pop up | 11/29/2007 |
Q: When I mouse over a cell with a persons name I want their image to pop up from a separate worksheet ...
A: Okay, you can have an image pop-up, but it cannot really be a permanent link to an external file ...
|
| Trigonometry in Excel | 11/29/2007 |
Q: Can you do this in Excel? RAMC = 2h 9m 44s = 2.162222 hrs = 32.43333 degree RAEP = RAMC + 90 ...
A: Parantap: Gotcha..so the new formula is this: ...
|
| Pivot table problem | 11/29/2007 |
Q: I have a simple pivot table created from a spreadsheet that has a department code, name & dollar ...
A: Dianne: The are selected for your Pivot Table must have a header row. This means the range you ...
|
| Trigonometry in Excel | 11/28/2007 |
Q: Can you do this in Excel? RAMC = 2h 9m 44s = 2.162222 hrs = 32.43333 degree RAEP = RAMC + 90 ...
A: Parantap: This is a good question, but unfortunately I don't remember anything from my Trig class ...
|
| Sum function | 11/28/2007 |
Q: In a range of values i want to get sum of highest or lowest 10 values and their averages? for ...
A: Mansoor: Let us assume your 25 values are in cells A1:A25. Here is the formula for the Top 10 ...
|
| Count Columns to reach 0 | 11/27/2007 |
Q: In A1 I have a value of 10; B1=-1, C1=-3, D1=-2, E1=-5, F1=-6 and G1=5; H1 would contain the formula ...
A: CJ: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/finddigit.xls ...
|
| Calculating Regular Worked Hours in 1 cell minus the OT hours | 10/30/2007 |
Q: I have a timecard spreadsheet in which I am creating and am having a heck of a time calculating ...
A: Steve: Time can be one of the trickiest aspects of Excel to get a hang of. Assuming your times are ...
|
| Calculating Regular Worked Hours in 1 cell minus the OT hours | 10/30/2007 |
Q: I have a timecard spreadsheet in which I am creating and am having a heck of a time calculating ...
A: Steve: You more or less answered your own question :). For overtime you use the exact formula you ...
|
| Auto Sum with Categories | 10/29/2007 |
Q: I would like to do an auto calculation of Expenses based on Categories of key in along with amount. ...
A: Ah Tan: I have uploaded a sample spreadsheet here: ...
|
| Timesheet | 10/29/2007 |
Q: I am try to make a timesheet for myself I do a rotating shift and my days off changes every three ...
A: Angelo: Please feel free toe mail to Lotus@swbell.net. Can you please also provide something along ...
|
| Color coding Calculations | 10/28/2007 |
Q: I need to know how to color code a calculation based on it's count. Right now, I have a spreadsheet ...
A: Crystal: If you set up the conditional formatting properly, you should be able to simply do the ...
|
| Numbering Cells in a Column, Excel | 10/28/2007 |
Q: I want to number the cells in a column (e.g. 1-100) in a way that stays correct even after sorting, ...
A: Tom: See if this works for you...place this formula in cell A3: =ROW()-2 Then you may copy the ...
|
| Limiting drop-down menu choices based on previous choices | 10/28/2007 |
Q: I have a series of drop-down menus, some of which are related to each other and I want to ...
A: Excel has a problem with using data validation lists and non-continuous ranges. This means, for ...
|
| Timesheet | 10/28/2007 |
Q: I am try to make a timesheet for myself I do a rotating shift and my days off changes every three ...
A: Angelo: I have uploaded a sample spreadsheet here: ...
|
| Household Mailing Labels | 10/27/2007 |
Q: I need an EXCEL formula for creating Household Labels from individual person records. In other ...
A: Vicki: I'm afraid this may be one of those situations where there are lots of variables that will ...
|
| Vesting | 10/25/2007 |
Q: What is a good formula for tracking vested options under the following schedule: after 1st year, ...
A: Christina: Okay, going to be very blunt here - not trying to be mean :), but I believe your math is ...
|
| Vesting | 10/24/2007 |
Q: What is a good formula for tracking vested options under the following schedule: after 1st year, ...
A: Christina: I need to get some additional information from you because I think I'm misunderstanding ...
|
| Vesting | 10/24/2007 |
Q: What is a good formula for tracking vested options under the following schedule: after 1st year, ...
A: Christina: I suggest something like this: http://home.swbell.net/nate-sus/excel/vestingsample.xls ...
|
| Sumproduct & MIN with criteria restricted to greater than 0 | 10/23/2007 |
Q: I am trying to use sumproduct to find the minimum date in a range subject to a criteria but the ...
A: Gwynneth: How about this formula? ...
|
| Sumproduct & MIN with criteria restricted to greater than 0 | 10/23/2007 |
Q: I am trying to use sumproduct to find the minimum date in a range subject to a criteria but the ...
A: Gwynneth: I believe I understand what you are trying to do - if not, I apologize and request you ...
|
| Graphs | 10/23/2007 |
Q: I'm trying to plot a graph with dates and time involved. Eg: 2/2/06 09:36 2/2/06 16:45 ...
A: Lee: Absolutely. The XY chart will actually allow you to add several sets of data. I have ...
|
| Lookup Function with Dates | 10/23/2007 |
Q: I currently have three columns A,D and F. A and D contains dates and F contains the moisture of the ...
A: Graham: I've never been a big fan of the LOOKUP formula - it never really does what I want it too - ...
|
| None numeric 'Sums' | 10/22/2007 |
Q: (I came across this site and think this is the way to use it). I need to find a formula which will ...
A: Chris: You are correct, COUNT() only counts numbers; however, there is another option -> give the ...
|
| Graphs | 10/22/2007 |
Q: I'm trying to plot a graph with dates and time involved. Eg: 2/2/06 09:36 2/2/06 16:45 ...
A: Lee: You are absolutely right; Excel's handling of time is very confusing. I do offer this website ...
|
| Excell formula | 10/19/2007 |
Q: Ok here we go. I am getting an error if i average more than 30 numbers. I need the average from ...
A: Dennis: Okay...here is formula #2...slightly more complex, but I think this will work better in ...
|
| Excell formula | 10/19/2007 |
Q: Ok here we go. I am getting an error if i average more than 30 numbers. I need the average from ...
A: Dennis: Excel 2003 and earlier have a limitation of 30 arguments. Here is a website that details ...
|
| Formula | 10/18/2007 |
Q: I will appreciate your help I have to calculate materials on and off by using formula below ...
A: The invalid name is likely due to using { and [ in your function...Excel requires all parenthesis. ...
|
| Lookup formula | 10/18/2007 |
Q: I have set up several spreadsheets holding maintenance records for farm machinery within this is a ...
A: Jason: Yes, a lookup is definitely the way to go and I suggest the VLOOKUP formula. I have ...
|
| Sum of zulu time | 10/17/2007 |
Q: How would i add time in a 24 hour format, to give a decimal? ex. 2300 + 0600 = 7.0 or 0000 + 0340 = ...
A: Dave: Good morning! My apologies for not getting right back to you - It looks like I just missed ...
|
| Sum of zulu time | 10/17/2007 |
Q: How would i add time in a 24 hour format, to give a decimal? ex. 2300 + 0600 = 7.0 or 0000 + 0340 = ...
A: Dave: Times can be difficult to work with sometimes. You have to have the data entry just right ...
|
| IF function | 10/17/2007 |
Q: I have aThe excel file I have is a list with a title: in this list there are 3 possible answers: - ...
A: Tom: I believe this may be what you are looking for...if I understand your intentions correctly: ...
|
| Excel formula stock options | 9/28/2007 |
Q: Ex. 10,000 stock options were granted on 1-1-07. The options vest quarterly- on 3-31-07 he'll have ...
A: Dean: I have uploaded another sample here: http://www.snapdrive.net/files/487599/vesting2.xls This ...
|
| Excel formula stock options | 9/28/2007 |
Q: Ex. 10,000 stock options were granted on 1-1-07. The options vest quarterly- on 3-31-07 he'll have ...
A: Dean: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/vesting.xls ...
|
| External data from files - filenames | 9/28/2007 |
Q: So I have this sheet, and I've got cells with ='file:///C:/Work/Timesheets/07-08-12 ...
A: Jay: The answer is yes, with certain problems doing this type of linking. You will want to use the ...
|
| probably a simple excel question | 9/27/2007 |
Q: You helped me before on a formula issue. I have a spreadsheet with data on one worksheet that I am ...
A: Mike: Let me know if this works for you...instead of cell XY and cell ZZ I'm going to use the ...
|
| like data not in same columns of worksheet | 9/27/2007 |
Q: Our IT department used to only send our payroll data on greenbar paper. Now they provide an ...
A: Dave: I agree...a bunch of nested IF statements are usually difficult to follow. I suggest the ...
|
| Conditional Formatting | 9/26/2007 |
Q: I'm trying to use conditional formatting on a range of cells and can't quite get the results I need. ...
A: Jeff: Presuming your 6 numbers above are located in cells A1:F1, then you would use this as the ...
|
| If/Count formulas | 9/25/2007 |
Q: I am having problems with if and count statements. If I have individuals with compensation between ...
A: Susan: You perfectly described the problem - it is actually a relief to have this kind of clarity ...
|
| Formula | 9/25/2007 |
Q: Column A has values as an order of shown example below.I want to split it in two column B and C as ...
A: Prem: Try placing this formula in cell B1: =INDIRECT(ADDRESS((ROW()-1)*2+1,1)) Place this formula ...
|
| paste into a data filtered column | 9/25/2007 |
Q: I have a long column of numbers. some have info in the cell to the right of them some dont. I used ...
A: Jay: Here are the steps you will take... 1) Find a blank column and insert this formula into all ...
|
| paste into a data filtered column | 9/25/2007 |
Q: I have a long column of numbers. some have info in the cell to the right of them some dont. I used ...
A: Jay: Can you select the entire range that includes all of the VLOOKUP items...or are there some ...
|
| Excel formula | 9/25/2007 |
Q: Trying to create a formula that will enable me to lookup from a list and return those customers that ...
A: Victor: There are a variety of reasons the above formula doesn't work (and none of them actually ...
|
| paste into a data filtered column | 9/24/2007 |
Q: I have a long column of numbers. some have info in the cell to the right of them some dont. I used ...
A: Jay: Okay...if all of the blank cells are going to contain the same exact data you can do this: To ...
|
| Comment Box??? | 9/24/2007 |
Q: I am working with a spreadsheet that I didn't create. It has a box that appears to be a comment box ...
A: Alicia: Typically comments are tied to a specific cell. As you indicated, when you select a ...
|
| AverageIf in multiple cells | 9/23/2007 |
Q: How can we calculate average of multiple cells (not a range of cells) in an excel work sheet? E.g. ...
A: Sarfraz: I'll answer your question first and then I'll explain the original answer and how it ...
|
| AverageIf | 9/22/2007 |
Q: How can we calculate the average of different cells (opposed to range of cells) in a worksheet with ...
A: Sarfraz: There might be a better way to do this, but I know the following will work: ...
|
| AverageIf in multiple cells | 9/22/2007 |
Q: How can we calculate average of multiple cells (not a range of cells) in an excel work sheet? E.g. ...
A: Sarfraz: There might be a better way to do this, but I know the following will work: ...
|
| XCEL: can I replace text w/ a new column? | 9/20/2007 |
Q: Greetings, I have an XCEL spreadsheet with a list of names and titles, formatted like this: Ronald ...
A: Kris: Give this a shot.. Presuming your data is all in column A and there is currently nothing in ...
|
| line chart | 9/20/2007 |
Q: I am trying to create a line chart in excel . The data I would like to plot is ...
A: Amanda: By default, when using dates for an Axis, Excel likes to plot a "smoother" timeline. Thus, ...
|
| Excel formula does not update in spreadsheet. | 9/19/2007 |
Q: I have an excel function that I'm trying to use... =AVERAGE(IF(Data!$V2:$V51=2,Data!$W2:$W51,FALSE)) ...
A: Mike: You are using arrays in your formula. Try confirming the formula with CTRL-SHIFT-ENTER ...
|
| Help with dropdown list functions | 9/19/2007 |
Q: I have made this spreadsheet. However it is rather on the large size. This is manly due to the fact ...
A: James: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/3lists.xls ...
|
| Comparing Two Column with third Column of anothe table | 9/19/2007 |
Q: I want to know suppose i have two table table1,table2 table 1 have three column a1 contains ...
A: Rashmi: I see you went with my suggestion on the spreadsheet I told you about....I recognize your ...
|
| Calc Depreciation by Quarters | 9/18/2007 |
Q: Nathan ~ I will attempt to explain in simple terms, but the outcome should span multiple years ...
A: Chris: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/depr.xls ...
|
| Comparing Two Column with third Column of anothe table | 9/18/2007 |
Q: I want to know suppose i have two table table1,table2 table 1 have three column a1 contains ...
A: Rashmi: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/stock.xls ...
|
| validation using lookup | 9/15/2007 |
Q: how can i create a pull down list in B23 which does a lookup on A23 and searches multiple lists in ...
A: Chris: I'm not 100% sure I understand the question. As such, I have uploaded a sample spreadsheet ...
|
| CONCATENATE FORMULA | 9/14/2007 |
Q: I am trying to use the CONCATENATE formula to display only the last 4 digits of a social security ...
A: FOLLOWUP - F9 forces the spreadsheet to recalculate - which would only work if somehow calculation ...
|
| Excel 2003 | 9/13/2007 |
Q: I am trying to set up a spreadsheet to compute parts produced per hour or minute. I am using a basic ...
A: Kathi: I speculate you are running into some issues with how Excel handles time. Please send your ...
|
| Excel | 9/13/2007 |
Q: Could you help me please? I have 2 sheets of information. On the 1st sheet I have 100 lines of data ...
A: Ivan: Give this a shot: 1) In the same sheet with your data insert this formula into cell E1: ...
|
| extracting unique triplets of data | 8/30/2007 |
Q: I am working with an Excel table that contains positions for locations called A,B,C and D. These ...
A: Jason: This can be done with a little bit of data manipulation. Please feel free to refer to the ...
|
| Minimum and Average Functions | 8/30/2007 |
Q: How do I take a column of data and from it extract the minimum and average value excluding any zeros ...
A: Jason: Give the following array formulas a whirl and let me know if they work for you (they worked ...
|
| Numbering line items | 8/30/2007 |
Q: Is there a formula I can write to number line items specific to a Claim Number? For example, I have ...
A: Mary: Presuming your claim numbers are located in column B, place this formula in cell A1: ...
|
| spreadsheet help | 8/28/2007 |
Q: colA I have quantities colH I have L,or XL,or XXL I would like to add on another sheet how many L's ...
A: Ryan: If any of the cells in $A$1:$A$1000 contain text, then you will get a #Value! error. I ...
|
| spreadsheet help | 8/28/2007 |
Q: colA I have quantities colH I have L,or XL,or XXL I would like to add on another sheet how many L's ...
A: Ryan: Give these formulas a shot. Please note I only estimated for 1000 rows of data - if you have ...
|
| Linking one sheet to another | 8/27/2007 |
Q: I'm really stuck, I'm hoping you may be able to help. On sheet 1 I have a list of people in column ...
A: Joe: If I interpreted your question correctly, then what you are requesting is slightly complicated ...
|
| Query about Excel | 8/24/2007 |
Q: I wonder if you can help me. I am trying to create a spreadsheet where I can create reports based ...
A: Marcos: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/phone.xls ...
|
| Advanced Excel Formulaes | 8/24/2007 |
Q: My Spreadsheet looks like this Column A contains Start Dates Column B Contains "Public" or "Private" ...
A: Roger: I believe I understand what you are looking for and I suggest the following formula: ...
|
| Nested IF statement help! | 8/23/2007 |
Q: Nathan, I have a question and I am not sure if I am approaching the problem the best way. ...
A: Ryan: I believe I have a solution for you. Instead of a nested IF lets go with SUMPRODUCT. I have ...
|
| formula question | 8/23/2007 |
Q: quickIn TabA, I have 4 columns (A,B,C,D). A,B, and C contain numerical values. In column D, I want ...
A: Daniel: Please place this formula in cell D2: =INDEX($A$1:$C$1,MATCH(MAX(A2:C2),A2:C2,FALSE)) This ...
|
| PIVOT | 8/23/2007 |
Q: I have 3 columns: one called brand; another called total07, and another called total06. Sometimes a ...
A: Gigi: I presume your pivot table only shows the 07 totals. Otherwise, doing the below is going to ...
|
| compiling 2 sheets info into 1 | 8/23/2007 |
Q: I have 2 sheets in Excel that I need to combine all the information into 1 sheet, at the same time ...
A: Michelle: This can be partially done through the use of a Pivot Table; the rest will be by ...
|
| Can you do a lookup with 2 values and return 1 value? | 8/22/2007 |
Q: I have been able to do this with dget, but dget requires you to put the database line on top of your ...
A: Joseph: This can be done with the SUMPRODUCT function. I have uploaded an example here: ...
|
| Excel If statement | 8/22/2007 |
Q: I have a tough Excel question. I am trying to programmatically evaluation two different columns ...
A: Tom: Unfortunately, versions of Excel prior to 2007 have a limit of 255 characters in a formula and ...
|
| Excel - Find the first postive value after the min value is found | 8/22/2007 |
Q: I have an excel spreadsheet that calculates the cashflow for projects. I'm using the following code ...
A: Linda: Are you holding CTRL-SHIFT-ENTER at the same time? In other words, hold CTRL & SHIFT ...
|
| Sum for criterion within a certain date range | 8/22/2007 |
Q: Let's say Column A has dates that a sales order originally booked. Column B has debooking amounts. ...
A: I suggest a SUMPRODUCT formula since we have multiple conditions (less than one date and greater ...
|
| Excel - Find the first postive value after the min value is found | 8/21/2007 |
Q: I have an excel spreadsheet that calculates the cashflow for projects. I'm using the following code ...
A: Linda: Give this formula a shot and let me know if it works in your situation (it seemed to work ...
|
| Excel If statement | 8/21/2007 |
Q: I have a tough Excel question. I am trying to programmatically evaluation two different columns ...
A: Tom: I have a solution for you. I'm going to provide it in segments to explain how it works and ...
|
| mode on excel | 8/21/2007 |
Q: I know how to use the function key to calculate mode on Excel, but is there a way to calculate the ...
A: Paul: I have uploaded a reference file here: http://home.swbell.net/nate-sus/excel/antimode.xls ...
|
| cross reference | 8/20/2007 |
Q: What does the function "ISNA" do?
A: Jan: Some functions, such as VLOOKUP, MATCH, and HLOOKUP, will return a value of #N/A if the item ...
|
| Array Formula? | 8/20/2007 |
Q: Question I am working on an inventory spreadsheet that lists product SKU, units sold, demand, and ...
A: Bruno: Well, array formula might be the way to go if you wanted a count of the number of policies ...
|
| creating scrollbars in Excel 2007 | 8/20/2007 |
Q: In Excel 2003, I used to make scroll bars (via the control toolbox) to be able to quickly adjust a ...
A: Paul: The control toolbox is rather well hidden in Excel 2007. You can gain access by following ...
|
| COMPLICATED FORMULA FOR EXCEL | 8/19/2007 |
Q: EXCEL: I’ve searched everywhere to figure this out and would greatly appreciate it if this could be ...
A: Charles: Okay, I think I'm following you here, but if not just email a spreadsheet to me at ...
|
| Thankyou | 8/19/2007 |
Q: Nathan, I sent you a question yesterday to find a formula to track daily vendor payments. You ...
A: Irfan: Spreadsheet for review: http://home.swbell.net/nate-sus/excel/cleared.xls Consolidate is a ...
|
| Query on Excel Formula | 8/19/2007 |
Q: I have an excel file with 3 sheets. I need to link the 3 sheets in such a way that I enter data into ...
A: Aadarsh: I have made some changes to the enclosed spreadsheet. Here are my notes: 1) On DPR I ...
|
| cross reference | 8/18/2007 |
Q: I just received your message and received your acive.xls attachment. THe ...
A: Jan: I have uploaded a new spreadsheet here: http://home.swbell.net/nate-sus/excel/acive.xls If ...
|
| excel | 8/18/2007 |
Q: ROW A4:AZ4 IS A DATE ROW ROW A5:AZ5 IS A NUMBER (OR BLANK OR ZERO) WHAT I NEED TO DO IS TO SELECT ...
A: Nick: I have uploaded a sample sheet here: http://home.swbell.net/nate-sus/excel/newdates.xls I ...
|
| cross reference | 8/18/2007 |
Q: I have very little experience in Excel. I am using 3 sheets (sheet1, sheet2 and masterlist). In ...
A: Jan: I presume COL D of the mastersheet already has the policy number in it - if you copied from ...
|
| Query on Excel Formula | 8/18/2007 |
Q: I have an excel file with 3 sheets. I need to link the 3 sheets in such a way that I enter data into ...
A: Aadarsh: Please go ahead and send me an email. I suspect you are looking for something similar to ...
|
| IF STATEMENT | 8/17/2007 |
Q: I need to do a lookup on a range of alpha numeric values (Canadian Postal codes) where I have the ...
A: Michael: I have uploaded a sample file here: http://home.swbell.net/nate-sus/excel/canzip.xls I ...
|
| Excel | 8/17/2007 |
Q: I am trying to make a spreadsheet that will auto-update if a cell matches a specific number. If ...
A: NOTE - File sent to your email address: Hi Dawn: Ahh…that helped immensely! Index and Match are ...
|
| Excel | 8/17/2007 |
Q: I am trying to make a spreadsheet that will auto-update if a cell matches a specific number. If ...
A: Dawn: I will need additional information to properly answer this question - I'm just not following ...
|
| Excel | 8/16/2007 |
Q: I am trying to make a spreadsheet that will auto-update if a cell matches a specific number. If ...
A: Is this what you are looking for? Type the following into cell D59: =IF(A2=1091,A1,"") If ...
|
| Excel | 8/16/2007 |
Q: I am not new to Excel but it has been many years and I am having big problems, could you possibly ...
A: Heather: Presuming your value is in Cell A1 and you have Excel 2007, then you can use this formula: ...
|
| Data Plot Line ''Disappears'' When X Axis Formating Is Changed | 7/30/2007 |
Q: SITUATION: I've never used Exel for chart-creation. This question probably makes that all too ...
A: John: Based on my understanding of the question, the plot line disappeared because the values on ...
|
| Excel Formula | 7/23/2007 |
Q: Column A = Geographical Area Column B = A Date (Meaning Date Completed,) PROPOSED (Job is planned), ...
A: Roger: How about this formula: ...
|
| Bonus Pay out | 7/23/2007 |
Q: This question is with reference to bonus calculation for employees. There are 103 employees and the ...
A: Anoop: I presume, since you didn't specify, that the formula that "works" is this one: ...
|
| formula | 7/23/2007 |
Q: I am trying to find the formula: I have a function that can take 4 known values(W).I would like ...
A: Ricardo: I do not understand the question. Can you give me a few examples of before and after? ...
|
| formula | 7/23/2007 |
Q: I am having problems with a cumulative formula in excel 2003. I am not sure how to write the formula ...
A: Ricardo: According to Algebra, the sum of all "natural" numbers leading up to a specific number is ...
|
| cross-sheet calculation | 7/23/2007 |
Q: Hallo there, I am working on a sheet for the purpose of calculating costs allocated to customers. ...
A: Julia: Your description was just fine :). I have uploaded a sample spreadsheet here: ...
|
| Excel Count Query | 7/22/2007 |
Q: I have three columns in a worksheet with heading; (ref#, amount, fee type). There are three ...
A: James: I'm not 100% sure I understand the question, so I have provided a spreadsheet with 2 ...
|
| display multiple answers from a search bar | 7/22/2007 |
Q: I've used one of your formula's from an answer I found just over a year ago called "how to make ...
A: Miro: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/formiro.xls ...
|
| comparing data fields | 7/20/2007 |
Q: I saw your last answer for a similar problem and that was a brilliant answer. However, in this ...
A: Lsmail: I have uploaded a file for your review: ...
|
| Filtering data matching 2 criteria | 7/20/2007 |
Q: NOTE: SIR THIS IS A CORRECTION FROM MY FIRST QUESTION. I have a employee master list in Sheet1 ...
A: Erwin: I have uploaded a solution here: http://home.swbell.net/nate-sus/excel/specialsort.xls ...
|
| Filtering data matching 2 criteria | 7/20/2007 |
Q: I have a employee master list in Sheet1 (Emp.No, Emp. Name, Date joined[dd/mm/yyyy], Status placed ...
A: Erwin: I have uploaded a solution here: http://home.swbell.net/nate-sus/excel/specialsort.xls ...
|
| calculate based on conditions? | 7/20/2007 |
Q: Column A has different data such as: 2C, 4C, GR Column B has either Y or N _____Sample: 2C Y 2C ...
A: Theresa: It appears you need a formula slightly more versatile than countif. I suggest the ...
|
| calculating time lapsed | 7/19/2007 |
Q: Iam trying to create a formula that calculates the difference between two dates using dd/mm/yy ...
A: Carole: Re: "a time stamp of before 12:00 then day is classed as day 1, if after 12:oo then next ...
|
| Bonus Pay out | 7/19/2007 |
Q: This question is with reference to bonus calculation for employees. There are 103 employees and the ...
A: Anoop: I will need more information to answer this question. Can you tell me how you determine 100 ...
|
| Date/Days formula | 7/19/2007 |
Q: I need to calculate the number of days between two dates to show either plus or minus. Example: ...
A: Phil: You have probably noticed you cannot subtract a larger date from a smaller one; thus, you ...
|
| Creating a Surplus/Deficit Column | 7/18/2007 |
Q: I am a self-taught rookie using Excel 2002 to create a household budget with the fiscal year ...
A: Dylan: New spreadsheet (be sure to click refresh if you see the old one): ...
|
| Today() function | 7/18/2007 |
Q: I am review a bunch of spreadsheets and someone in their wisdom has used the "=today()" function ...
A: Dom: You may replace the today() function with this in your formula: DATE(2007,7,18) This will ...
|
| Creating a Surplus/Deficit Column | 7/18/2007 |
Q: I am a self-taught rookie using Excel 2002 to create a household budget with the fiscal year ...
A: Dylan: Can you review this spreadsheet and let me know if it will work for you? ...
|
| Conditional Forms | 7/17/2007 |
Q: I am having some serious trouble. I have a form where I would like an option (Text box or just a ...
A: Arvind: Note: Revised spreadsheet with a new example here: ...
|
| Conditional Forms | 7/17/2007 |
Q: I am having some serious trouble. I have a form where I would like an option (Text box or just a ...
A: Arvind: Is this what you are looking for? http://home.swbell.net/nate-sus/excel/formcond.xls In ...
|
| Excel chart | 7/17/2007 |
Q: Can I format a bar graph so that the individual bars are colored to correspond to the cell the data ...
A: Simon: You may color each bar individually in Excel by following one of these options: Option #1) ...
|
| excel date formula | 7/17/2007 |
Q: I am trying to get the year part of a date to automatically update to the next year. For example, I ...
A: Scott: Well, I can provide an answer, but you will need to tweak it to your situation (since I ...
|
| exporting from on list to another | 7/16/2007 |
Q: the thing is, i want to be able to creat an excel spreadsheet that i can cut and paste data into, in ...
A: Nicholas: Check out my spreadsheet here: http://home.swbell.net/nate-sus/excel/tomfrankfred.xls I ...
|
| Excel formula | 7/16/2007 |
Q: For our youth sports association, I need to: 1. Compare the list of players from last season to the ...
A: Mark: Check out this spreadsheet: http://home.swbell.net/nate-sus/excel/findduplicates.xls I ...
|
| Trying to count certain events | 7/16/2007 |
Q: Let me first say thanks for even offering something like this. Fills good to ask a question to ...
A: Greg: I have uploaded my guess at what you are looking for here: ...
|
| conditional formatting excluding blank cells | 7/16/2007 |
Q: I am working on a spreadsheet where I am comparing work order start and completion dates. There are ...
A: Michael: Check out my spreadsheet examples here: ...
|
| Autocomplete in Drop Down List | 6/20/2007 |
Q: I have cells containing very large dorp down lists (offering many possible entries). Is it possible ...
A: Ann: Yes, this can be done; however, it requires very specific placement of your data-validation ...
|
| Auto update/averaging of cell with date | 6/19/2007 |
Q: I would like to create a sheet that can auto update the cell and do auto calculation with the date I ...
A: Dennis: Can you review the spreadsheet here: http://home.swbell.net/nate-sus/excel/monthlyavg.xls ...
|
| Auto Drop-down Menu for Navigation | 6/19/2007 |
Q: I would like to create a drop-down menu(not via Cell but an object which I can move arrount) which ...
A: Dennis: I believe I know what you are looking for - a menu that is "on top" of everything and can ...
|
| Creating a summary sheet from varying numbers of worksheets. | 6/18/2007 |
Q: without having to click "=" and click on the cell i am referring to every time. To provide an ...
A: Andrea: I have uploaded a sample spreadsheet here: ...
|
| Time in excel | 6/17/2007 |
Q: I have been trying to use time in excel but time as pace for runners. Example a pace of 8:00 per ...
A: Martin: In the formula bar, Excel is always going to place an AM or PM - but fortunately, you can ...
|
| complex averaging | 6/15/2007 |
Q: I have a spreadsheet that documents a daily temperature at noon every day. And use it to prepare ...
A: Maria: Presuming your formula is located in cell A1, you could use this formula: ...
|
| aging a date in exel | 6/14/2007 |
Q: I want to age a date from todays date and stop aging when a resolve date is entered. My date is in ...
A: Tonya: Here is an example.. Cell B1 contains the date 5/10/07. Cell C1 contains the date 6/10/07. ...
|
| COUNTA...COUNTIF...IF....not sure which one (if any) I need | 6/13/2007 |
Q: So I'm trying to idot-proof a worksheet on Excel 2003. Heres the problem: I have a range of cells ...
A: Barrett: There are a variety of ways to do this so I'll lay out a couple and let you pick the one ...
|
| Calculating a date in excel | 6/13/2007 |
Q: I need to calculate the date insurance will kick in for employees.H ow do i write a formula that ...
A: Randi: Presuming your hire date/start date is located in cell A1, then this formula should work: ...
|
| Remove subtotal in bar chart | 6/13/2007 |
Q: I've a list of total in different category and i need to have a subtotal for each category. From ...
A: Ling: The answer depends on how your bar chart is set up. Generally speaking, you can exclude the ...
|
| Excel functions | 6/12/2007 |
Q: Is there a way I can add only the numbers in odd or even rows?
A: Kathy: Presuming your data is located in cells A1:A25, you can use this formula for the odd rows: ...
|
| Excel Rounding | 6/12/2007 |
Q: I have numbers coming in a format of X.XXX, i need to format them so they go out X.XXXX rounded to ...
A: Patrick: This will need to be done in 2 steps. Step 1 - Formatting: 1) Select the cell you want to ...
|
| Numbering paragraphs | 6/11/2007 |
Q: I'm trying to figure out a way to number paragraphs/questions in a questionnaire, where the ...
A: No problem...how about this? ...
|
| Numbering paragraphs | 6/11/2007 |
Q: I'm trying to figure out a way to number paragraphs/questions in a questionnaire, where the ...
A: My first thought is to do the following for Sheet2: ...
|
| cascaded list | 6/10/2007 |
Q: jimenel matlab io_utils readtext2.m jimenel matlab io_utils readtext.m ...
A: Polina: The short answer is no, Excel itself doesn't provide a naming convention that would handle ...
|
| cascaded list | 6/9/2007 |
Q: jimenel matlab io_utils readtext2.m jimenel matlab io_utils readtext.m ...
A: Polina: I wasn't sure if this was an additional question or not. In the original spreadsheet I ...
|
| cascaded list | 6/8/2007 |
Q: jimenel matlab io_utils readtext2.m jimenel matlab io_utils readtext.m ...
A: Polina: This is kind of a preliminary spreadsheet, but does it essentially do what you are looking ...
|
| Excel Function to total using database | 6/8/2007 |
Q: Nathan, I have a database that has budgeted tons and revenue by material type by plant location by ...
A: Give this formula a shot: =SUMPRODUCT((A2:A1000="Steel")*(B2:B1000="A")*(C2:C1000)) SUMPRODUCT is ...
|
| Lookong for Good Formula | 6/8/2007 |
Q: Hope you are fine. I am looking for a formula that contain minimum cells. in A1 0 to 11 may come as ...
A: Note, you said: If B1=A1, That means c1 = 1, If B1=A1+1, then c1=2, If B1=A1+2, then c1=3 If ...
|
| overtime trickiness | 6/8/2007 |
Q: I figured out how to make my timesheet calculate overtime for me using MIN and MAX. But I'm having a ...
A: Kim: First off...I don't think I could handle your hours :D. Second...can you check out this ...
|
| Pivot table and pivot charts | 6/7/2007 |
Q: Can u please suggest me some good link from where i can learn Basic pivot table along with advanced ...
A: I have created a quick example of a Pivot Table and a Pivot Chart. You may download it here: ...
|
| Lookong for Good Formula | 6/7/2007 |
Q: Hope you are fine. I am looking for a formula that contain minimum cells. in A1 0 to 11 may come as ...
A: 1) Based on what you said, this should work (this was the first formula I listed in the previous ...
|
| Pivot table and pivot charts | 6/7/2007 |
Q: Can u please suggest me some good link from where i can learn Basic pivot table along with advanced ...
A: I did some searching with Google and I found this website. It seems to have lots of good examples ...
|
| Lookong for Good Formula | 6/7/2007 |
Q: Hope you are fine. I am looking for a formula that contain minimum cells. in A1 0 to 11 may come as ...
A: 1) The reason your IF formula isn't working is because Excel has a limitation of 8 nested IF ...
|
| excel conditional list | 6/6/2007 |
Q: I want to display a dropdown list in a certain cell if the cell next to it contains "OP". OP is ...
A: Jason: I can only suspect that your Internet browser is not updating with the latest file. As ...
|
| excel conditional list | 6/6/2007 |
Q: I want to display a dropdown list in a certain cell if the cell next to it contains "OP". OP is ...
A: Let me know if this works: http://home.swbell.net/nate-sus/excel/partlist.xls I used some trickery ...
|
| excel conditional list | 6/6/2007 |
Q: I want to display a dropdown list in a certain cell if the cell next to it contains "OP". OP is ...
A: Hey Jason! Check out this spreadsheet: http://home.swbell.net/nate-sus/excel/partlist.xls I ...
|
| comparing two columns of number | 6/5/2007 |
Q: What formula could I use to compare two columns? Say I have 200 numbers in A and 400 numbers in B ...
A: Laura: I have uploaded a sample spreadsheet here: ...
|
| Populating a drop down with data dependent from another drop down | 6/4/2007 |
Q: I've got 2 drop down fields on a worksheet created via the Data/Validation functionality. The data ...
A: Bryan: This appears to be the question of the week :). Please check out this spreadsheet (it is ...
|
| Retrieving data using two drop-down lists | 6/1/2007 |
Q: I'm trying to retrieve information/data using two drop-down lists. Drop-down List #1 shows large ...
A: Sorina: I presume you meant B7 and B10...B14 is just a quick formula to calculate what column of ...
|
| Retrieving data using two drop-down lists | 6/1/2007 |
Q: I'm trying to retrieve information/data using two drop-down lists. Drop-down List #1 shows large ...
A: Sorina: I have uploaded a spreadsheet here: http://home.swbell.net/nate-sus/excel/japan.xls I ...
|
| return count of 2 values based on 1 criteria | 6/1/2007 |
Q: I have a spreadsheet containing supplier delivery details, I'm SUMPRODUCT to count "delivery" ...
A: John: I may need you to email a sample spreadsheet to Lotus@swbell.net if this doesn't answer your ...
|
| Array Question on Excel | 5/31/2007 |
Q: I am trying to create an array formula with an If/Then statement. The formula that I have been using ...
A: Alissa: If you have an extra header row on the main worksheet, CounselingLog, then you won't need ...
|
| Conditional formatting question | 5/30/2007 |
Q: I wanted to ask you a quick question related to conditional formatting. If I have the following row ...
A: Vincent: A formula format with conditional formatting should do what you need. Here are the steps: ...
|
| pivot table | 5/28/2007 |
Q: I have a question regarding pivot tables I have a pivot table however i understand that i cannot ...
A: That is very odd. What version of Excel are you using? Since wizard does not appear in the ...
|
| Advanced Filter | 5/28/2007 |
Q: I need to create an advanced filter tool on a particular worksheet where the user has control over ...
A: Harv: I have uploaded a spreadsheet here which follows the discussion: ...
|
| Difference of Two Columns | 5/26/2007 |
Q: I need to find a difference of two columns. My First column is the Starting Mileage of a car which ...
A: Mike: Does the following work? I'm making some assumptions on how your data is set up, but I ...
|
| pivot table | 5/25/2007 |
Q: I have a question regarding pivot tables I have a pivot table however i understand that i cannot ...
A: Sue: A pivot table is built from an original set of data. For example, on "Sheet 1" you might have ...
|
| Lookup | 5/22/2007 |
Q: Is there a variation of Lookup (Vlookup. Hlookup?)that will lookup a value within a range specified ...
A: Good morning Barry! The cause of this error is because I used the ROW() function in my formula. ...
|
| Lookup | 5/22/2007 |
Q: Is there a variation of Lookup (Vlookup. Hlookup?)that will lookup a value within a range specified ...
A: Barry: Lets presume the above data is in Cells A1:C3: ...
|
| Conditional add/count on filtered data | 5/22/2007 |
Q: I use subtotal(arg,range) to add and count data because it sums/counts correctly when data is ...
A: Barry: No problem - glad I could help. Learning array functions will open up a HUGE door for you - ...
|
| Conditional add/count on filtered data | 5/22/2007 |
Q: I use subtotal(arg,range) to add and count data because it sums/counts correctly when data is ...
A: Barry: Here is what I use to do a "COUNTIF" on filtered data. You should be able to modify this ...
|
| Excel question | 5/18/2007 |
Q: The question is not very well worded Im afraid.. This is something my work has asked if I can help ...
A: Liam: The problem is you cannot explicitly tell Excel to stop updating just one formula (for ...
|
| Formula for incremental projected data. | 5/16/2007 |
Q: I am creating a worksheet for projected sales in a 12 month period. The total sales for the year ...
A: Diana: I uploaded an example here... http://home.swbell.net/nate-sus/excel/diana-incremental.xls ...
|
| Formula for incremental projected data. | 5/16/2007 |
Q: I am creating a worksheet for projected sales in a 12 month period. The total sales for the year ...
A: It depends...will you still have 12 months, but one month doesn't increase....or will you only be ...
|
| Formula for incremental projected data. | 5/16/2007 |
Q: I am creating a worksheet for projected sales in a 12 month period. The total sales for the year ...
A: Diana: This is a very subjective question which potentially has lots of ways to go about obtaining ...
|
| Sum function/formula is not working | 5/14/2007 |
Q: Nathan, I imported data from SAP system into excel into J3:14. When I use Sum function on J15 to sum ...
A: 1) DAT15 appears to be a named range that corresponds to J3:J14. Named ranges are "friendly" names ...
|
| Date-to-days formula | 5/14/2007 |
Q: Not exactly an advanced formula for you, but I'm stumped. On spreadsheet I've created, column C is ...
A: Patrick: Presuming you enter these values: 1) C1 contains 5/10/2007 (I know you said year was ...
|
| excel | 5/11/2007 |
Q: Each column possesses 100 rows containing randomly generated numbers. Can I take all 100 columns to ...
A: David: I'm pretty sure I do not understand the question. So, you have random numbers in columns B ...
|
| How to add a button | 5/11/2007 |
Q: Please can you tell me if there is a simple way to add a button (and name it)which will then open ...
A: Cliff: Did you record the macro yet? You need to record a macro that opens another sheet in the ...
|
| formula question | 5/11/2007 |
Q: so I have two worksheets. TabA lists companies in column A, and column C needs to show their ...
A: This is a very interesting question. We need to use an array formula, but the MAX() function will ...
|
| How to add a button | 5/11/2007 |
Q: Please can you tell me if there is a simple way to add a button (and name it)which will then open ...
A: Cliff: Before adding a button, you must record a macro that does what you need. To record a macro ...
|
| Graph labels | 5/9/2007 |
Q: How do I get a graph to display e.g. years on the bottom axis rather than the default of 1, 2, 3 ...
A: Jaco: This should work for most charts: 1) Right-click on the chart and choose source data. 2) ...
|
| Pivot table - field subtotals automatic calculation | 5/8/2007 |
Q: Is there a way to configure pivot table to turn off automatic calculation of subtotals for fields? ...
A: Mihails: Unfortunately, there is no "default" to have sub-totals set to "none" for Pivot Tables. I ...
|
| External references | 5/8/2007 |
Q: I have a question on external references. I have referenced an external sheet's cells successfully. ...
A: Steve: Please email the example to Lotus@swbell.net and I'll take a look. I presume you are using ...
|
| data validation | 5/4/2007 |
Q: I am looking to see if you can auto populate field(s) when you choose from a list. I have 3 colums ...
A: Peter: I uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/peterlist.xls I ...
|
| Excel Formula | 5/3/2007 |
Q: If I am using an "IF" formula and based on the results, it returns "Continue" or "React"; can I put ...
A: Betsy: You cannot change font with the IF formula itself, but you can use conditional formatting. ...
|
| FORMULA | 5/2/2007 |
Q: Do you have an Excel formula that would calculate how many months, between 2 dates, there are as ...
A: Mario: Quick question for clarification... Try this formula for me: =DATEDIF(A1,B1,"m") The older ...
|
| HEEEELP!! | 5/2/2007 |
Q: Good morning, I need help big time. I am in an excel spreadsheet, 1-I need to create a grey boxes ...
A: Good morning Nadia! 1) Normally I don't answer questions about Macros (as per my profile), but I'll ...
|
| If function Does not work after 8 Ifs | 5/1/2007 |
Q: I want to set if function. ...
A: The IF function is limited to 8 nestings by Excel. There is no workaround with the IF function. ...
|
| Sorting | 4/30/2007 |
Q: I have 8 lists if sign ingress of planets. e.g. Moon Gem 04/01/1996 02:24:13 Moon Can 06/01/1996 ...
A: Let's presume the information is organized as such: Column A1:A4 = Planet/Moon Column B1:B4 = ...
|
| web page filter | 4/25/2007 |
Q: I have an Excel 2000/03 sheet with Pvot tables, filter etc. When I save this as a web page the under ...
A: Saving as HTML is the same as saving as a web page. I have uploaded a new spreadsheet in reference ...
|
| web page filter | 4/25/2007 |
Q: I have an Excel 2000/03 sheet with Pvot tables, filter etc. When I save this as a web page the under ...
A: Sam: What you see is what you get...Excel doesn't give you any further controls over exporting to a ...
|
| Rank one column's values based on another | 4/20/2007 |
Q: What I have is a list of ranks in column A. In column B I have corresponding periods (say months) ...
A: Shaun: Try placing this array formula in C1: =SUMPRODUCT(--(B$1:B$100=B1),--(A1> A$1:A$100))+1 ...
|
| sorting lists | 4/19/2007 |
Q: I run a commercial aviation repair shop that has a long excel list of service capabilities listed by ...
A: John: I sort of see two questions here so I will address each one separately... 1) Merging two ...
|
| grouping dates by month | 4/18/2007 |
Q: I have a list of dates associated with three different projects. I want to create a bar graph ...
A: Laurie: Will these functions work? =MONTH() and =YEAR() For example, presuming your date, 4/1/07, ...
|
| merge two columns (match column's text string) | 4/18/2007 |
Q: I would like to create a new column to describe LIST A. How to match the text string in LIST B with ...
A: Presuming I understand the question correctly, this can be done with VLOOKUP. Setup: 1) List A ...
|
| Filtering and dropping duplicate records | 4/17/2007 |
Q: For our youth sports organization, we need to submit registration roles to a national governing body ...
A: It actually changes quite a bit...SUMIF can only handle 1 criteria...and now there are 2. No ...
|
| Filtering and dropping duplicate records | 4/17/2007 |
Q: For our youth sports organization, we need to submit registration roles to a national governing body ...
A: Mark: Here is what I would do. You need to create a new column for your table that indicates a ...
|
| excel countif formula | 4/14/2007 |
Q: i am trying to have a formula first look down a date column and if the date=2003, for example, then ...
A: Good afternoon! Presuming your dates are actually dates and just just a year (ia, 1/23/2003 and not ...
|
| Lookup function using ranges within conditional format | 4/13/2007 |
Q: I have four columns. Columns A & B and C & D . I'd like to use lookup to highlight similar data ...
A: Jim: This formula should give you a start: =MATCH(A1&B1,$C$1:$C$100&$D$1:$D$100,FALSE) This is an ...
|
| graphing problem with Excel | 4/13/2007 |
Q: I hope you can help me with a complex graphing problem I hope to solve with Excel. For the y-axis, ...
A: Bill: Please let me know if you have any more questions...this was a fun question for me...I've ...
|
| Time Tools! | 4/12/2007 |
Q: These time tools will be very useful to me. I need your help. 1.Date & Time + Date & Time = Date & ...
A: 1) Presuming A1 through H1 contains this data: 1/1/2007,00:00:00,5,4,2,5,45,6 You would use this ...
|
| need formula | 4/10/2007 |
Q: if j8 is between 1 and 20 then I8=9.26 if j8 is between 21 and 30 then I8=11.11 if j8 is 31 or ...
A: Rich: There are a variety of ways to do this and here are two of them: ...
|
| Thank You | 4/8/2007 |
Q: You have really tried your best to help me. Thank you very much. I am completely in fault. Actually, ...
A: 11PM to 4AM are two different days according to Excel. From 11PM until 11:59PM Excel is on day ...
|
| Looking for Best Formula | 4/7/2007 |
Q: Ok. I am trying to make my question Easy. There are 2 similar Questions. One is about EXCEPTION BOX. ...
A: 1) You are correct, it will say "Refresh Data". 2) I cannot explain why the data is not updating ...
|
| Date &Time (- minus) Date &Time!! | 4/5/2007 |
Q: 1.In Column A there is a data of Date & Time. (As below. Data -) 2.In Column P there is an ...
A: I have done a complete redesign of the spreadsheet. Here is the new version: ...
|
| Excel | 4/5/2007 |
Q: Is there any way i can set up my spreadsheet where if there's a duplicate number it will give me a ...
A: Sherlyn: Give this a shot... 1) In Excel select a cell and click insert->hyperlink 2) On the left ...
|
| mysterious addition of '2' in pivot table | 4/5/2007 |
Q: On a list I have run a pivot table to count the number of occurrences of an identifier. For some ...
A: Karen: Based on your description I believe you are seeing the ACRONYM in the "header" section of ...
|
| If then with several variables | 4/4/2007 |
Q: We are implementing a program to give students a card based on up to 5 criteria and want Excel to ...
A: Brian: There are a variety of ways to handle this...depending on the complexity of your various ...
|
| Excel | 4/4/2007 |
Q: Is there any way i can set up my spreadsheet where if there's a duplicate number it will give me a ...
A: Sherlyn: Presuming the invoice number you want to check is in cell A1, you could use the following: ...
|
| Count IF, If, Match? What is the best formula for me? | 4/4/2007 |
Q: Why I need a formula: Two program managers share ownership of certain accounts in a theater. I'm ...
A: Julie: It is a limitation of formulas that rely heavily on arrays (such as SUMPRODUCT). These type ...
|
| Looking for Best Formula | 4/4/2007 |
Q: Ok. I am trying to make my question Easy. There are 2 similar Questions. One is about EXCEPTION BOX. ...
A: I have done a complete redesign of the spreadsheet. Here is the new version: ...
|
| Count IF, If, Match? What is the best formula for me? | 4/3/2007 |
Q: Why I need a formula: Two program managers share ownership of certain accounts in a theater. I'm ...
A: Julie: I believe SUMPRODUCT will work for you. Presuming the program manager's name is in Col-F ...
|
| Excel | 4/3/2007 |
Q: I am having some problems with a function, I have a letter designation in the K column that I want ...
A: Don: VLOOKUP is the way to go...I suspect you are getting a #REF because some of your lookup values ...
|
| excel formula | 4/3/2007 |
Q: I do have an unique id numbers into column A.All Unique numbers has Description (Column B),Size ...
A: This can be done with a variety of lookup formulas such as VLOOKUP, INDEX, etc. I have uploaded a ...
|
| Count number of Orders for transaction type "Repair and return" | 4/3/2007 |
Q: Transaction Type Order number REPAIR & RETURN 1001 REPAIR & RETURN 1001 REPAIR & ...
A: Francis: This was a very interesting question and not something I have tried to do before; however, ...
|
| Trim leading alpha characters | 4/2/2007 |
Q: I have data that has information imbedded within that I need to extract...ex. IRR2.6/1.2/1.1-52 . I ...
A: Tim: Presuming the sample is in cell A1, you could use this formula: ...
|
| charts | 4/2/2007 |
Q: I have two columns of numbers and I want to build a line graph (plot)using both numbers on each row ...
A: Shane: When setting up your chart you will need to select a "XY Scatter Graph". This is one of the ...
|
| Excell Formula ? | 4/2/2007 |
Q: Need help in Excell. I'm setting up a spread sheet for my expense accounts and using different ...
A: Excel has two formulas for inserting the current date. =TODAY() =NOW() These formulas will always ...
|
| Formula | 4/2/2007 |
Q: I need help with a formula. I have one column that shows the letters(A,B,C,D,E,F or H) and another ...
A: Robert: Using just your data above, this formula provides the expected result: ...
|
| Looking for Best Formula | 4/1/2007 |
Q: In the series of dates I want to set exception(s). E.g. In Column A there are dates from 01/01/2007 ...
A: A appologize for taking an extra day to reply...I had intended to send you a response last night. ...
|
| Formula to spread costs based on start date and end date | 3/31/2007 |
Q: I'm looking for a formula that will spread employee costs over the coming year based on a start date ...
A: Russ: The simplest way is to divide your annual amount by 52 and apply that amount to each week. I ...
|
| Looking for Best Formula | 3/30/2007 |
Q: In the series of dates I want to set exception(s). E.g. In Column A there are dates from 01/01/2007 ...
A: I uploaded a new spreadsheet where I added a sheet 4. The only change I made was to change the ...
|
| Converting a formula for Dutch excel | 3/30/2007 |
Q: I have created a template that uses a "Date Lookup" sheet to reference to. To get the months I have ...
A: I haven't used the Dutch Excel, so I'm not sure of how different it might be. Would these formulas ...
|
| Column A not Column B | 3/29/2007 |
Q: I have two lists of IDs. Column A contains the entire list of IDs. Column B is a subset of Column ...
A: I have uploaded an example spreadsheet here: http://home.swbell.net/nate-sus/excel/2lists.xls I use ...
|
| Looking for Best Formula | 3/29/2007 |
Q: In the series of dates I want to set exception(s). E.g. In Column A there are dates from 01/01/2007 ...
A: I have uploaded a revised spreadsheet here: http://home.swbell.net/nate-sus/excel/stars.xls There ...
|
| Date Format Formula | 3/29/2007 |
Q: I have a set of data like this Date Month Jan-05 1 Feb-05 2 Mar-05 3 Apr-05 4 May-05 5 Jun-05 6 ...
A: Presuming the date is in column A, you could use this formula: ...
|
| Looking for Best Formula | 3/28/2007 |
Q: I have database in Column A. (0,1,2,3,4, -, -, -, 8,9, -, -, -, -, -15,.. 359, 0,1). Maximum Number ...
A: Okay.....building lists like this is going to take some formula "mumbo-jumbo". Since I cannot ...
|
| Looking for Best Formula | 3/27/2007 |
Q: I have following database in Excel. In Column A (Date – 1 TO 30), Column B (Degree of Sun – 21 TO ...
A: I have uploaded a new spreadsheet here: http://home.swbell.net/nate-sus/excel/stars.xls The ...
|
| Looking for Best Formula | 3/27/2007 |
Q: I have following database in Excel. In Column A (Date – 1 TO 10), Column B (Degree of Sun – 21 TO ...
A: Please refer to the example here: http://home.swbell.net/nate-sus/excel/stars.xls I placed a ...
|
| Looking for good formula | 3/26/2007 |
Q: In Excel I have some DATA. In Column A (Date) in Column B (Sale of that Date) Column C (Expense of ...
A: I am making the following assumptions: 1) In Column A (Date) in Column B (Sale of that Date) Column ...
|
| Splitting Information from one cell across multiple cells | 3/26/2007 |
Q: Nathan, What I am trying to do is speed up my data entry. Basically I enter for example in one ...
A: Sean: Presuming your date is in cell A1 (and it is a valid date and not text), you can use these ...
|
| Sum with two or more criteria | 3/26/2007 |
Q: I need formula in each cell, for this case. . . Date code Name of Account amount ...
A: Stefan: This can be done with the sumproduct formula. I have uploaded an example here: ...
|
| adding time for worked hrs | 3/24/2007 |
Q: I am trying to make an excel sheet to keep track of my hours plus overtime hours. Date ...
A: Luis: Check out this spreadsheet and let me know if it does what you are looking for: ...
|
| Filter | 3/22/2007 |
Q: I'm creating a spreadsheet where I have merged rows 1 & 2 in columns A, B, C, H, I, J, & K. In row 1 ...
A: Kelly: Presuming you are using auto-filter, you can do the following: 1) Select the range D2 ...
|
| Excel HELP! - formula | 3/22/2007 |
Q: I am trying to create a workbook for my employer that I would normally do but will be leaving for ...
A: Jennifer: Can you take a look at this sheet?: http://home.swbell.net/nate-sus/excel/months.xls ...
|
| Accounting Aging | 3/21/2007 |
Q: How do I set up an aging in Excel that will only calculate based on date. I would like to be able ...
A: Kara: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/aging.xls ...
|
| Data formula and analyzation | 3/20/2007 |
Q: ProductID Revenue Quantity Revenue Net FGI ASSY WIP DGI 1002-3P-1 4 $12,000 1 ...
A: Sometimes tables don't paste very well into Allexperts...here is the table as I interpreted it: ...
|
| trying to extract data from certain columns for an average | 3/20/2007 |
Q: Im trying to extract data from certain columns with a common Text "BSMT" in the heading. my formula ...
A: In Excel you can get an error message called "#N/A". Is this what the cells have, or are you ...
|
| trying to extract data from certain columns for an average | 3/20/2007 |
Q: Im trying to extract data from certain columns with a common Text "BSMT" in the heading. my formula ...
A: Dennis: We will need to add some extra items to the formula to have it ignore cells that contain an ...
|
| trying to extract data from certain columns for an average | 3/20/2007 |
Q: Im trying to extract data from certain columns with a common Text "BSMT" in the heading. my formula ...
A: Dennis: The above formula doesn't work because Excel doesn't like the range (D13:CU13) being used ...
|
| Conditional Formatting entire column uniquely | 3/19/2007 |
Q: I have 2 columns of date, both numbers. What I would like to do is set the conditional format with ...
A: Mark: This should solve your problem... 1) Select one of your cells that currently has the correct ...
|
| Pivot table page fields | 3/19/2007 |
Q: I am using a pivot table with 3 page fields. I was wondering if it is possible to filter lower level ...
A: Tom: I may need you to email me an example of what you want before and after to look like to ...
|
| Sorting Data | 3/15/2007 |
Q: thats almost what i need. Could you explain the formula in further detail? i also will need the ...
A: Johnny: Updated Spreadsheet: http://home.swbell.net/nate-sus/excel/vlookupnotebook.xls You ...
|
| Changing cell references | 3/15/2007 |
Q: Suppose in cell A1 I have the formula = B1*C1. If I copy the formula to cell F1 it becomes =G1*H1. ...
A: Based on your description it sounds like a "search and replace" should be able to accomplish what ...
|
| Sorting Data | 3/14/2007 |
Q: I made a tracking file using excel and i store different data on each of the pages. (for example on ...
A: Johnny: I believe the VLOOKUP formula may be what you are looking for. Can you check out this ...
|
| merging in excel | 3/14/2007 |
Q: I saw the following on your wed site and I think this will fix a problem that I have Could you tell ...
A: Emma: Here are the steps for removing duplicate items (you may need to adjust depending on your ...
|
| Static a formula cell | 3/14/2007 |
Q: Good day and I need your guide, STANDARD COPYS the formula ========================== Original ...
A: KT: Start with this formula in cell C1: =+A1+$B$1 Now when you copy this formula to the lower ...
|
| Excel - Conditional Formatting Using Formulas | 3/14/2007 |
Q: I've created a scheduling spreadsheet which lists my employees in the first column followed by their ...
A: This can be done with the conditional formatting feature of Excel. Select the cell you want to have ...
|
| Excel Cell Format Issue | 3/13/2007 |
Q: I have tables that were created from Equis. The numbers in the cells are text and left justified. ...
A: Scott: Yup, that happens sometimes when importing external data into Excel (or pasting from another ...
|
| Adding a % increase to a spreadsheet that contains absolute numbers only | 3/12/2007 |
Q: My worksheet currently contains absolute numbers only (raw data), no formulas. My company is ...
A: Yes, there is actually a very convenient way to hand this by following these steps: 1) Select a ...
|
| Dynamic referencing a range to sum within a datalist | 3/5/2007 |
Q: Nathan, I have a list with dates in Chrono order in 1st column and forecasted cash flow in other ...
A: Ron: I have uploaded a sample spreadsheet here: ...
|
| finding and moving same numbers in two rows | 3/5/2007 |
Q: I am trying to create an excel sheet for our inventory. I have a list of item numbers in a column. ...
A: Shannon: I have uploaded a sample spreadsheet here: ...
|
| how to sort rows with formulas | 3/1/2007 |
Q: I have a difficult issue that I am trying to resolve and since I am new at excel2003 I cannot find ...
A: I think the RANK() and INDEX() function might be what you are looking for. You would place this ...
|
| IF AND statements | 2/28/2007 |
Q: I would like to know how to write a formula to check a cell for two conditions and return one of two ...
A: I'm not certain I understand the 9="Store" and 10="Grocery"...unless you mean the 9th column ...
|
| Formula | 2/28/2007 |
Q: What is the formula for removing text from the Left of something? for example I have : 448885-20123 ...
A: Manny: Presuming the number you mention is in cell A1, you could use this formula: =RIGHT(A1,5) ...
|
| Subtracting Time | 2/27/2007 |
Q: You helped me with a spreadsheet a couple of weeks ago regarding subtracting times. My formulas work ...
A: Sure thing - go ahead and send it to Lotus@swbell.net and I'll take a look. It is likely a case ...
|
| Excel Drop-Down List | 2/26/2007 |
Q: I working on a spreadsheet that will log surgery encounters. To standardize data entry, I want to ...
A: Richelle: I generated a quick spreadsheet to demonstrate how to create multiple/linked drop-down ...
|
| Excel-Drop Downs | 2/25/2007 |
Q: I have a form that I am using for 10 different companies. There is a drop down for company, and a ...
A: Check out these functions: =CHOOSE() =INDEX() Suppose these are your companies: Apple Bear Cat ...
|
| formatting a cell | 2/15/2007 |
Q: but something is not working... Maybe it's me? The problem is: when I use your formulas on a clean ...
A: The segmetns of code, from the previous answer, must exist in all spreadsheets that use the flashing ...
|
| Extending Length of Data | 2/14/2007 |
Q: You did in fact it appears, answer the question I asked - but unfortunately I now realise it wasn't ...
A: Anthony: Please take a look at my sample sheet here: ...
|
| formatting a cell | 2/13/2007 |
Q: Is it possible to format a cell so it starts flashing when a value entered in it is "equal or ...
A: Jack: This can be done, but it requires a combination of conditional formatting and visual basic... ...
|
| Extending Length of Data | 2/12/2007 |
Q: I have a problem relating to extending a set of data. I'm sure there must be a way to do this in ...
A: Anthony: Before I answer I'm going to rehash my understanding of the question... Your original ...
|
| Lookup data in column A and B, then output all values in Column B for matching values in column A | 2/8/2007 |
Q: I am looking to do something similar to a VLOOKUP, only what I am looking up is NOT unique value in ...
A: Lisa: I have uploaded a sample solution here: http://home.swbell.net/nate-sus/excel/products.xls ...
|
| lookup? | 2/8/2007 |
Q: woah...now THAT is cool, it just sliced off like 8 hours of time...especially when I have like 1000 ...
A: Glad to hear that :). You should be able to copy and paste the final result into Word directly from ...
|
| If then formula - How can this be accomplished?? | 2/7/2007 |
Q: I believe I actually understand what is being asked in the below formula. however I have to last ...
A: Please refer to this spreadsheet to see the formulas in action. ...
|
| IF, Then Function | 2/7/2007 |
Q: I need help with creating an If, Then Function on work sheet 2 that will analyze a column row by row ...
A: I think I have a good idea of what you are looking for. Can you review this spreadsheet and see if ...
|
| If then formula - How can this be accomplished?? | 2/7/2007 |
Q: I'm working on setting up a spreadsheet that will help automate several positions in the company to ...
A: I think I have a good idea of what you are looking for. Can you review this spreadsheet and see if ...
|
| vlook up formula question | 2/6/2007 |
Q: I frequently use the following vlook up formula: ...
A: Please bear with me while I walk through your formula: ...
|
| Excel flow through sheet | 2/5/2007 |
Q: Ok now I am trying to insert some blank rows above my data on say the Ashgrove tab, when I do I ...
A: Don: The formula I provided uses the actual row number to determine what row to pull information ...
|
| Excel flow through sheet | 2/5/2007 |
Q: this is what I am looking for. So to enter this type of formula I press CTL-SHIFT-ENTER Now how can ...
A: To enter or change an existing array formula, you will want to press CTRL-SHIFT-ENTER instead of ...
|
| lookup on horiz and vert criteria | 2/3/2007 |
Q: I've created a spreadsheet that generates price escalation factors for past and future years. I ...
A: I would like to answer this question with a link to a sample spreadsheet, but I'm out of town at the ...
|
| if statement | 2/3/2007 |
Q: =IF(A14=" Please remove", Sheet5!A14,A14), this is my formula. it means that if A14 on working sheet ...
A: Sue: The below formula is probably the simplest solution: =IF(IF(A14=" Please remove", ...
|
| Excel flow through sheet | 2/2/2007 |
Q: I already posted a follow up question and forgot to add that some of the cells on the raw data that ...
A: I believe the sample I provided in the first response will not have any issues with transferring ...
|
| Extracting Date Data | 2/2/2007 |
Q: can you help with this small problem. In Col A I have some dates in the format DD/MM/YYYY, but also ...
A: Excel doesn't have a built-in function such as ISDATE, but I can think of two ways to do this ...
|
| Excel flow through sheet | 2/2/2007 |
Q: this is close to what I am looking for. here is a copy of my raw data sheet. Source 7 Day ...
A: Note #1 - It will go down to infinity From row 1 to row ?????? In terms of Rows, Excel has a finite ...
|
| working with large data and lists | 2/2/2007 |
Q: Believe it or not same project. I am working with a list of data. Basically I want to: 1. Show ...
A: I'm not 100% sure I will be providing the answer you are looking for; nevertheless, I will give it a ...
|
| Excel flow through sheet | 2/1/2007 |
Q: I am creating a series of sheets and would like to put raw data on the first one and then on the ...
A: Don: I have read your question a few times, but I'm not certain I understand. Nevertheless, I have ...
|
| counting string cells in a table | 1/31/2007 |
Q: How can I get a count rows of unique strings (not within a string)in a one column array? 123 123 ...
A: David: There are a couple of ways to handle this, but I believe the following is one of the better ...
|
| lookup? | 1/31/2007 |
Q: I can't figure out for the life of me how to do this. It seems like it would be fairly easy. I ...
A: Phil: I would need to see the exact input in order to provide an exact solution; however, you can ...
|
| Look-Up formula | 1/29/2007 |
Q: I was wondering whether you would be able to help me improve the usability of my spreadsheet. I ...
A: Darren: VLOOKUP sounds like the perfect solution to your question. I have uploaded an example ...
|
| searching a column range multiple criteria | 1/27/2007 |
Q: I am working on a project in excel and I want to basically do a countif function looking for a ...
A: Give this formula a try and let me know if it works for you: ...
|
| Excel Formula | 1/26/2007 |
Q: I am trying to create a formula which will look at a column for a specific number and then for all ...
A: ). Please refer to the spreadsheet I have upload to the following URL: ...
|
| Pivot table | 1/26/2007 |
Q: I have a pivot table that I need to "unpivot" i.e. see the raw data, which have been deleted, ...
A: Lena: Presuming Excel is still storing the information for your pivot table in "memory", then you ...
|
| excel freeze | 1/25/2007 |
Q: have a quick question with excel. Driving me nuts. I am trying to freeze row 6 and column A. so ...
A: By defintion of the freeze pane option, ONLY the data existing both in the column to the left of the ...
|
| Combo Boxes | 1/22/2007 |
Q: I am currently trying to develop an electronic purchase order form using a spreadsheet from excel. I ...
A: I uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/ifthencombo.xls The ...
|
| Pivot Table - Problems when using Calculated Item | 1/20/2007 |
Q: The problem is, when i use the calculated item option, it ignores my PAGE FIELD and returns ALL ...
A: Good morning Sandi! Please review this spreadsheet: ...
|
| Maxa - return a higher specific number | 1/19/2007 |
Q: I am stumped. I need to return the higher number between numbers and it is not working. I am using ...
A: Dennie: I have uploaded a sample spreadsheet here: ...
|
| look up formula | 1/17/2007 |
Q: I have 1 sheet labeled "CHECK STATS" and B7 cell is "BAS", and another sheet "FINAL AMOSS". I need a ...
A: Does this formula work for you? =VLOOKUP('CHECK STATS'!B7,'FINAL AMOSS'!A7:F50,6,FALSE) It takes ...
|
| remove spaces between text | 12/29/2006 |
Q: I'm sure this question is fairly simple but I'm new to the advanced features of excel I can't do ...
A: Good morning!! There are actually two ways to do this...depending on what you are looking for: 1) ...
|
| index/match formula | 12/29/2006 |
Q: Sorry last question. Let's say I have a spreadsheet, in column A there is a bunch of names and in ...
A: I may need some additional information on this question...but here is what I can think of thus far. ...
|
| index/match formula | 12/28/2006 |
Q: One more question if I could. What if I were to have the same situation as before but my formula is ...
A: No problem - the same basic formula would still apply: =IF(ISNA(original formula),0,original ...
|
| index/match formula | 12/27/2006 |
Q: I have created an index/match formula to pull stock prices from one spreadsheet to another. How can ...
A: You can follow this basic formula to insert a zero when there is an #N/A: =IF(ISNA(original ...
|
| Rows to Colums | 12/27/2006 |
Q: I have data in a spreadsheet in one column like this: 11/19/2006 15.26 10029 11/19/2006 10 48215 ...
A: I believe I have a solution that will work perfectly (and it is fairly simply despite there being 10 ...
|
| SUM.IF whith a month as criteria | 12/27/2006 |
Q: I am sure the question I am about to ask is a very easy one, but I just can't get it right. I have ...
A: Give this formula a shot and let me know if it works for you: ...
|
| Excel IF Or Formulas with time? | 12/26/2006 |
Q: Okay can't get to into it I've got about 25 formulas on this sheet all dealing with schedules and ...
A: I suspect you may be using something a little different than Excel. Excel formulas do not end with ...
|
| Excel Help | 12/26/2006 |
Q: I am trying to compare 2 columns (A&B) of data that I have to see what is not repeated. I need to ...
A: Presuming the above data is in cells A1 through B10, then place this formula in cell C1 and copy it ...
|
| Comparison of Data | 12/21/2006 |
Q: I am ranking about 100 cells. These cells are percentages. Here is a copy of the formula ...
A: I set up a table with about a 100 percentages and the rank formula, as you listed above, seems to ...
|
| Comparison of data | 12/20/2006 |
Q: There will be 5 values in cells AI6:AI10 The values are 1000 8000 3000 2000 1000 What i want is as ...
A: First I would like to point out that your starting values: 1000 8000 3000 2000 1000 Are not the ...
|
| Excel Formulas | 12/18/2006 |
Q: I am using the formula =INDIRECT(D3&"!B11") to extract information from worksheet to another. ...
A: This formula appears to create an indirect reference to a cell on another workshet. The "D3" ...
|
| SIMPLE QUESTION | 12/17/2006 |
Q: I HAVE A FORMULA THAT IS BASICALLY A SUM. THE FORMULA REFERS TO THE CELL THAT IS BEFORE IT.IF I HAVE ...
A: There are lots of ways to hande this depending on the type and variety of data...as detailed on this ...
|
| Linking sheets in Excel | 12/16/2006 |
Q: Example: I have a workbook with 6 sheets, one being the "master" sheet. It's a list of action ...
A: Linking in Excel is fairly user friendly and can be done with the following steps: 1) Open your ...
|
| Pivot tables from scenarios | 11/28/2006 |
Q: I receive a spreadsheet from some one else that includes all of the following parts. They have ...
A: Unfortunately, by design, Excel doesn't allow changes/refreshes to a Pivot Table created from ...
|
| Formula | 11/10/2006 |
Q: About a month ago i asked for your help on a formula i needed to work between two databases with ...
A: John: Can you send me a copy of the formula you are currently using? I browsed through my previous ...
|
| excel bell curve | 11/8/2006 |
Q: This is definitely close to what I was looking for, but not quite. What I want is for those ...
A: Can you check out this website? http://home.swbell.net/nate-sus/excel/bellcurve.xls I have made ...
|
| excel question | 11/8/2006 |
Q: I have two columns (A, B) and if the name in Column A is Daniel, for example, then I want the ...
A: Daniel: You are correct; conditional formatting is the way to go. Starting with cell B1, open the ...
|
| excel bell curve | 11/7/2006 |
Q: I was wondering if it is possible to take existing data and turn it into normalized data. Monetary ...
A: I'm not 100% sure what you mean by "normalized", but it sounds like you have something like this: ...
|
| Excel formula's | 11/6/2006 |
Q: I have a day that begins on say friday's at 6am to 6pm = Regular Time 6pm to midnight = Weekday ...
A: Your original question made no mention of a start time and end time. The formula I sent you just ...
|
| Excel formula's | 11/6/2006 |
Q: I have a day that begins on say friday's at 6am to 6pm = Regular Time 6pm to midnight = Weekday ...
A: John: I have uploaded a spreadsheet here: http://home.swbell.net/nate-sus/excel/timesjohn.xls This ...
|
| Complex Formula | 11/5/2006 |
Q: however can pick up pretty quick. I would like to review two rows B38:BG39 to determine if it has ...
A: Presumptions: BG1 through BG8 contains the letters MTPVBROS...M is located in BG1; T is located in ...
|
| quotes in cell | 11/3/2006 |
Q: it gave me an error about circular refences, what did i do wrong?i have a report that i have to ...
A: I suspect you attempted to place the formula in cell A1, which would cause a circular reference ...
|
| quotes in cell | 11/3/2006 |
Q: i have a report that i have to import into my database and it is csv, it says the proper import is ...
A: Presuming the above example is in Cell A1, this formula would work: =CONCATENATE("""",A1,"""") You ...
|
| formular for worksheet name or workbook name | 11/3/2006 |
Q: I am creating an application where the name of each worksheet tab will be meaningful. Can I have a ...
A: The CELL formula is the way to go, but you just have to use some other formulas with it in order to ...
|
| Daily Compound interest formula | 11/3/2006 |
Q: After putting heads together and the ACCRINT function is doing it how we want. What we need is a ...
A: I just realized I never mailed you the formula I used to calculate the amount I show should be the ...
|
| Daily Compound interest formula | 11/3/2006 |
Q: I need a formula for calculating a monthly payment on in an excel amortization schedule. The ...
A: This is similar to calculating the yield on a bond...ie, you want the interest calculated on a daily ...
|
| Function/Formula/Macro (Extract Names and paste to a new sheet) | 11/2/2006 |
Q: Greetings, Looking for a solution for a worksheet where a formula, funtion, or macro is created ...
A: Okay...based on my interpretation it appears you have two lists. List A contains a bunch of names. ...
|
| formula question | 11/2/2006 |
Q: I have a question regarding a formula. Basically, I have two worksheets. Worksheet A has a list ...
A: I believe SUMIF is what you are looking for. The following is a sample formula that you may need to ...
|
| Drop Down List in Excel | 10/31/2006 |
Q: How do I print a drop down list in Excel 2003?
A: I'm going to start out by saying the easiest way to print the box and then go into some other ...
|
| Excel | 10/28/2006 |
Q: Can you hide certain colums so that only certain people can view information?
A: Laura: Yes, specific colors (or rows) along with entire pages/sheets can be hidden from view. 1) ...
|
| Excel Charts - grayscale | 10/27/2006 |
Q: Is there a way to have the color choices for charts a grayscale or using various textures, etc., for ...
A: Rosa: There are a couple of options available: 1) When you create a new chart via the chart ...
|
| Filter | 10/26/2006 |
Q: I have a large sheet of data that I have put filters on, and I would like to have a Total line at ...
A: Jon: I presume you are using auto-filter. When you add your total line, be sure to leave a blank ...
|
| need help with graphs | 10/25/2006 |
Q: Nathan, Please help me with my chart. My sample budget data is as follows: Actuals ...
A: Roomi: Question 1: A lot of charting is personal preference and what you are looking for in the end ...
|
| FORMULA | 10/23/2006 |
Q: SORRY NATHAN, A COMMUNICATION BREAKDOWN, MY FAULT, I SHOULD HAVE TYPED (ANY WAY "AROUND" THIS) NOT ...
A: The only way to "fix" this formula is to fix the other problem formulas in the spreadsheet. Having ...
|
| FORMULA | 10/23/2006 |
Q: YOU,RE RIGHT NATHAN COLUMN "E" HAD THE "N/A" VALUES DUE TO AN ARRAY FORMULA I INSERTED, DONT KNOW ...
A: Kewl beans! Glad to hear the formula is working now :). The best way to round is to use the ...
|
| formula | 10/20/2006 |
Q: I have a spread sheet with multiple taps. I am attempting to write a formula to have the cells sum ...
A: To sum an entire row, you can use a formula such as this: =SUM(10:10) The above will sum all ...
|
| SumProduct Formula | 10/19/2006 |
Q: I have the following formula... ...
A: You have asked this before...although you appear to be updated your question with the information ...
|
| SUMPRODUCT formula | 10/19/2006 |
Q: It's returing a value of zero. But there are numbers for it to sum up. Any advice? I changed all ...
A: If it is returning zero, then it is probably one of the following: 1) Since you are using the ...
|
| SUMPRODUCT formula | 10/19/2006 |
Q: It returns this "#VALUE!".I have the following formula... ...
A: If it returns #VALUE, then that is probably because the items in column G are text instead of true ...
|
| SUMPRODUCT formula | 10/19/2006 |
Q: I have the following formula... ...
A: Arthur: Let me know if this works for you: ...
|
| shared file issue | 10/18/2006 |
Q: Nathan, the problem is that the excel feature is wrought with problems and has tons of glitches. I ...
A: Okay, you have two options: 1) Use the feature Excel has built-in (that is all Excel offers for what ...
|
| shared file issue | 10/18/2006 |
Q: Gyula, I asked a similar question yesterday regarding shared files and what i got back was that the ...
A: Lee: 1) Click on tools->share workbook 2) Put a check in Allow changes by more than one user at the ...
|
| formula | 10/18/2006 |
Q: NATHAN SORRY NATHAN PLEASE DISREGARD THAT LAST E-MAIL. AS I SAID, THE FORMULA YOU GAVE ME WORKS ...
A: John: The formula, =SUMPRODUCT((E1:E1000="New York")*(M1:M1000<>"")) Should work even if there ...
|
| IF + COUNT + OR | 10/17/2006 |
Q: I have a list in column B. I am currently using the following formula (trying to define which client ...
A: Your question is, "I wish to count and find the word NEW in column B or in column B in the other ...
|
| filter and data protection | 10/16/2006 |
Q: Nathan, I'm using Excel 2000. I have checked for your box and it is not there. I'm therefore ...
A: Yup...I'm using 2002. So, beyond upgrading, here is what I found on the net for earlier versions of ...
|
| filter and data protection | 10/16/2006 |
Q: I would like to share an excel spreadsheet on our Business intranet. In order to prevent any ...
A: Daniel: Are you using the Excel Auto-Filter? If so, then there is an option when you protect a ...
|
| formula | 10/16/2006 |
Q: I NEED A FORMULA THAT WORKS BETWEEN TWO DATABASES (ANALYSIS & LOG)IN THE SAME WORKBOOK. USING ...
A: John: It sounds like you want the following: IF "value in column F" = "City Name Specified" AND ...
|
| Joining data in Excel | 10/13/2006 |
Q: I have multiple worksheets that have 1 piece of similar data that I need to consolidate into one ...
A: Julia: Your best bet is to use the VLOOKUP formula. You can use VLOOKUP to index the customer ...
|
| Pivot table | 10/12/2006 |
Q: I have a pivot table that has part numbers in the first column and the customer for that part number ...
A: You are asking about my #1 complaint of Pivot Tables :). There is no way, with the pivot table ...
|
| Formatting Multiple Workbooks (Files) all at once | 10/11/2006 |
Q: I wonder if there is any way to do the following, I have ten Excel files let say book1, book2, ...
A: There is one method I can think of that fits your situation (separate workbooks; not in the same ...
|
| excel question | 10/9/2006 |
Q: I have data in 2 worksheets. Worksheet A contains names and registration dates, Worksheet B ...
A: Daniel: Give this formula a shot and let me know if it works for you. You will need to change the ...
|
| Excel, iteration & filters | 10/7/2006 |
Q: Many thanks for your speedy response. The formula works beautifully. If it is not too troublesome I ...
A: You provided two formulas that you wanted merged: =If(A1>(B1+0.04),B1,A1) – I will call this ...
|
| Excel2000-Formulas | 10/7/2006 |
Q: R12 to R18 is where the formula is inserted. They referance the cells in N2 to N8. R12 references ...
A: Please email the spreadsheet to Lotus@swbell.net. In addition, might I inquire what the question ...
|
| formula | 10/6/2006 |
Q: Nathan: It worked vary nicely, except: I use this array formula to calculate the average for each ...
A: Rodney: Would the following work on your formula that takes an average of the 6 periods?: ...
|
| formula | 10/6/2006 |
Q: Nathan: Sorry to ask another question, but... I was using your formula and instead of the value ...
A: If there are no valid periods or subjects, the formula ends up taking an average of (FALSE, FALSE, ...
|
| formula | 10/5/2006 |
Q: Nathan: Excellent! That is exactly what I wanted...the right way to do it instead of the patchwork ...
A: Excellant! It appears you understand the formula perfectly. The only side note I will add is that ...
|
| formula | 10/5/2006 |
Q: Nathan: I used the formula you gave me with some modification. My version: ...
A: Not confused yet :). Give this a shot... The below formula is an array formula. It must be ...
|
| jmlinscott@yahoo.com | 10/5/2006 |
Q: When opening an excel file, it will come up unable to read file. I click ok, & then it brings a ...
A: Joann: This means there is a flaw somewhere in the Excel file. Typically it isn't something you or ...
|
| Emailing excel changes page setup? | 10/5/2006 |
Q: I hope you can help. I frequently need to email Excel 97 spreadsheets that I have formatted to be ...
A: Mel: The quick answer is no, you do not appear to be doing anything incorrectly and instead, there ...
|
| if & nesting | 10/3/2006 |
Q: Followup To Question - sorry my Question may be easy but i tryed to get the answer and did not ...
A: I have uploaded a sample spreadsheet, with the above two sample formulas, to the following location: ...
|
| converting to number format | 10/2/2006 |
Q: Nathan I am working with the Master Builder accounting program where I have the ability to export ...
A: Here is my first idea: 1) Select the entire spreadsheet (or the items you want to reformat) and ...
|
| if & nesting | 10/2/2006 |
Q: sorry my Question may be easy but i tryed to get the answer and did not find the answer in a way i ...
A: You asked how would the suggested formula work with words that you want to compare. Here is an ...
|
| if & nesting | 9/30/2006 |
Q: sorry my Question may be easy but i tryed to get the answer and did not find the answer in a way i ...
A: You can "nest" up to 8 IF functions together. As an example I am going to use letter grades on a ...
|
| Merging Excell Workbooks | 9/29/2006 |
Q: Is there a way to merge two workbooks which don' t necessarily have exactly the same rows. We have ...
A: Hey Ray...I'm going to discuss several items here...they may be considered steps (except for #1). ...
|
| Excel - vlookup | 9/29/2006 |
Q: Sometimes when I enter a vlookup formula, the formula does not calculate, the cell displays the ...
A: Jason: The "most common" cause of this issue is when the cell has somehow been changed to a text ...
|
| Advanced Excel 03 Question | 9/29/2006 |
Q: So I can reference 12 different worksheets with this and it will show only the information that ...
A: So I can reference 12 different worksheets with this and it will show only the information that ...
|
| Advanced Excel 03 Question | 9/28/2006 |
Q: I am making a bill payment workbook. I have 12 worksheets, one for each month of the year. I also ...
A: Chris: I think this question is best explained with an example. As such, I have uploaded a sample ...
|
| Advanced formula | 9/28/2006 |
Q: ------------------------- Nathan, No wonder I couldn't figure out the formula; I know now that ...
A: My applogies for the slow reply...I thought I had answered this but I guess I forgot to click ...
|
| Creating mutiple worksheets based on data from another workbook | 9/28/2006 |
Q: I am working on a hurricane relief project in New Orleans and here's what I have... I have a ...
A: I may be misunderstanding the question, but this almost sounds like you want to do the equivalent of ...
|
| Advanced formula | 9/28/2006 |
Q: I am stuck on a formula/arguement on a spreadsheet I have created to track stock purchases. ...
A: Patrick: Try placing this formula in cell I5: ...
|
| Working With Dates in Excel 2003 Pivot Table | 9/27/2006 |
Q: Nathan, Thanks again for the prompt response. I think my second question needs to be clarified in ...
A: Sure, here is an example: Suppose "ProcessMovement" is in cell A1 and "LocalClient" is in cell B1. ...
|
| Working With Dates in Excel 2003 Pivot Table | 9/27/2006 |
Q: Nathan. You deserve a good rating based on such turn-around. I like the idea of subtracting dates ...
A: Well, you cannot add and subtract dates directly in the PivotTable, but you can create a new column ...
|
| Working With Dates in Excel 2003 Pivot Table | 9/27/2006 |
Q: I'm hoping you can help me answer how to find the answer to this question using a Excel 2003 pivot ...
A: Generally speaking you will always have numbers in the data field (since the data can only contain a ...
|
| Excel filter, advanced | 9/27/2006 |
Q: I noticed this school year in working with our data that even though a critera range is specified ...
A: Janice: Based on my interpretation of the question I can think of two ways to generate the list: ...
|
| formula | 9/26/2006 |
Q: It worked very nicely. I really appreciate the help and the spreadsheet you posted for me made it ...
A: I'm glad to hear it works for you! Here are some additional comments.. ... "(period=quarter)" and ...
|
| Auto-Complete /w Data Validation | 9/26/2006 |
Q: I want to set up a drop down list where the user can either scroll through the selection options or ...
A: FYI - Corrected my original answer - I had the letters on my sample references wrong... Data ...
|
| formula | 9/25/2006 |
Q: In row 1 have have entries of 1, 2, 3, or 4. In row 4 I have numbers 0-100. In row 5 I have ...
A: Rodney: Let me know if this formula works for you: ...
|
| excel dsum() | 9/22/2006 |
Q: The dsum formula in LOTUS can be written in one cell with the criteria defined in the forumla. Exell ...
A: You are correct, DSUM in Excel requires a criteria range. I did some research on this and found ...
|
| Excel lookup formula | 9/21/2006 |
Q: I'm trying to use a lookup formula and sometimes I get a "N/A" answer because it isn't found in the ...
A: Sure thing! Suppose this is your formula that is returning the N/A: ...
|
| SUMPRODUCT | 9/20/2006 |
Q: I have the current SUM Product Formula... =SUMPRODUCT((CB_Source!$C$1:$C$3000="Spot Direct ...
A: Two solutions: 1) The easy way is just to add the three options together: ...
|
| Sum a specific name using multiple columns and criteria | 9/19/2006 |
Q: I've been working on this for hours now and can't seem to figure out how to do the following. I ...
A: I believe this is the answer you are looking for: Sumproduct is a formula that you can use to ...
|
| Excel graphs | 9/18/2006 |
Q: It is a problem I have experienced many times when trying to use automated graphs within excel and I ...
A: My suggestion is to have three columns. In column A have your list of dates. In column B, do a ...
|
| Time Calculations | 9/17/2006 |
Q: Can you please advice me: In a spreadsheet i have: Start time (will be 00:00 for a 'given' date) ...
A: Ryan: I have uploaded a sample spreadsheet here: home.swbell.net/nate-sus/excel/ryantimes.xls For ...
|
| button or drop down list with color | 9/14/2006 |
Q: I am wanting to make it so when i click on a cell it turns the red. it can be through a botton or a ...
A: http://home.swbell.net/nate-sus/excel/colors2.xls The above spreadsheet contains an example of what ...
|
| Followup To: compare two columns in excel | 9/8/2006 |
Q: I am Dhaval Shah. I tried to use that formula but i think I am doing it in a wrong way. Here i have ...
A: To be honest, I'm not sure I understand the question. Nevertheless, I have placed an Excel file ...
|
| time | 9/8/2006 |
Q: it goes like this... column a time in column b time out then in column c is the total time consumed ...
A: Lhia: There may, of course, be a more elegant way to handle this, but this is the formula I came up ...
|
| Follow up: compare column in excel | 9/7/2006 |
Q: Let me send you the example in proper format. A B C D E F 1 A 1 A 1 C 1 B 1 B 1 C 2 X 3 A ...
A: Greetings! You are essentially wanting to do a lookup of A&B to see if it exists in C&D. The ...
|
| compare two columns in excel | 9/7/2006 |
Q: I am Dhaval Shah. I have in Total 6 column. Result ...
A: Greetings! You are essentially wanting to do a lookup of A&B to see if it exists in C&D. The ...
|
| Using OFFSET to refer to external worklbooks | 9/7/2006 |
Q: I am trying to find all matches of a project number that exists in a column of data on a sheet in an ...
A: Unfortunately, this is a limitation of offset. You can only reference an external workbook IF the ...
|
| Changing the dates in my spreadsheet | 9/7/2006 |
Q: I have just made an enormous spreadsheet to track the production in the factory where I work. I ...
A: Claire: The following should work; if it doesn't, please let me know and I will need to take a ...
|
| Generating a list from Vlookup | 9/6/2006 |
Q: Good morning Nate, I understand that Vlookup will give you the value of a column related to one ...
A: Antoine: The Short: Vlookup won't work very well for this situation. I suggest a combination of ...
|
| Rounding in Excel | 9/6/2006 |
Q: I am trying to round a number that is attached to a LOOKUP function... =LOOKUP(L4,'TAX ...
A: Just insert the following function around the above: ROUND(original function, 2) or ...
|
| Pivot Table - Multiple Sheets | 9/5/2006 |
Q: unfortunatley you don't appear to get the same level of functionality from the "consolidated" data - ...
A: Hmm...I use that particular type of Pivot Table to merge multiple lists for myself and although it ...
|
| Pivot Table - Multiple Sheets | 9/5/2006 |
Q: Nathan, I have multiple tabs(in a speparate work book that I would like to query using Pivot table ...
A: Allan: This can be done through the Pivot Table wizard. Here are the steps to get started on ...
|
| vlookup | 9/4/2006 |
Q: can we retrieve a complete row from a table by giving vlookup formula. e.g. from a table, if i want ...
A: I presume you already know how to use VLOOKUP to extract the first item in a row. Nevertheless, ...
|
| IF function | 9/3/2006 |
Q: When doing the IF function, what is the condition, what is the valve If true and what is the value ...
A: If works like the following: =IF(logical_test,value_if_true,value_if_false) This means, if you had ...
|
| how to display 0001234 in excel cell | 9/1/2006 |
Q: This is very small matter. when I try to type 0001234 number in cell and press enter, number ...
A: There sure is! 1) Select the cell in question. 2) Click on Format->Cells 3) Choose "Custom" 4) Type ...
|
| Seprate numbers | 9/1/2006 |
Q: I have 2 column. A B Result 1 2 ...
A: Give this formula a shot in column C: Place this one in cell C1 and copy down as far as you need ...
|
| Excel- series problem | 8/31/2006 |
Q: Let's say I have the value 1, 2, 3 in each cell in a column.To create series, I can select the cells ...
A: Start off with the following: A1: 1 A2: Blank A3: 2 A4: Blank A5: 3 A6: Blank Highlight and select ...
|
| VLOOKUP | 8/30/2006 |
Q: Good Day Nathan , Can you help me out with the below case . (eg)I have datas in sheet2 columns ...
A: I suggest using the VLOOKUP formula. Presuming you are entering the "lookup" values in cell A1 of ...
|
| Excel Formula, less than zero | 8/30/2006 |
Q: I have a formula as follows: ...
A: Duncan: Give this a shot and let me know if it works for you: ...
|
| SumIf and VLookup | 8/29/2006 |
Q: I'm trying to convert a monthly payment formula to a quarterly formula (I'm running out of columns), ...
A: From the top of my head, I would make sure you are using the PMT() function in Excel instead of ...
|
| Excel Formula_ Pivot Table | 8/29/2006 |
Q: I have a spreadsheet of about 20,00 rows and 4 columns. Column Headers are Customer Name, Contract ...
A: If I understand your question correctly, I believe the second XYZ example should have a different ...
|
| Cell References across spreadsheets | 8/29/2006 |
Q: I have 2 spreadsheets. File1 has a named range that refers to a grouping of 12 cells (one for each ...
A: Since you have created an array called MONTHS, you can index this array on any sheet in the same ...
|
| Overtime | 8/29/2006 |
Q: I've never seen the "NETWORKDAYS" function. One question I do have regarding your example sheet, ...
A: Yup, the spreadsheet would need tweaking to change it to a 40 hour week instead of 80 hours. I have ...
|
| Overtime | 8/28/2006 |
Q: Total Regular Hours Overtime Hours 20.00 20.00 21.00 0.00 0.00 0.00 0.00 0.00 0.00 ...
A: I have a sample spreadsheet for overtime at this address: ...
|
| Address retrieval | 8/28/2006 |
Q: Say I perform a vlookup and the answer is returned in cell D4, how would I go about having the ...
A: There are two ways I can think of to do what you request. I will present them both and you can ...
|
| =if not working until I hit F2 | 8/28/2006 |
Q: I am comparing 2 ranges of data that each have over 7,000 rows. One range I am pulling in using a ...
A: It sounds like you are having problems with Excel itself which requires troubleshooting/memory ...
|
| Subtotals twice in excel ? | 8/28/2006 |
Q: As far as I can see only one subtotal in excel can be inserted. Is it possible to add a second ...
A: Good morning! I just wanted to clarify the1) Are you looking to have a subtotal that is the total ...
|
| List numbers | 8/28/2006 |
Q: My name is Ivan - I am a student from russia. I have a book file in excel, which consists of about ...
A: The easiest way to generate list numbers in Excel is to put the starting number into the first cell ...
|
| Re reply to my Excel problem with explorer sidebars appearing in folders called from Excel | 8/27/2006 |
Q: I have replicated what you did and certainly calling a folder from the desktop does work. However, I ...
A: It will call the sidebar on the new technique I emailed you, but the only item available in the ...
|
| Excel will only call folders with explorer side bar | 8/27/2006 |
Q: I have a real problem with a spreadsheet I created for a conference session presentation entry ...
A: Okay...here is my first "solution": I created a folder on my desktop called "Nathan". I then ...
|
| calculating overtime | 8/25/2006 |
Q: I am trying to figure out how to calculate overtime - Can you please show me how to set the formulas ...
A: I have uploaded a sample spreadsheet here: http://home.swbell.nate/nate-sus/excel/overtime.xls The ...
|
| DIV0! error | 8/25/2006 |
Q: I have created a conditional sum using the wizard, which is measuring up to 4 criteria in a table. ...
A: You are likely getting #DIV/0! because your formula is dividing by zero. Without knowing what your ...
|
| Multiple tabs - formulas not updating | 8/24/2006 |
Q: I am working in a workbook with many tabs (around 275 or so)...basically all the tabs flow into a ...
A: Ideas…in no particular order: 1) You have a circular reference somewhere. Excel will tell you if ...
|
| Random Index | 8/24/2006 |
Q: When you do a random formula to select a certain amount of names from say 100 it only displays one ...
A: I presume Column A has similar info in rows 1 through 100: Nathan Susan Gary Bill Henry I presume ...
|
| Graph Printing Problem | 8/23/2006 |
Q: I have created a chart using logarithmic scales and data series that form the y and x axes. I am ...
A: I have three ideas at the immediate moment; however, this might be one of those situations where I ...
|
| data validation not working properly | 8/23/2006 |
Q: I am working on Excel 2004 on a mac and I am trying to get a timesheet for my company to work ...
A: After reviewing your problem I suspect the problem is something specific to your spreadsheet (aka, I ...
|
| Excel Password Protection | 8/22/2006 |
Q: Is it possible to password protect a single page in a worksheet. I'd like to have a worksheet for ...
A: There is probably an elegant way to do what you need with Visual Basic programming, but I know very ...
|
| Export to XL from PDF | 8/21/2006 |
Q: I am tyring to export data from PDF file to XL , can this be done ? i have tried it but i was not ...
A: Generally speaking, PDF was specifically designed so that you could not convert PDF to any other ...
|
| Appostrophe S&R | 8/20/2006 |
Q: I have two worksheets, one imported into excel. I am trying to do a Vlookup on Names in a column. ...
A: Find and Replace is how you will want to get rid of the apostrophes. The only reason I can think as ...
|
| Formula to Lock a Row/Column | 8/19/2006 |
Q: I just have 2 questions before you . 1 )Is there any formula to lock a particular row/column/cell ...
A: Good morning Ed! 1) I'll start out by saying you can lock cells, rows, columns, and an entire sheet ...
|
| ms excel help needed | 8/18/2006 |
Q: i made a budget with excel, and it works great for my wife and me. however, at the beginning of ...
A: I'll have to be honest; I really don't understand the question. Perhaps it would be better if you ...
|
| formatting for currency changes | 8/17/2006 |
Q: I have a spreadsheet where one cell has a drop down list of different currencies. Another cell ...
A: I have uploaded a sample spreadsheet to the following location: ...
|
| Excel text | 8/17/2006 |
Q: This is kind of hard to explain but I will give it a go. If I have an worksheet and I take row 1, ...
A: I think I know what you want - you would like the text to shift around so that it is always ...
|
| Count between times | 8/17/2006 |
Q: Rather than entering the "times between" into the formula, how can I refer to a cell for the start ...
A: Rather than entering the "times between" into the formula, how can I refer to a cell for the start ...
|
| Count between times | 8/17/2006 |
Q: I have a list of cells with times, formatted 01:00, 01:32, 02:45, etc.. What I want to do is count ...
A: The best way to do the above is to count the number of times less than 5:45 and then subtract the ...
|
| AVERAGEIF() function | 8/16/2006 |
Q: I am averaging data that is arrayed in columns and I would like to experiment with the outcome by ...
A: There isn't a perfect solution I can think of that fits your description; however, I will offer the ...
|
| More than 8 if's | 8/16/2006 |
Q: I have 12 of these below, how should i create this? VLookup didn't seem to work ...
A: You are correct; VLOOKUP will not work in this situation since you are using Horizontal values. In ...
|
| Using LOOKUP function to return date | 8/16/2006 |
Q: I have a spreadsheet detailing company sales with hundreds of rows and several columns. The columns ...
A: If a cell is not formatted as a date, then it will appear as a number - as such, what appears to be ...
|
| I've been workin' on the Railroad | 8/15/2006 |
Q: I work for a steel company that has an order tracking program that was written in DOS when I was ...
A: I think Excel would be ideal for your situation. The only downside is there would probably be quite ...
|
| Sort Cells by Type | 8/14/2006 |
Q: I have a chart on a master sheet and it is made up of a lot of items but they can be grouped into 17 ...
A: This sounds like something that could be done with an indexing formula - for example, VLOOKUP or ...
|
| Excel Time Spend on Task | 8/14/2006 |
Q: I need to calculate the time spend on a particular task. The time duration needs to take into ...
A: Good morning! I have a spreadsheet for you to look at: ...
|
| suppressing blank rows | 8/14/2006 |
Q: I work with excel sheet with a lot of data in these sheets. The layout of the sheets is as such.... ...
A: Filters are one of the easiest methods of doing this. You would need to add a sum in column Q that ...
|
| Excel File not calculating automatically | 8/14/2006 |
Q: I'm running Excel 200 SP3 and run a 15 MB financial file with a large number of calculations from ...
A: Is it possible for you to zip the file (compressed it should be just a few MB) and email it to me at ...
|
| Making Excel handle a larger file size | 8/13/2006 |
Q: I am using Excel v.X on a 2GHz G5 iMac with 1G RAM. However, if I check using INFO("memavail") I see ...
A: The following website contains a LOT of useful information on memory problems with Excel: ...
|
| Link Two Drop Down Lists | 8/12/2006 |
Q: I notice that beside the "Cell Link", there is another "Input Range" in the format control. With ...
A: The Input Range should be filled in with the list of valid selections. For drop-down box one that ...
|
| Time Log | 8/12/2006 |
Q: yes Nathan you are right ,but these user dont have the prevailage to change the time (System setting ...
A: I have uploaded a sample file to this location: http://home.swbell.net/nate-sus/excel/timesheet.xls ...
|
| Time Log | 8/12/2006 |
Q: I don't have much expertise in excel and i wanted to know how to perform the below task in excel . ...
A: Before I spend any time on this, I just wanted to point out one thing: Excel bases its time on the ...
|
| Adjusting range in formulas to accomodate new data | 8/11/2006 |
Q: I have a spreadsheet with a master list of data, and a secondary spreadsheet with columns,organized ...
A: You may want to send a copy of your spreadsheet to Lotus@swbell.net. If I look at the actual data I ...
|
| Link Two Drop Down Lists | 8/11/2006 |
Q: I have created two drop down lists named as Country and Currency in Excel. Is there a way I can ...
A: The best way to do the above is to assign both drop-down boxes the same link cell. You change the ...
|
| combine lines for indexing | 8/11/2006 |
Q: I have an excel sheet which has a student number as the primary connection with a database I am ...
A: I can think of a couple of ways to do what you need; however, I believe the easiest method is a ...
|
| Reference Chart series dynamically | 8/10/2006 |
Q: I have lots of charts. Each chart needs to get data from different sheets. I would like a way to use ...
A: John: This may not be what you are looking for, but can you review this spreadsheet?: ...
|
| Pivot Tables with data greater than max lines in worksheet | 8/10/2006 |
Q: I have data that I add to monthly to calculate a year to date calculation for prices. I use pivot ...
A: Good news, there is a way to use multiple columns (or spreadsheets) of data! I'm going to truncate ...
|
| Bonus Calculation | 8/10/2006 |
Q: Example if the financial result was $32.46, and the bonus payouts are 25%= $31.00, 50% = $32.00, 75% ...
A: http://home.swbell.net/nate-sus/excel/profit.xls I have uploaded a sample spreadsheet to the above ...
|
| Bonus Calculation | 8/10/2006 |
Q: Trying to write a formula that will take an actual financial result and compare it to % payout based ...
A: Hmm...I am slightly confused by the question. You say you want to take an actual financial result ...
|
| Excel formula - SUMIF | 8/9/2006 |
Q: I do need more assistance. I stink at complex formulas. Is it possible for me to send you an ...
A: Sure - You may email to Lotus@swbell.net Your best bet is to do the following: 1) Email me the ...
|
| Excel formula - SUMIF | 8/9/2006 |
Q: I am working on a project where I need to pull information from one report to another. Here is a ...
A: More than likely you will want to use the SUMIF formula. Sumif works like this: =SUMIF(range of ...
|
| pvalue | 8/9/2006 |
Q: I'm in the middle of doing a data analysis, but i got stock in how to calculate the Pvalue. my ...
A: PValue is a statistical value…refer to this Wikipedia entry for an explanation: ...
|
| Formula Question w/in SUMPRODUCT | 8/8/2006 |
Q: and the point of the "+" is to just add things? I know that it isn't just a "negative of a ...
A: Yes, a "+" is just for addition. The above formula is taking the positive total number of records ...
|
| Area under a graph | 8/4/2006 |
Q: I'm producing a curve showing the effect of cashflow over time, and want to use the area under the ...
A: From your description it sounds like what you are looking for isn't a standard label Excel includes. ...
|
| RE: Two questions | 8/3/2006 |
Q: > Custom colors are stored with each workbook. When you make changes and save your workbook, those ...
A: I can think of a yuckier way - add a Control->Label (Text Box). You can use more than 255 ...
|
| Two questions | 8/3/2006 |
Q: Nathan, I have two questions for you. 1) I cannot seem to get Excel to remember a custom color that ...
A: Question #1) Custom colors are stored with each workbook. When you make changes and save your ...
|
| List box or combo box allowing user to select fill color. | 8/3/2006 |
Q: I'm creating a form for work and my target audience would like to have a list box or combo box that ...
A: Here is what I came up with: http://home.swbell.net/nate-sus/excel/colors.xls NOTE - Excel does ...
|
| Area under a graph | 8/3/2006 |
Q: Excel 2002. Is there any way that I can use Excel to calculate the area under a chart? I'm talking ...
A: Can you email me a sample spreadsheet to Lotus@swbell.net? Excel can display a variety of labels ...
|
| Days in period | 8/2/2006 |
Q: The DATEDIF function is not exactly what i am looking for. Say I have an activity that has a start ...
A: Presume A1 contains this: 8/2/2006 Presume A2 contains this: 8/20/2006 Presume B1 contains this: ...
|
| Conditional Editing | 8/2/2006 |
Q: My name is Richard and I need to be able to have a worksheet prevent the user from entering any data ...
A: I think I figured out a way to do this: 1) Add the following code to "sheet1" using the visual ...
|
| Displaying numerical result & text in same cell | 8/2/2006 |
Q: I have a ratio formula that I would like to display the result in number and text. Example: If the ...
A: Excel has a fraction format (format->cells->fraction), but it does not have a format for ratios. ...
|
| Date Ranges in Excel | 8/1/2006 |
Q: I copied a lot of data from wordperfect into an excel spreadsheet. I'm having 2 formatting problems ...
A: Please send the spreadsheet to Lotus@swbell.net and I'll make the below changes (or you can try ...
|
| Double-lookup VLOOKUP | 8/1/2006 |
Q: I am looking for a formula that will go to a column of data on a sheet, check that data, go to a ...
A: I have a spreadsheet that demonstrates three different ways to do double-lookups. ...
|
| Days in period | 8/1/2006 |
Q: I want to get total days between a start date and an end date. I tried to use the "NETWORKDAYS" ...
A: I want to address this first since it may answer your=DATEDIF(A1,A2,"d") Depending on your version ...
|
| Extract Value from Different Worksheets/INDIRECT | 7/29/2006 |
Q: My worksheet 1 is a summary of monthly expenses with the following format: Column A is a list of ...
A: Presuming your spreadsheets are also named identical to the labels in Column A (ie, the spreadsheets ...
|
| Reverse concatenate | 7/27/2006 |
Q: I have a very long list in Excel with first name, last name and title in one column. The title is ...
A: I presume a sample Cell, A1, contains the following data: First Last,Title Two ways to do it... ...
|
| Excel chart to track kids walking to points of interest | 7/27/2006 |
Q: Row 1 would be a list of names of students in a class (20-30 students) Column A would be the school ...
A: This can be done a variety of ways; some more complicated than others. For our first shot I'll try ...
|
| Statistics in excel | 7/27/2006 |
Q: I have a table in excel of survey question for an organization, 360 responses are processed. It is ...
A: First I would like to say that I remember very little from Statistics - That being said, Excel has a ...
|
| SUM/COUNT equation needed | 7/27/2006 |
Q: Alright. I have a range of cells (A1 through A20) that will have peopels names and are adjacent to ...
A: I speculate since we are working with names/text that you are actually needing a COUNT instead of a ...
|
| vlookup0 | 7/27/2006 |
Q: .. How do I ensure that the LOOKUP VALUE is in the same FORMAT on both sheets? Seems that it ...
A: I'm certain I do not understand the problem. Vlookup doesn't copy formats, it only copies a value. ...
|
| UNIQUE DATA IN A COLUMN OF CELLS | 7/25/2006 |
Q: I have an Excel 2000 spreadsheet that I've been using for some time now. It has columns of data ...
A: Vickie: There might be a way to use visual basic to program something to where it will only let you ...
|
| macro to calculate Decimal Deg | 7/18/2006 |
Q: I have the data in the column C,D is in the following format. lat lon ...
A: It sounds like you are describing a Visual Basic Program/Macro, and as per my Expert description, ...
|
| SUMMING value between date range (year to date) | 7/15/2006 |
Q: I am sure at your level of expertise in EXCEL you have solution for this query of mine. I have a ...
A: I wasn't entirely sure about the exact question, but I have speculated and provided what I believe ...
|
| excel formula | 7/14/2006 |
Q: I have a row in which some cells have numbers and some cells are blank. To show an example of what ...
A: Try this and let me know if it works as expected (it works using your small sample size) Place this ...
|
| Excel Dictionaries | 7/7/2006 |
Q: I’m PowerBuilder developer and I’m using Excel in my application to do a spell check on data fields ...
A: I did some more research, and as far as I can tell, it is required to add it to Word first. If I ...
|
| Excel Dictionaries | 7/7/2006 |
Q: I’m PowerBuilder developer and I’m using Excel in my application to do a spell check on data fields ...
A: This question was posted as a followup to my previous answer, but it does not appear any followup ...
|
| Excel Dictionaries | 7/7/2006 |
Q: I’m PowerBuilder developer and I’m using Excel in my application to do a spell check on data fields ...
A: ADDENDUM to previous answer - I want to say (back when I was in school) that the University had a ...
|
| Averages | 7/7/2006 |
Q: I work in a school supporting teachers with various things. One teacher has asked me to set up a ...
A: I can create a sample spreadsheet for you that does all of this; however, I have a question on the ...
|
| Update Column | 7/6/2006 |
Q: I have Data in 3rd and 4th column as in the format like> lat lon ...
A: Creating a Visual Basic program to dynamically change the contents of a cell is beyond my experties. ...
|
| Ex cel Formula | 7/5/2006 |
Q: The IF/Then worked Fabolousy! Thanks a million and a half! :0) Final Result: ...
A: I think the best way to demonstrate the other options is with an example. As such, I have uploaded ...
|
| Ex cel Formula | 7/4/2006 |
Q: A B C 1 1 1 A 2 1 2 B 3 2 1 D 4 2 2 C 5 1 3 A 6 1 4 A 7 1 ...
A: 1) If the number of combinations in the table are small enough, then you can do a nested IF/THEN ...
|
| Find in Range | 7/4/2006 |
Q: Im am trying to COUNT the number of times I have "Created" in Colmun A and "Converted" in Column ...
A: The best way to do this is with an array formula: ...
|
| Formula | 7/3/2006 |
Q: I have four words that I need to get all combonations possible. Including all one words, two words, ...
A: I believe CONCATENATE() is going to be the formula of choice for this situation. In our example we ...
|
| EXCEL Formula | 7/2/2006 |
Q: How would you create a spreadsheet or formula for cut off dates. Work that is 3-7 days old and ...
A: Depending on what your desired output should look like, there are a variety of ways to handle ...
|
| Preventing changes to an Excel spreadsheet | 6/30/2006 |
Q: After I've created an Excel spreadsheet, I need to send it to another person via an email ...
A: You will need to do this for each sheet in the workbook with data you want to "lock". 1) Click on ...
|
| Excel Pivot Tables - Labels | 6/30/2006 |
Q: I have a pivot table with 3 row fields. Here is the way the pivot table is laid out: Name ...
A: Darren: There is no feature in Excel that will allow pivot tables to display all labels instead of ...
|
| Consolodating Pivot Table data | 6/30/2006 |
Q: I manage a group of account managers. Each have there own accounts that they are responsible for. I ...
A: Daniel: I have uploaded a sample spreadsheet to the following URL: ...
|
| Calulation formulas | 6/29/2006 |
Q: When I put in any time other than 18 minutes (ex 0:18:30 I get an error in the formula cell. Is it ...
A: It will show up with an AM, and in fact, Excel will convert it to 12:MM:SS AM - that is okay. You ...
|
| Calulation formulas | 6/29/2006 |
Q: When I put in any time other than 18 minutes (ex 0:18:30 I get an error in the formula cell. Is it ...
A: Bruce: I appologize if the automated system message sounded negative on this question. When you ...
|
| Calulation formulas | 6/29/2006 |
Q: Trying to develop a formula to caluclate score based on run time for physical performance ...
A: I have given this some thought and since you are looking at a scenario with a potential for 90 final ...
|
| Sort and align routine needed | 6/28/2006 |
Q: Yes- you understood correctly- the example you gave is perfect! By the way- at times there will be ...
A: There may be an easier way to do this, but I took into consideration that there may be values in any ...
|
| Excell 2003 and macro's | 6/28/2006 |
Q: Nathan, I am an end user that uses Excel 2003 and I access many workbooks throughout the day. We ...
A: I have experienced a few oddities with opening Excel files over a network location. Typically they ...
|
| Sort and align routine needed | 6/28/2006 |
Q: Sort and align routine needed- An excel file contains multiple columns of different data- lets say ...
A: I believe I may need a sample file on this one. Lets see if I understand first...from your ...
|
| Offset and address combination | 6/27/2006 |
Q: Why doesn't this equation work? ...
A: I'm not certain this will answer your question, but it might. When I input the first formula you ...
|
| creating important macro for my new important job :) | 6/15/2006 |
Q: It will be very hard for me to explain. It would probably take you 2 seconds to figure this out if ...
A: I answered this question with my email address a while back, but it appears that answer didn't ...
|
| Excel Invoice | 6/13/2006 |
Q: Followup To Question - Can you show me please how to fill the excel invoice with the help of a ...
A: If you are talking about using a form to enter customer information, and then have that form ...
|
| Formula | 6/12/2006 |
Q: I'd like to know how I can calculate the cells from multiple pages without having to go to each ...
A: Choni: This is one of those situations that has multiple solutions. Some require VBA, some require ...
|
| Excel Invoice | 6/11/2006 |
Q: Can you show me please how to fill the excel invoice with the help of a data base with the name, ID, ...
A: I have uploaded a sample invoice to the following URL: ...
|
| Excel Invoice | 6/11/2006 |
Q: Can you show me please how to fill the excel invoice with the help of a data base with the name, ID, ...
A: You may need to send me another message with additional detail on what you are wanting to ...
|
| Linking in Excel | 6/10/2006 |
Q: I did but book2 does not appear. I try to use the window avenue through the help command but again, ...
A: NOTE - I have uploaded a video of how to do this to the following location: ...
|
| Linking in Excel | 6/9/2006 |
Q: This is what I did 1. opened and saved book1 and book2 -2 I wrote the formula in book2 ...
A: You manually typed Book1.xlssheet1A11, etc again. Please stop doing that. I have simplified the ...
|
| Linking in Excel | 6/9/2006 |
Q: I did what you said but in step 4 the--formula bar does not has +[Book2.xls]Sheet1!$A$1 ...
A: Bill: What does it show?...it should show something, ie, ...
|
| Linking in Excel | 6/9/2006 |
Q: Nathan, I'm back again. For whatever reason my links are not working. I have two workbooks, Book1 ...
A: Bill: The problem is that you appear to be trying to type in a linked formula manually. Although ...
|
| Budget | 6/9/2006 |
Q: I'd like to create a budget using excel whereby I put a list of expenses versus my incomes to ...
A: James: First off, here is a sample budget I prepared for someone last month. It may or may not ...
|
| Why do I keep getting the error "Reference not valid" and how do I fix it? | 6/6/2006 |
Q: I have been copying and pasting from various websites to my spreadsheet and seem to have copied an ...
A: Amanda: In a perfect world, you should be able to right-click on the icon and select "cut"; ...
|
| excel | 6/5/2006 |
Q: I just have a quick question regarding microsoft excel and visual basic access. I'm learning excel ...
A: I’m not certain exactly what you are looking for, but Excel, by itself, is a powerful calculator. I ...
|
| Search formula | 6/5/2006 |
Q: Actually the formula I am looking for is not independent of the first one, but is instead further ...
A: I believe I have it: ...
|
| Search formula | 6/5/2006 |
Q: I want to search a database in excel with the following formula; ...
A: David: There are probably several ways to do this, so let me know if the below method doesn't work ...
|
| Excel Stacked Column Chart | 6/3/2006 |
Q: I want to make a stacked column chart with 3 segments. The top segment would show my starting ...
A: Rich: I figure the best way to present this solution is by example. As such, I have uploaded a ...
|
| Pivot Table Source | 5/31/2006 |
Q: I am currently using MS-Excel 2003. Q # 1: Can i select data source from multiple source points? Q ...
A: There is a lot of info here...please let me know if you have any problems implementing any of it: ...
|
| Adding up data using sumif | 5/26/2006 |
Q: In the format of 0S1 2S3 5S2 4S3 etc. There could be from 2 to 12 lots of xSx The cells go across so ...
A: You are on the right track, but I believe the easiest way to do this will require an array formula. ...
|
| Categorical scatter plot | 5/25/2006 |
Q: I have multiple data points (10-20) for multiple catergories(groups). (I wish to name the categories ...
A: I think I understand what you are looking for, and I have designed a sample graph and uploaded it to ...
|
| Excel 2000 - change column left to right | 5/24/2006 |
Q: Subject: Excel 2000 - change column left to right Answer Jaz: There are a variety of ways to ...
A: I have uploaded a sample spreadsheet to the following location: ...
|
| Excel 2000 - some thing like Conditional Formatting | 5/23/2006 |
Q: Mr. Nathan Head Please tell me how I can do this if it’s possible, I will explain you by an ...
A: Good morning JAZ! As you suspected, this can be done with conditional formatting. I have placed a ...
|
| Excel 2000 - change column left to right | 5/22/2006 |
Q: Subject: Excel 2000-Copy and Past Row to Column Question Hi I want to ask how I can Copy and Past ...
A: Jaz: Excel does not provide a way to "transpose" in that manner, but you can do a Sort to reorder ...
|
| keystoke command for 'dragging out' in excel | 5/20/2006 |
Q: I use the 'drag out' feature quite often in excel. Whether it is a date that I am 'dragging out' to ...
A: I know exactly what you are talking about in regards to dragging…I have a few spreadsheets in excess ...
|
| Conditional COUNTIF | 5/19/2006 |
Q: Nathan, Thank you again, your modifications to my formula are doing exactly what I needed. Can you ...
A: Well, I honestly haven't ever read a book on Excel, so I can't recommend one, per se. However, I do ...
|
| Advanced formula needed | 5/19/2006 |
Q: Microsoft Office 2003 Professional Edition OK. What I have here is a table on multiple worksheets ...
A: The below formula will sum all of the tabs in a spreadsheet where the value in D9 is not equal to ...
|
| Conditional Formating | 5/19/2006 |
Q: Is there any method (in Conditional Formating or anything else) by which I can format a cell to ...
A: That is a new one on me – I had to research it myself to see if it could be done :). Quote from a ...
|
| how to make search bar | 5/19/2006 |
Q: As we now we can use access to mange database For example if we have table contains information ...
A: Sample file updated: http://home.swbell.net/nate-sus/excel/database.xls This can be done with forms ...
|
| Conditional COUNTIF | 5/18/2006 |
Q: With difficulty I have created an array formula that will COUNTIF the value in C23 is less than the ...
A: Good morning Alan! Here is a modified version of your formula: ...
|
| Multivariable formulas | 5/18/2006 |
Q: I have a stone installation business that has a number of cost variables involved in figuring out my ...
A: Good evening! It is time for you to become familiar with the Forms toolbar. Right-click on any ...
|
| Excel IFTHEN statement and formatting | 5/18/2006 |
Q: I've designed a simple checkbook register in Excel 97 and have used an IFTHEN statement so that ...
A: Because of the & in the middle of this formula, it is converting the formula result from a number to ...
|
| Excel date formatting | 5/18/2006 |
Q: I am importing a report in CSV format into Excel 2003 and the dates are being changed. The date 1 ...
A: Since Excel doesn't recognize 20060301 as a valid date, it will display ##### when formatting the ...
|
| Excel 2000-Copy and Past Row to Column | 5/18/2006 |
Q: [ ][ A ][ B ][ C ][ D ][ E ][ F ] [ 1 ] Aa Ba ...
A: Excellant description of the problem! I love it when people put before and after examples into ...
|
| Excel | 5/17/2006 |
Q: I have an issue with MS Excel when I try to open a file, it takes forever to access the directory. I ...
A: I will admit upfront that this sounds like a hardware/software problem between office and the hard ...
|
| formula | 5/17/2006 |
Q: What I would like to know is can I set a value limit in one cell say the value is 8 and i do this ...
A: Setup: A1 = 8 (normal day) B1 = 10 (overtime day) C1 = 10 (overtime day) D1 = 6 (short day) E1 = 8 ...
|
| sound embedded in Excel | 5/17/2006 |
Q: Basically I want to have a very small clip embedded into an Excel sheet. I understand how to play a ...
A: I believe the following should work (at least it embeds the file; I don't know if it will still work ...
|
| Excel '97 | 5/17/2006 |
Q: I was wondering how I would go about locking a row, so that I can pick a column and use that to sort ...
A: I suspect I do not understand the question. If you have the following data in rows 1 thorugh 3 of ...
|
| 2003 excel graphs | 5/16/2006 |
Q: I am trying to graph data for 2 years. I want this to show comparison between 2005 and 2006. i ...
A: In order to break up the data you will need to separate the "series" a bit. This can't really be ...
|
| how to make search bar | 5/16/2006 |
Q: As we now we can use access to mange database For example if we have table contains information ...
A: I have a couple of ideas that might fit the bill. Here is a link to a sample spreadsheet: ...
|
| Linking in same sheet to a dynamica cell with an specific text | 5/15/2006 |
Q: Thanx a lot but unfortunately thats the case, I aways paste the data, because i where i have ...
A: There is almost always a way...lets try this: http://home.swbell.net/nate-sus/excel/filename.xls ...
|
| Linking in same sheet to a dynamica cell with an specific text | 5/15/2006 |
Q: This is what i am trying to do, i have an index for some info (example Store A, Store B, Store ...
A: Here is one way to do it: 1) Find the cell where "Store XYZ" is currently located. 2) Click on ...
|
| Religeous Holiday Formulas | 5/14/2006 |
Q: Am using MS office 2003 for Excel. I have a formula for calculating Easter for any given year but ...
A: I thought this was going to be simple, but that was because (at the time) I didn't know anything ...
|
| Can the range specified in... | 5/14/2006 |
Q: Can the range specified in the MATCH function be dynamically calculated? The target area begins at ...
A: I appologize for the delay; I normally like to respond with an answer on the same day, but I was out ...
|
| Averages | 5/12/2006 |
Q: I am working with golf hndicap program that I created. I find out there handicaps based on there ...
A: I think the solution for this situation is going to be extremely dependant on how your spreadsheet ...
|
| Overtime Formula | 5/12/2006 |
Q: I am running Excel 2002 SBE. I have a spreadsheet onto which a fee-earner enters the fee earner, ...
A: Good afternoon Jeremy! http://home.swbell.net/nate-sus/excel/times.xls The above link is a sample ...
|
| separating elements in a colunm | 5/12/2006 |
Q: I have a technical question about creating walking lists, as those used by canvassers. Can ...
A: Good morning Jerry! This is definitely doable with Excel, and although there isn't a special ...
|
| merging workbooks | 5/11/2006 |
Q: I have 2 different workbooks with generally the same columns and data in them. How do I merge both ...
A: Excel has a merge wizard, but it only works with shared workbooks that originally were the same ...
|
| Filtering Dates in Excel | 5/10/2006 |
Q: I have a worksheet that has a column full of birthdays in it for more than two hundred people. I ...
A: In a perfect world, Excel would let you filter by formula (maybe we can keep our fingers crossed and ...
|
| excel macro | 5/10/2006 |
Q: I would like to create a macro to save my file and close out excel and assign it to a command ...
A: 1) First you will need to create your macro - You can start by recording a macro to save your ...
|
| Excel autofilter | 5/9/2006 |
Q: Is there any way to either increase the size of the arrow, or better still, change its colour when ...
A: That is a though one - As far as I can tell, there are no settings within Excel that let you control ...
|
| Excel Formula | 5/9/2006 |
Q: I have enclosed an extract of the spreadsheet that I need a formula for to extract data from. What ...
A: Hopefully this is what you are looking for - aka, I'm not 100% certain what all data you want ...
|
| Counting a column with a previous formula | 5/8/2006 |
Q: I have a spreadsheet with a column that has a simple formula (A5-A6)which is hour:minute and comes ...
A: Good afternoon Kim! I believe I know what the problem is - I will use 6:30 and 6:00 as an ...
|
| Email one spreadsheet only | 5/8/2006 |
Q: Can you tell me how I can email one spreadsheet only from a workbook containing many spreadsheets, ...
A: Good morning Roger! If everything is installed properly, you can email directly from Microsoft ...
|
| formulas for changing colors | 5/7/2006 |
Q: How do I change the color of a cell's numerical value to green when the numerical value in the cell ...
A: To do something like this you will want to use conditional formatting: Select the cell you want to ...
|
| excel workbook | 5/6/2006 |
Q: how do you create random numbers in excel workbook? I am attempting to answer the following question ...
A: If I needed to do a random sample of 74 accounts, I would use the following formula: ...
|
| Excel 2003 - noncontiguous data in array formulas | 5/4/2006 |
Q: I'm try to run a multiple regression using LINEST. However, my two independent variables are ...
A: Good afternoon Greg! Okay, you got me - I have never used LINEST before. As such, I'll play around ...
|
| rounding | 5/4/2006 |
Q: I use a formula such as =A20*B20 I need the answer to be rounded up to the nearest Multiple of 10. ...
A: Good afternoon Steve! This is really just a little bit of mathematical trickery, but it should get ...
|
| "If - Then" formulas | 5/4/2006 |
Q: I have three columns. In column A there are entries such as "A", "B", "C", etc. (These etries will ...
A: Good morning John - lets see if we can get you some formulas... For your first question, you will ...
|
| converting negative numbers into positive | 5/3/2006 |
Q: what would the formula be if you wanted to add all numbers in a column and some all negative and ...
A: After giving it some more thought, you could sum an entire column using this formla: ...
|
| converting negative numbers into positive | 5/3/2006 |
Q: I want to add a column of numbers. Some of the entries are negative, but I need to convert them ...
A: You will need to use the absolute value function in Excel to add your numbers: For example, if A1 ...
|
| excel | 5/2/2006 |
Q: I just sent you a problem calling it a spreadsheet, its Microsoft Excel. Also if I could add that if ...
A: This may not be exacly what you are wanting, but it is the simplest solution I could come up with: ...
|
| recurring payments in budget | 5/2/2006 |
Q: I'm fairly new to excel. I am building a budget and I am looking for a way to have excel ...
A: Excel is an awesome tool for doing budgets! There are a variety of things you can do to get ...
|
| Excel | 5/2/2006 |
Q: Excel 2003 I have a range of six cells in a row on my spread sheet. Five cells are empty and one ...
A: There are a variety of ways one might go about doing this, but I think CONCATENATE will be the ...
|
| creating a copy of a shhet | 5/2/2006 |
Q: I have done it many times but now I get a circle with a line through it, what does this mean? What ...
A: Normally, holding down Ctrl while dragging a tab will create a copy of it. Here are some things ...
|
| Textfile saved from Excel | 5/1/2006 |
Q: Whenever I want to use a textfile created by doing a saveas on an Excel worksheet, I can't access ...
A: There is a method behind the madness of that message/restriction. If you were to open the file in ...
|
| Excel formula for form organization | 4/28/2006 |
Q: I have a small formula problem I haven't been able to figure out. I do not know if it is possible ...
A: The simple answer to your question is to use a nested IF function. These can pretty much go on and ...
|
| COUNT and IF questions | 4/28/2006 |
Q: I've been working with EXCEL 2003. I made a spreadsheet for my employer that's worked out well, ...
A: As with any solution, there may be a more elegant way to do it than what I propose - nevertheless, ...
|
| formula | 4/27/2006 |
Q: i need help with a REPLACE formula. i have part numbers that contain a * within the number and at no ...
A: Presuming your number with the * is in cell A1, you would use this formula: ...
|
| Puzzle solving. | 4/25/2006 |
Q: Is there a way to have Excell separate the numbers, or do I need to manually do that for all the ...
A: Yes, there is an easy way...here you go: 1) Copy and paste all of the pairs into one column in ...
|
| Puzzle solving. | 4/25/2006 |
Q: I am a soldier at a military post, and am new to a form of land navigation sport. I am trying to ...
A: What a small world. I also participate in Geocaching and this happens to be my cache in Lawton, OK ...
|
| image link on excel spreadsheet | 4/24/2006 |
Q: i can't make my image name to link so when i click on the image name it will either pop-up or link ...
A: Using the insert->hyperlink option should work as you have attempted. It should open the picutre in ...
|
