About Experts Sitemap - Group 76 - Page 7 2012-07-27
Excel: Format Cells, douglas m smith, format cells
douglas m smith, format cells, vlookup formula: Hi Michael, If you rework your Saves/Payout list, you will be able to read it with a VLOOKUP formula. 0 0 55 1.25 60 1.50 85 2.00 110 2.25 135 2.50 If the above table is in cells A1:B6 and the value to lookup is in cell...
Excel: Format Cells, format cells, bonus structure
format cells, bonus structure, thanks in advance: Michael, you can use the following equation to get the result you are looking for. =IF(D2 =135,D2*2.5,IF(D2 =110,D2*2.25,IF(D2 =85,D2*2,IF(D2 =60,D2*1.5,IF(D2 =55,D2*1.25, NA ))))) It just drills down through the levels until it finds one that applies,...
Excel: Format Check, msgn, input format
msgn, input format, decimal point: Some. The format of the cell will no impact whatsoever on what the user types. I can could type Rick in the cell and that cell could be formatted as Date, for example. Right? So, using the cell s format as a basis for testing is not a good choice. I...
Excel: Format Column, query in access, math functions
query in access, math functions, column c: Lat = Left(Range( C1 ).Value, 2) + Application.Round(Mid(Range( C1 ).Value, 4, 9) / 60, 5) Lon = Left(Range( D1 ).Value, 2) + Application.Round(Mid(Range( D1 ).Value, 4, 9) / 60, 5) Or: For each thing in Range( C1 ,Range( C65536 ).end(xlup)) Lat =...
Excel: Format cell on Click, conditional formatting, mail group
conditional formatting, mail group, vba code: Hello Rose, If you don t like using VBA (macros) then your only option would be to use Conditional Formatting based on some number value or entry you make in the cell (or a nearby cell). If you don t mind VBA then I d recommend coding the double-click...
Excel: Format of comments, keystroke combination, default format
keystroke combination, default format, input box: I m afraid I m not aware of a way of doing it automatically (although there MAY be) but you COULD have a macro assigned to a keystroke combination which asked for the contents of the comment, then set that comment on the active cell with whatever formatting...
Excel: Format a control, microsoft excel 2000, using microsoft excel 2000
microsoft excel 2000, using microsoft excel 2000, control tab: Hi Kyle, How are you? I guess that you are drawing a combo box from the control toolbox and you want to use the combo box from the forms tool bar So, 1. VIEW - TOOLBARS - FORMS 2. Draw the COMBO BOX and you get a fialog box with 5 tabs. The last...
Excel: Format for date, douglas m smith, format button
douglas m smith, format button, dialog boxes: Hi Peter, Yes, this is possible, but not through normal formatting. Select the date cells, and then select Conditional Formatting under the Format menu. In the dropdown box, select Formula Is . Enter the following formula in the edit box: =WEEKDAY(A1)=1...
Excel: Format for hours and minutes, chip pearson, cpearson
chip pearson, cpearson, mvps: Hi Peter, Let s say you want to calculate the difference between 8:00 am and 10:00. This of course comes to 2 hours. If you show this as hh:mm, to Excel that is showing 2:00 am, rather than meaning this 2 hours and no minutes is time elapsed. There...
Excel: Format protection, column widths, excel 2000
column widths, excel 2000, spreadsheet: Hello, Debi, If you ve left a cell unlocked to allow for editing, users will be able to modify the cell formatting however they want. They cannot, however, adjust row heights and column widths when the worksheet is protected, as that is considered a...
Excel: Format/Row/Autofit, autofit, excel 2000
autofit, excel 2000, culprit: If you re using merged cells, that could be the culprit -- there are problems in thie regard with merged cells. Also, a cell s display is limited to approximately 1024 characters -- any longer than this and it just won t adjust row-heights correctly all the...
Excel: Format Varying Length Decimal Places, format cells, format numbers
format cells, format numbers, number thanks: If the value is stored as a number, there is no way for excel to know whether you mean 1000111.1 or 1000111.10 the only solution would be to format the cells as Text, then enter the number (format= cells= Number tab, select Text). an alternative is to...
Excel: Formating background according to value, conditional formatting, value question
conditional formatting, value question, background colors: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros...
Excel: Formating Date, cell b1, cell a1
cell b1, cell a1, first date: Hey Tonya, To get the date to repeat in all cells without typing it in, you can follow these steps. (These steps assume that your first date, Jan 02 is in cell A1. You will need to modify the steps if yours starts in a different cell): 1. Type Jan 02...
Excel: Formating Page, paul henson, excel 2000
paul henson, excel 2000, cells: A column can be one width and one width only, so you can t do it - at least, not as you have specified it - HOWEVER, you COULD merge the cells in rows 16-30 with the next column to allow you to achieve your aim (in these cells, the width of the column will...
Excel: Formating Sorted Columns, drop down list in excel, format cells
drop down list in excel, format cells, data validation: The following assumes that each column has values in it. For instance, you click on the header in C4, and in C4:C35 the cells are filled with values, and C36 is blank. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Cells.Interior.ColorIndex...
Excel: Formating a spreadsheet, page break, common factor
page break, common factor, spreadsheet: I could answer this question with a lot of questions as there are a lot of unknowns here. What I ll try to do is give you pieces of code to use and you might able to gleen what you need to build your code. Sorry about that. I may need to see an example...
Excel: Formatting, note numbers, click options
note numbers, click options, help file: I m afraid you are not going to be able to do it - at least, not easily - percentage formatting multiplies the value of the cell by 100 and adds a % sign on effectively - so to enter .22% you need to do .0022 - HOWEVER, you can stop 22 being interpreted as...
Excel: Formatting, cell d1, cell b1
cell d1, cell b1, decimal point: 1. Why the data in cell B1 to B3 is not with decimal point 1. (B1=3.0 B2=5.0 and B3=0.0) Check the formatting - it could be set to Text 2. In cell B4 the formula is =SUM(B1:B3) the result should be 13.0 but it is 0 instead. Check the formatting - it...
Excel: Formatting, current sheet, page break
current sheet, page break, thanks in advance: Hi, Upload your file using the following link: http://www.savefile.com/index.php?id=upload Once you uploaded it will provide you another link, something like: http://www.savefile.com/files/5550756 Post that link here. Regards, Sriraman...
Excel: Formatting, cell a1, colorindex
cell a1, colorindex, format button: HELLO Brian The easy way is to set up a conditonal formula for the range of cells you wish to change color relative to another value. Lets say they key value is in cell a1 and you want the font of cells a2 TO C2 interior colour ( it could eb font color...
Excel: Formatting Cells Automatically, colorindex, interior color
colorindex, interior color, cl: You d need VBA code to do this. Select all the cells involved and run this procedure: (Alt/F11, Insert Module, paste this code. Return to Excel, make your selection, Alt/F8, select ReverseIt , click Run): Sub ReverseIt() For Each cl In Selection ...
Excel: Formatting Cells, douglas m smith, validation data
douglas m smith, validation data, data validation: Hi Amer, You can limit the number of characters to a cell by using Data Validation (Data menu) but that only works with direct data entry. For this problem, you should use Text to Columns. Paste the entire text string into the cells where you only...
Excel: Formatting a cell for latitude/longitude data, latitude and longitude, chip pearson
latitude and longitude, chip pearson, latitude longitude: Hi Meg, I could not even begin to do better than one of the real excel experts Chip Pearson . Check out his page on Latitude and longitude. I believe he explains everything that is possible and the easiest way to display latitude and longitude. http://www.cpearson.com/excel/latlong.htm...
Excel: Formatting cells in Excel for time, billing program, decimal number
billing program, decimal number, decimals: Barb Time is always tricky to work with becaue of the formatting etc. In your example of 6:41 Am in and 5:04 Pm out if both are formatted the same and you subtract one from the other you will get the answer .4326 (if the answer is formatted as number...
Excel: Formatting cells, cell a2, consecutive number
cell a2, consecutive number, feature type: If you just need to automatically create a consecutive number, and you have the number 1 in cell A1, then you would put a formula in cell A2: =A1+1. You can do this manually using the fill feature. Type the number 1 into a cell. Highlight that cell...
Excel: Formatting customs cells in excel, spread sheets, cells
spread sheets, cells, customs: Formatting can t affect the value of a cell. You could use a custom format so that if the cell value is #N/A, the font would be white, and appear invisible. If you want the value to be zero, you have to do it formulaically. For instance: =IF(ISERROR(A1),0,A1)...
Excel: Formatting Date Cells, using microsoft office, microsoft office 2003
using microsoft office, microsoft office 2003, numeric value: The first thing I think I would do would be to format the column as a NUMBER - this would highlight any cells that are being treated as text as the cells would not change to a numeric value. It is my suspicion that part of the problem is that some cells are...
Excel: Formatting In Excel, format feature, b1
format feature, b1, formatting: Tim If the formatting is different in each of the two cells that you are combining the data from, it won t be possible to have two formats, but if not it can be formatted any way that you want, using the format feature. Remember the formula +A1& &+B1...
Excel: Formatting an entry, range target, format option
range target, format option, input data: Sorry, I rushed the previous reply a bit - this macro is entered pretty much as you said (I use Alt F11 to get into the VBA editor, then choose the worksheet from the menu choice, and set it to change on the right hand side. The macro doesn t show on the...
Excel: Formatting error message, revision 3, formating
revision 3, formating, microsoft excel: The message means what it say - I can only find this documented in Excel for mac, but even if this ISN T your platform I would expect the same solution to work:- Error message: Too many different cell formats in Excel for Mac Article ID : 213904 Last...
Excel: Formatting in excel, conditional formatting, format button
conditional formatting, format button, menu format: Hi Nathan, I apologize for my slowness in responding to your question. I am having a problem with posting answers in that my answers keep failing to get sent. I sent an answer yesterday, but now I see that again it never got posted. The easiest way to...
Excel: Formatting in excel, left hand side, format button
left hand side, format button, fourth column: Hi Nathan, You can do this pretty simply by completing the following steps. 1) Click on the square above the 1 and to the left of the A at the upper left hand side of the spreadsheet. This will select the entire spreadsheet. 2) Select Edit from the...
Excel: Formatting excell cells to avoid emtpy space in cell, left hand corner, paste values
left hand corner, paste values, paste data: I think you have answered your own question right here. Turn off the Wrap Text feature. If you want to do this to the entire worksheet, select all cells by clicking on the little square in the upper left hand corner of the worksheet (to the left of the letter...
Excel: Formatting hours and minutes, box hh, negative time
box hh, negative time, peter answer: Peter If you mean showing it as - 10.00 pm 2/3/2004 then no. Excel can t handle negative dates. If you just want a - in front of the text you will have to modify the format into something like this in the Custom section of the Format cell box - hh:mm...
Excel: Formatting hours and minutes, cell a1, negative time
cell a1, negative time, cell b1: Peter, Unfortunately, Excel (and all Microsoft products) cannot handle negative times. However, I have found a way around it. For example, Assume that you have 08:00 in cell A1 and 20:00 in cell B1. Taking the difference, one would expect -12:00. As...
Excel: Formatting individual items inside a listbox, vba programmer, eval version
vba programmer, eval version, internet explorer: Aubrey, There is no standard way to do this that I am aware. You could make a custom ListBox with a Class Module, but that s not a task for the casual VBA programmer like myself. Betting that someone has already ventured down this path, I put excel listbox...
Excel: Formatting MsgBox, score range, documents and settings
score range, documents and settings, grade worksheets: Hi Vivek, I think you will find this formatting more pleasing, and the code more compact. The addition of the With statement enabled qualifying the worksheet without typing it multiple times. Also, I eliminated the path from the file SaveAs since the ChDir...
Excel: Formatting Multiple Workbooks (Files) all at once, column width, data structure
column width, data structure: There is one method I can think of that fits your situation (separate workbooks; not in the same file). 1) Make all of your formatting changes to just one workbook. 2) Select the entire worksheet (or workbook if there are multiple pages). 3) Copy the...
Excel: Formatting Multiple Workbooks (Files) all at once, folder test, sheet names
folder test, sheet names, sheet1: I will try to go step by step, if there is anything that it is not clear please let me know. First, let s set the infrastructure and define what we will do. In this example we will format a range in the first worksheet in all the Excel files inside a folder...
Excel: Formatting Multiple Workbooks (Files) all at once, test ie, test folder
test ie, test folder, documents folder: The reason may be that the file that have the code is not placed in the My Documents folder (or the folder that contains the test folder). Try to save it there and run the code after you have saved it, if it still doesn t work please send more details about...
Excel: Formatting Multiple Workbooks (Files) all at once, empty template, format painter
empty template, format painter, easy solution: Well, without VBA you will never get the same easy solution as you have with worksheets, for the reason that you cannot extend the selection between workbooks. The solutions that you have can be either the format painter, by selecting all the work done in...
Excel: Formatting numbers for merge document, excel spreadsheet, data source
excel spreadsheet, data source, decimal point: Dolly See if these steps work 1. With the main document open, click the Tools menu 2. Select Options… 3. On the General tab, select Confirm Conversion at Open 4. Click OK When you select an alternate data source such as Excel, it will ask you to...
Excel: Formatting Page, format cells, paul henson
format cells, paul henson, column line: Paul To size the rows, paint the rows that you want to size Clic on toolbar/format/row height Put in the height that you want the rows to be. then clic ok. Or you can drag the row line to the size that you want. To size the columns it is easiest...
Excel: Formatting page, cell b1, cell a2
cell b1, cell a2, format cells: Paul Maybe I didn t make my answer clear. As an exercise let me have you do the following. Open a brand new worksheet Paint cell A1 and B1 While these are still painted Go to toolbar/format/cells/alignment and clic the merge cells box. As you...
Excel: Formatting a spreadsheet, header footer, macro code
header footer, macro code, page break: Hey tcvb, This macro should solve your problem. It searches column A for the word Page and inserts a page break when it is found. You would modify the column to whatever column your Page values are in. Sub Macro3() For Each rCells In Range(...
Excel: Formatting a spreadsheet, page break, free time
page break, free time, spreadsheet: No, sorry. I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to write. ...
Excel: Formatting a spreadsheet, c cells, preak
c cells, preak, page break: You can put this in your personal.xls and assign it to a toolbarbutton: Sub InsertPageBreaks() Dim FirstAddress As String, c As Range Set c = Cells.Find( Page , Lookat:=xlPart) If Not c Is Nothing Then FirstAddress = c.Address ...
Excel: Formatting spreadsheets, unused cells, blank cells
unused cells, blank cells, reply thanks: Sorry for the delay in replying to you - the issue seems to be that the sheet has blank cells to the right of the actual data - two fixes I can think of - firstly, set the sheet to one page wide by as many pages tall as needed - in code With ActiveSheet.PageSetup...
Excel: Formatting spreadsheets, page break, spreadsheets
page break, spreadsheets, free time: No, sorry. I do not write macros/code for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to write....
Excel: Formatting Text to Column for ALL worksheets, array array, autofit
array array, autofit, f8 key: Laraine, the code actually looks good and functions fine when I use it, so I am not sure where the problem is arising on your end. I would suggest compiling the project to see if it can find any problems, and then if not, check it by using the F8 key to walk...
Excel: Formatting Text to Column for ALL worksheets, array array, selection commands
array array, selection commands, autofit: Laraine, I was not able to find anything wrong with your code. I did amend it a bit as you ll see below by taking out some of the unnecessary .selection commands, but both your code and my amended code ran fine in a sample workbook I put together with...
Excel: Formatting telephone numbers in Excel, dashes, parentheses
dashes, parentheses, telephone numbers: Whether or not the form is protected is not an issue. It sounds like you formatted the cell correctly and it should work. What you type also matters - this formatting should work if you type only numbers. If you type any dashes, spaces, or parentheses,...
Excel: Formatting worksheet tab, excel 2002, sure what version
excel 2002, sure what version, angie: Hi Angie, I m not sure what version of Excel you are working with, but in Excel 2002 you can do this by following these steps: -Right click on the tab name that you want to format -Select Tab Color -Choose the color you would like for this tab -Click...
Excel: Formatting, target value, sheet tab
target value, sheet tab, zeros: you would need VBA, not formatting to do that. Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value 1 Then Application.EnableEvents = False Target.Value = Target.Value...
Excel: Forms, cell position, combobox
cell position, combobox, combo box: You aren t clear in your question as to what sort of form you are referring to, but I ll assume we are talking a form in VBA - if so, use the OPEN event of the workbook to fire up the form. Don t worry about the current cell position (although activecell...
Excel: Forms, excel forms, approach web
excel forms, approach web, public folder: Hi Christy, Yes, this is something commonly done with Excel. And I should mention that Microsoft Word also has a nice Forms capability that includes checkboxes, textboxes, etc. Word might be preferable if the Form includes a lot of formatted text and the...
Excel: Forms in Excel, column line, format features
column line, format features, column width: Becky I m not sure what ou need to do in working outside the column limitations so its kinda hard to be specific with an answer. It sounds like maybe you need to check out some of the cell format features go to toolbar/format/alignment, and ck out...
Excel: Forms (option button, chech box), visual basic programming, control toolbox
visual basic programming, control toolbox, option buttons: Hi Martin, For simple checkboxes and Options you would go to the Excel menu and select View then Toolbars then Forms from the Forms toolbar there is a checkbox and an Option buttons that you can place on the Excel worksheet. Checkboxes work independently...
Excel: Forms type ListBox multiple selection, listboxes, ogilvy
listboxes, ogilvy, line 6: Try something like this: Sub ListBox1_Change() Dim Msg As String, i As Integer Dim s as String s = Application.Caller Msg = For i = 1 To 7 If ActiveSheet.Listboxes(s).Selected(i) Then Msg = Msg & Activesheet.Listboxes(s).List(i) &...
Excel: Forms, cell position, type f1
cell position, type f1, combo box: Hi Mohamed! How are you? Please try the following 0th Type the list of optins in F1:F6 1st TOOLS-MACRO-VBE 2nd INSERT-USERFORM One Combo Box and 2 commandbuttons 3rd Select the combobox View- Properties window Type F1:F6 in ROWSOURCE 4th INSERT...
Excel: Formual to track time, format cells, cell a2
format cells, cell a2, menu bar: Let s say that cell A1 reads 8:00 AM and that cell A2 reads 4:00 PM . Follow these steps to find the difference in hours. 1) Enter this formula in another cell: =(A2-A1)*24 2) On the menu bar, go to Format/Cells. 3) Click the Number tab. 4)...
Excel: Formula, csv format, csv file
csv format, csv file, comma separated values: Thanks for the nice feedback, Jeff. I forgot to answer your second question. CSV stands for comma-separated values. Let s say you have some data that looks like this: (A1) Mary (B1) Anderson (C1) Chicago (A2) Joe (B2) Smith (C2) New York If you save...
Excel: Formula, ogilvy, windows xp
ogilvy, windows xp: Teresa Assume A1 holds the date =Date(year(A1)+5,Month(a1),Day(A1)) if you want to subtract 1 day =Date(year(A1)+5,Month(a1),Day(A1))-1 if you want the last day of the previous month =Date(year(A1)+5,Month(a1)+1,0) -- Regards, Tom Ogilvy...
Excel: Formula, decimal portion, input format
decimal portion, input format, time values: Understanding date/time values in Excel can be a bit tricky if you re just starting to explore. Armed with some knowledge of how the values are stored you can come up with logical equations to sum and add the values you are entering. Assuming you re...
Excel: Formula, ofc 2003, b31
ofc 2003, b31, b14: Teresa, The first formula worked fine for me in the month of February. for the second question based on the limited information provided: =DATE(YEAR(B31),MONTH(B31)-ROUND(C31/30,0),DAY(DATE(YEAR(B31),MONTH(B31)-ROUND(C31/30,0)+1,0))) worked for...
Excel: IF THEN Formula, h11, n11
h11, n11, parenthesis: Christine The formula does not look correct. You are limited to 7 IF s in one formula. You only have 4 so that should not be a problem. However the formulation appears to be better suited to the IFAND format. For example isn t the first statement in the...
Excel: Formula, cell a1, sheet tabs
cell a1, sheet tabs, sheet1: Hello Mike, The following formula would add together all the values in cell A1 for each sheet that falls between Sheet1 and Sheet31 inclusively. =SUM(Sheet1:Sheet31!A1) If your Sheet1 is named ProjectA and your Sheet31 is named ProjectZ then it...
Excel: About a Formula, xls files, external reference
xls files, external reference, fundamental reasons: Hello, glad to meet another Jan. :-) Where are you from? If I understand you correctly, you d like to know how to link from one spreadsheet to another. I ll explain linking to you, and if I misunderstood your question, please write back again. Linking...
Excel: About a Formula, horizontal cells, xls files
horizontal cells, xls files, patience: This can t be done with formulas, as it s dynamic and dependent on when you enter the data. You would have to have a macro do it, and it d be better to run the macro from a button on file d to pick up the values from a, b, and c. Much of this can be recorded,...
Excel: ? on Formula, douglas m smith, sumproduct
douglas m smith, sumproduct, hi amy: Hi Amy, This is going to be a little complicated but that s what happens when you mix number formats like this. Assume your times are in cell range B4:B10: =SUMPRODUCT(INT(B4:B10))+INT((SUMPRODUCT((B4:B10)-INT(B4:B10))*100)/60)+MOD((SUMPRODUCT((B4:B10)-INT(B4:B10))*100),60)/100...
Excel: Formula, cell b1, cell a1
cell b1, cell a1: ...Is that difficult to do?... Well, that all depends on how much you know about macros - that s a tough question for me to answer for you. The macro you need CAN be kinda be recorded (it will have to be slightly modified to make it the way you need...
Excel: Formula, douglas m smith, brainbench
douglas m smith, brainbench, yy: Hi Steve, It looks like you have the components configured. If you connect them like this: = LATEST 4 WEEKS ENDING &RIGHT( FDM MENS !I3,8) it should give you what you want. For completeness, if you were reading the date directly from cell as...
Excel: Formula between 2 spreadsheets, cell a1, sheet1
cell a1, sheet1, mabe: That is probably because Sheet1!A1 is formatted as Text. Format it as general and the cell where you enter the formula as general and then re-enter it. If you can figure it out, you can send it to twogilvy@msn.com. Include some explanation, because...
Excel: Formula, microsoft excel 97, array formula
microsoft excel 97, array formula, relative references: Stacy There may be a better way, but I would create a word from the two columns such as digitalincome and then sum based on the new criteria. Example cells A1 thru A4 contain descriptions and cells B1 thu B4 contain descriptions. In cells E1 thru...
Excel: Formula, exact contents, column c
exact contents, column c, b2: You don t tell me what s in column C. I can help if you supply me with EXACT CONTENTS of: B2, C2, D1, B3, C3, D2, the address where you re entering this formula, what you ARE getting as a result, and what you WANT to get as a result. When you say ...in the...
Excel: Formula, accounting
accounting: This confuses me: But only do this calculation ( S18*ACCOUNTING!$AD$10 ) IF(SUM(AT18:AV18) 56,56-SUM(AT18:AV18),0),0) ?? Guessing here, since I don t understand what you want: =IF($D18 =ACCOUNTING!$AB$2,0,IF(SUM(AT18:AV18) 56,56-SUM(AT18:AV18),S18*ACCOUNTING!$AD$10))...
Excel: IF Formula, d36, question survey
d36, question survey, happy new year: I wouldn t even use an IF formula to do that. The simple formula to calculate the bonus based on n surveys would be: =((D36 =12)+(D36 =24))/10 If you wanted to add the bonus amount to the total avearge of surveys it would be: =J36+((D36 =12)+(D36...
Excel: Formula, cell references, d1
cell references, d1, c1: You would use an IF function to evaluate first whether or not the sale price is greater than zero. =IF(A1 0, B1-C1-D1, 0) In this case A1 is the sale price, the other cell references are your sales proceeds, principal and commission. Regards, Aaron...
Excel: Formula, first transaction, last transaction
first transaction, last transaction, f7: No formula can be written to copy itself, formulas can not perform actions - they just return results from calculations. If I understand what you need then you will first need to copy the formula I am suggesting all the way to last row of where you think...
Excel: Formula, uniform naming convention, array formulas
uniform naming convention, array formulas, multiple solutions: Choni: This is one of those situations that has multiple solutions. Some require VBA, some require advanded array formulas; however, there is a simple solution that will work for most people: =SUM(Start:End!C9) or =SUM(Sheet1:Sheet50!C9) Start...
Excel: Formula, apple orange, word apple
apple orange, word apple, d1: I believe CONCATENATE() is going to be the formula of choice for this situation. In our example we will have Orange in A1, Apple in B1, Grape in C1, and Seed in D1. For the remainder of the columns to the right, E through however many you need (which will...
Excel: Formula, bad habit, column c
bad habit, column c, d3: Troy I would use a formula like in E3 =if(and(len(trim(C3))=0,len(Trim(D3))=0), ,E2+C3-D3) then drag fill down. This worked fine for me. Normally I would use a form of the simpler formula you presented, but since you say it isn t working,...
Excel: Formula, spreadsheet example, column c
spreadsheet example, column c, hth: Troy, That is odd. Are you sure that you typed the formula correctly. The IF formula should only return FALSE if the last argument is missing. In this case the last argument is D1-B2+C2. I can t find a way to get the formula to return FALSE for me. If...
Excel: Formula, douglas m smith, trucking company
douglas m smith, trucking company, formula usa: Hi Debra, Sorry about the delay. The RANK function should do what you want. If your miles are in cells B2:B100, the formula would be: =IF(RANK(B2,$B$2:$B$100) 9, ,RANK(B2,$B$2:$B$100)) This will just show the top 9. Douglas M. Smith Brainbench...
Excel: How-to Formula, celebrex 200, pharma company
celebrex 200, pharma company, cell a1: Joe, After you have done the text to columns command, run this macro to remove the other non-numeric characters. Expand / change the range in the Range statement (line 2) to the true size of your range. XF Sub NoLetters() For Each cell In Range(...
Excel: Formula, cell a1, cells
cell a1, cells, variables: Hi Erica, =COUNTIF(I2:I845, 10/15/06 ) =COUNTIF(I2:I846, Live ) Another way to do this is to use a variable . Instead of typing into the formula 10/15/06 or Live you would type in a cell such as A1. Then anything you type into cell A1 would be...
Excel: Formula, cell b1, array formula
cell b1, array formula, cell b2: Hi Dlana, You would type a formula similar to the one below, just adjust for the right range that you need. =SUM((Sheet2!A1:A10= Joe )*(Sheet2!B1:B10=100)) Then what you need to do is once you have entered the formula you need to make it an array...
Excel: Formula, database 2, digit numbers
database 2, digit numbers, minor adjustments: John: Can you send me a copy of the formula you are currently using? I browsed through my previous questions, but you would be surprised how many questions I get from people named John :). I suspect we will be able to use something similar to the...
Excel: Formula, e san juan, cell reference
e san juan, cell reference, column c: The formula you want is VLOOKUP This works as follows =vlookup(WHAT,WHERE,WHICH,LOGICAL) What is the value you want to look up (so 7369/9952 etc, but obviously putting in the cell reference is fine) WHERE is the location of the lookup table - the...
Excel: Formula, array formula, array formulas
array formula, array formulas, mouse pointer: Hi John, Here is a good formula that you can use to count unique entries with. =SUM(IF(FREQUENCY(IF(LEN(Range1) 0,MATCH(Range1,Range1,0), ),IF(LEN(Range1) 0,MATCH(Range1,Range1,0), )) 0,1)) I copied this formula from the following site. ...
Excel: Formula, type xxx, happy new year
type xxx, happy new year, occurances: Hi Selena Happy New Year!! I am sorry but I cannot understand well what you mean. But, please try the function countif e.g 1. Type the data you want to count in A1:A10 2. Type xxx in G1 3. Type =countif(A1:A10, G1) Sorry but I think that you need...
Excel: Formula, array formulas, array formula
array formulas, array formula, double array: Hi Selena, Sorry about the headache. It is probably because it is not intuitive to know how to do double array formulas until someone has shown you. That is how I learned about them. If you want the formula on the same worksheet as the column D &...
Excel: Formula, array formula, sheet1
array formula, sheet1, yyy: Selena, You need to use an array formula. Your example isn t very specific so you will need to adjust it to suit your specific need. The following formula will count each row in Sheet1 (from row 1 to 10) where the value in column D= xxx and the value in...
Excel: Formula, occurances, sheet1
occurances, sheet1, spreadsheet: The simplest way is to add a column that concatinates columns D and E of the first tab. For instance, on that first tab, enter in cell F1 the formula =D1&E1 and copy down the column. Then, on the second tab, you can use the formula: =COUNTIF(Sheet1!F:F,...
Excel: Formula, cell a1, digits
cell a1, digits, len: Manny: Presuming the number you mention is in cell A1, you could use this formula: =RIGHT(A1,5) It takes only the right 5 characters from a string. Just in case you need to remove everything to the left of a dash (ie, the number of digits to the...
Excel: Formula, column c, windows xp home edition
column c, windows xp home edition, windows xp home: Hi Gerry, I m really sorry for the delay, but thought I had answered this question. Here s how: First put your beginning balance in column C - Let s say the checkbook starts at row 2. This formula goes in C3: =c2+b3-a3 Copy this formula down...
Excel: IF Formula, null string, sum total
null string, sum total, a2: Hi Dennis, Yes, this is possible. For example, if the two numbers are in cells A1 and A2, then a formula that would do this would be: =IF(AND(ISNUMBER(A1),ISNUMBER(A2)),A1+A2, ) This simply yields a null string if either number is non-numeric. ...
Excel: Formula, b1, a3
b1, a3, a1: Robert: Using just your data above, this formula provides the expected result: =SUMPRODUCT((A1:A3= A )*(B1:B3= X )) That being said, you will need to modify the formula so that the range matches the number of rows actually used...ie, instead of...
Excel: If Then Formula, digit value, translation table
digit value, translation table, b cell: What you don t tell me is where I get the translation table from, but =mid(a1,9,1) would return the 9th character from A1 - if it s the 7 you want to see, then this formula on it s own is enough, otherwise it would need to be combined with a look up table...
Excel: Formula, sign checks, d9
sign checks, d9, ray ray: Ray - The formula you want is SIGN(). SIGN checks a number, and returns a 1 if it s positive, a 0 if it s 0, and a -1 if it s negative. So for your first equasion, determining if the amount is positive or 0, you just test if the number is negative: =IF(SIGN(D9-D4)=-1,...
Excel: IF Formula, cell a1, logical function
cell a1, logical function, switches: You can nest IF formulas, and use the logical function AND, in a way like this: =IF(A1 =400,A1*1.05,IF(AND(A1 600,A1 =800),A1*1.03,IF(A1 =600,A1*1.04, Out of range ))) Other option, that may be easier to read, is to directly use the logical comparisons in...
Excel: Formula, translation table, translation 2
translation table, translation 2, vlookup: Usually, you can create a translation table, that is basically a range of two columns where you relate the letters in one column and the numbers on the other. Once you have it created, it is usually useful to name it (menu Insert- Name- Define), as it will...
Excel: Formula, format cells, input number
format cells, input number, ogilvy: Robert, Select the cells you want to have that behavior do Format= Cells, then select the number tab and go to Custom. Put in a format like: [Red][ 300]General;General General could be a specific format such as #,##.00 as an example. -- Regards,...
Excel: Formula, conditional formatting, input number
conditional formatting, input number, format option: Robert Select the cells that you want to highlight if they go above 300 then go to Format Conditional Formatting and condition 1 use Cell Value is Less Than 300 and change the format option to cell color red. This will make all cells less than 300 red...
Excel: Formula, a4 a6 a8, indirect address
a4 a6 a8, indirect address, cell b1: Prem: Try placing this formula in cell B1: =INDIRECT(ADDRESS((ROW()-1)*2+1,1)) Place this formula in cell C1: =INDIRECT(ADDRESS((ROW()-1)*2+2,1)) Copy the formulas down as far as necessary. Please let me know if you have any additional questions...
Excel: Formula, data columns, value error
data columns, value error, quickview: =SUMPRODUCT(--( All Actions (2) !P4:Q341 =DATEVALUE( 7/1/2007 )),--( All Actions (2) !P4:Q341 =DATEVALUE( 7/31/2007 ))) +SUMPRODUCT(--( All Actions (2) !T4:U341 =DATEVALUE( 7/1/2007 )),--( All Actions (2) !T4:U341 =DATEVALUE( 7/31/2007 ))) +SUMPRODUCT(--(...
Excel: Formula, cell b1, cell a1
cell b1, cell a1, cell c1: Hi Kim, You can use nested IF functions to evaluate the sales % of a product. You want to evaluate the least percentage first and work your way up to the higher percentages. This is the basic structure of nesting IF functions. In this example I placed the...
Excel: Formula, working solution, f1 key
working solution, f1 key, pivot table: My suggested book would be the F1 key - this gives online help. It s worth searching and learning how to use this facilty. The rest of your question is slightly hazy to me - it s SOUNDING as though a pivot table may be the way to go, but I m probably wrong...
Excel: Formula, 2c1, parenthesis
2c1, parenthesis, b1: The invalid name is likely due to using { and [ in your function...Excel requires all parenthesis. In addition, PI has to be entered as PI()....with parenthesis at the end and nothing in them. I did write this formula, based on the information you provided:...
Excel: Formula, row labels, middle values
row labels, middle values, field settings: Quickest way would be to use a pivot table - use the data menu, then pivot table and chart report - follow the wizard, then drop the field you want onto the left (row labels) and the middle (values - in this instance a count) IF it comes up as SUM instead,...
Excel: Formula, f3, parentheses
f3, parentheses, cel: Hi Graham, This one is going to require a follow up from you. From what I can tell, #1 and #4 can be combined to be F3 D3=YES and #3 and #6 can be combined to be F3 E3=NO. Where I struggle though, is with your inbetween conditions. What if D3 is bigger...
Excel: Formula, straight text, sum function
straight text, sum function, concatenation: I m not sure how the 97 is arrived at, so I ll take it as straight text - you BASICALLY need to use concatenation, which does exist as a formula but is easier to use AND SPELL (!) when done as & = 97%/ & text(sum(a:a), $0.00 ) as an example this...
Excel: Formula, cell a2, input sheet
cell a2, input sheet, column c: Kathy, Yep, pretty confusing! I wouldn t try to do this with a single formula. Even if you could figure it out, it d be so whacked that no one could ever decipher it. Here s how I would do it. Let s say that your dates start in cell A2 and the number...
Excel: Formula, dollar figure, lease term
dollar figure, lease term, expiration dates: Teresa, Assume the date rent starts is in A1 and the monthly amount is in A2 in A3 =((Date(year(A1),month(A1)+1,0)-A1)/Day(Date(year(A1),Month(A1)+1,0))) * A2 This excludes the being date, so to add that in if you want it is =((Date(year(A1),month(A1)+1,0)-A1+1)/Day(Date(year(A1),Month(A1)+1,0)))...
Excel: Formula, help windows, dollar figure
help windows, dollar figure, lease term: Teresa, Assume the date rent starts is in A1 and the monthly amount is in A2 in A3 =((Date(year(A1),month(A1)+1,0)-A1)/Day(Date(year(A1),Month(A1)+1,0))) * A2 This excludes the begining date, so to add that in if you want it is =((Date(year(A1),month(A1)+1,0)-A1+1)/Day(Date(year(A1),Month(A1)+1,0)))...
Excel: Formula for Adding numbers sequence, pic id, adding numbers
pic id, adding numbers, excel formula: Philip, Hope you are doing well. You haven t mentioned how you are using it - via Excel formula or via a macro Either way, there is no direct way. The best way is for you to construct the file_name in a loop or something 1. If you are using it in Excel,...
Excel: Formula Array in VBA, string s1, blank row
string s1, blank row, object error: Hello Tom, That was a typo on my part - didn t completely clean up copying your formula - my fault. Thanks for noting it on my rating. Good luck with other experts on future questions. Dim s as String, s1 as String s = _ =SUM(IF(FREQUENCY(MATCH(R[-4]C[7]:R[-1]C[7],R[-4]C[7]:R[-1]C[7],0),MATCH(R[-4]C[7]:R[-1]C[7],R[-4]C[7]:R[-1]C[7],0))...
Excel: Formula Autofill, f4 key, absolute reference
f4 key, absolute reference, b7: You need an ABSOLUTE reference which is $A$1 - which you can do by pressing the F4 key with the cursor on the A1 in the formula bar (or just type the dollars) For reference $A1 would always refer to column A, but variable rows A$1 would always refer...
Excel: Formula for Averages, array formula, array formulas
array formula, array formulas, mouse pointer: Hi Billy, What you need is an array formula. An Array formula is a formula, but instead of pressing the Enter key you need to have your mouse pointer flashing within the formula up at the top of the excel spreadsheet on the formula bar and then press...
Excel: Formula "auto change" when row added, autosum, auto change
autosum, auto change, cell reference: Thanks for the comments. Note: You should click on any row within the table that is in the formula to insert a row, not necessarily the Total row & the new row should be included. ####################################### When using Autosum, if you...
Excel: Formula based on how another cell is formatted., conditional formatting, employee schedules
conditional formatting, employee schedules, jaret: Jaret, There is no way to make a formula based on color. If you really need to do it, I would suggest writing VB code. However, in this instance, I think you need to see whether you can workaround by using a entering Y/N rather than color coding it directly....
Excel: Formula to calculate vacation - USA, douglas m smith, vacation schedule
douglas m smith, vacation schedule, brainbench: Hi Cory, Sorry about the delay. If you build a table with the above values you can retrieve them with VLOOKUP. The table would look like this: 0 0 1 60 2 112 5 136 10 168 15 224 If this table were in cells A1:B6, then you could...
Excel: Formula for calculating sunshine minutes?, sunrise and sunset times, sunrise time
sunrise and sunset times, sunrise time, sunrise sunset: Depends on the EXACT nature of the way you ve entered the times. If A1 has 6:19 AM and B1 has 8:41 PM, then this formula will give the 862 minutes: =60*24*(B1-A1) However, once you enter the formula you will have to reformat it as General or it ll look...
Excel: Formula for cemetery plot layout, douglas m smith, plot layout
douglas m smith, plot layout, feet and inches: Hi Barbara, Here s the basic formula to display inches as feet and inches. Two separate operations are performed to get the feet and inches, and then they are combined together the feet/inches notation. =INT(A1/12)& &MOD(A1,12)& 38 in cell...
Excel: Formula to change cell value color, conditional formatting, cell reference
conditional formatting, cell reference, numerical value: You use conditional formatting! Under the Format Menu, choose Conditional Formatting. A dialogue box appears. Choose [Cell Value Is ] {greater than} 50. You can type 50 or make it a a cell reference. that way if your conditional format value changes...
Excel: Formula for combining data, city state zip, excel 2000
city state zip, excel 2000, copy paste: Assuming the data is columns A, B, & C, and you want the combined data to be in column D, then =A1& &B1& &C1 Copy and paste as far down as you need. Then you copy and paste all of the formulas on top of themselves using Copy, Paste Special As Values....
Excel: Formula for compounding daily interest, effective interest rate, amortization schedule
effective interest rate, amortization schedule, analysis toolpak: If you ve installed and enabled Excel s Analysis ToolPak, there s a function =EFFECT() that will calculate the compound interest for you. If not, you can do it this way: Say the annual interest rate is 5%, so monthly interest rate is 6%/12 or .5%. The...
Excel: Formula to count cells with certain text & other criteria, task numbers, formulas in excel
task numbers, formulas in excel, personnel training: I would create a new column (hidden if necessary) on the Personnel Training Worksheet that concatinates Task Number and GO/NOGO. So if Col B is the Task Number (e.g. 6) and Col D is GO/NOGO, put in Col E the formula =B1&D1 so it d return for instance 6GO...
Excel: Formula to count cells, a101, b90
a101, b90, a100: This should be a start. You might need to fine tune it to get the exact number you want: =if(row() 101,row()-101,100-row()) If you want it to dynamically find the location of the cell with median in it, that would take more work, but I didn t understand...
Excel: Formula Dilema, douglas m smith, column letter
douglas m smith, column letter, row numbers: Hi John, There is a neat function called INDIRECT that will convert strings into a reference. If your sheet name is in cell A1, then this formula will constuct your reference: =INDIRECT($A$1& !$W$9 ) Using it in your formula: =IF(AND($A19=TRUE,INDIRECT($A$1&...
Excel: Formula in Excel, datevalue, error data
datevalue, error data, ogilvy: Assume Jan-06 mean January 2006 and the values are stored as actual dates. =Sumproduct(Data!$D$1:$D$69,--(Month(Data!$E$1:$E$69)=1),--(Year(Data!$E$1:$E$69)=2006),--(Data!$F$1:$F$69= L )) You must look at the same number of rows in each section. Since...
Excel: Formula Excel 2002 EASY, incorrect entries, excel 2002
incorrect entries, excel 2002, iname: Assuming you are counting the number of correct and incorrect entries, the formula would be: NumberOfCorrectEntries/TotalEntries. However, the actual implementation depends on the layout of your spreadsheet. If you want send it to me at rmadsen@iname.com....
Excel: Formula in Excel, vlookup function in excel, blank rows
vlookup function in excel, blank rows, numbers report: A thorough explanation would take longer than I have right now, but I ll try to get you started. I m assuming you have a table of information that you will be looking up a value in - I think this is your REPORT B. This table needs to be set up with no blank...
Excel: Formula for Excel, format cells, current date
format cells, current date, sp3: From the menu, click Format, Cells. Select the Number tab. Select Date and choose any date format. Then choose Custom and type YYYY as the custom format. So, if you have 3/24/2006 in a cell then it will appear as 2006 after applying the format above....
Excel: Formula error, cell b1, cell a1
cell b1, cell a1, conditional formatting: The answer you got would never work as the piece C1 2/29/4 is comparing c1 against 2 divided by 29 divided by 4, not a date. In any case, to see if a cell is blank by a given date, you would need a macro (VBA). Right-click on the excel LOGO near the file menu...
Excel: Formula for excel (office2000), value entries, text entries
value entries, text entries, office2000: If the goal is to just count text entries in a column, you could just use the COUNTA function. =COUNTA(A1:A100) The above would return a count of entries in the column regardless of text or value. If you have multiple text entries in the column and...
Excel: Formula execution, window options, select tools
window options, select tools, apostrophe: From the main menu, select Tools, Options, View. If the box marked Formulas (under Window Options ) is checked, it will cause your sheet to show formulas rather than results, as you ve described. It s not clear to me what else could cause this problem....
Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, cell c1
cell a1, cell c1, text numbers: This would create a HORRIBLE formula, Rose, if it can be done at all. Two suggestions: 1. Split your data up. Select column A and do Data-- Text to Columns and turn it into 3 columns (or more)...and go from there to get your numbers. You can also use x...
Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, math portion
cell a1, math portion, cell c1: Well, that makes things more difficult. The two formulas you will need to get the data (one for the math portion and the other for the ga number) you need for the final formula you already have will both be about as long as your arm - too long for me to write...
Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, split application
cell a1, split application, cell c1: Change Function Getno(rg As Range, no As Integer) As Integer to Function Getno(rg As Range, no As Integer) As Double You mean specifically change 11 to .012? If so, put this in just before End Function: If Getno=11 then Getno=.012 This will change...
Excel: Formula/format to match certain character, question column, sap r 3
question column, sap r 3, character question: This is one UDF that uses regular expressions. I have tried with the data you have provided, and it returns positive matches for all them when setting the chunk size to 6, failing only in the Rocky one when the chuck size is set to 7. To use the function,...
Excel: Formula Help, b10, arrays
b10, arrays, a5: Can it be done? There are so many ways to do it, I m not even sure where to start... Most experts I suspect would recommend a LOOKUP style function. That s a pretty common way to attack it. Although, they d probably tell you to enter values in cells,...
Excel: Formula help pls, calcs, c450
calcs, c450, chars: There wasn t sample data, but it SOUNDS as though you need SUMPRODUCT =SUMPRODUCT(A1:A450,C1:C450) which would multiply A1 by C1, A2 by C2, A3 by C3 etc and display the total result in the cell. You DO need to specify a given range, it will not work...
Excel: Formula help pls, row header, header row
row header, header row, calcs: Thanks for rating and thanks, I appreciate them. Regarding the issue, the #NAME error usually appears when the formula name is not recognized, what version of Excel are you using? And which language? I can assure that the formula exist on Excel 2003 and...
Excel: Formula help pls, button search, calcs
button search, calcs, chars: Deborah - There is such a formula. It s called SUMPRODUCT(), and it does just what you re looking for. Rather than me try to explain it (it would be a long email!), I will refer you to the Insert Function button (it s the Fx button on the formula...
Excel: Formula help for retrieving and calculating specific information, pivot table, taxable items
pivot table, taxable items, value error: It SOUNDS as though you would be best to use the DATA menu, and then Pivot Table and chart report - this would give you the data summarised as you want it - would this work for you? If not, I can probably come up with formulas, but the flexibility of the...
Excel: Formula help, douglas m smith, column letter
douglas m smith, column letter, cell references: Hi Sarah, The formula structure would be: =(60+11)*1.5*1.06 Substitute the cell references for the values to get the actual formula. Since I don t know the specific layout of where the other numbers are, I ll give you general guidance on how to...
Excel: Formula for Interest, format cells, due interest
format cells, due interest, happy new year: Hi Alicia Happy new year to you too!!! Please try the following 1. Type 54000 in A1 2. Type 10% un B1 3. Type letter s date in C1 4. Type Payment s date in D1 5. Compute the number of days the payment was due, type in E1 =D1-(c1+10) 6. Format E1...
Excel: Formula for incremental projected data., cell a2, kudos
cell a2, kudos, weighted average: Diana: I uploaded an example here... http://home.swbell.net/nate-sus/excel/diana-incremental.xls It is slightly more flexible than an IF then formula. It allows you to weight each month (row 2). The formulas then automatically calculate based on...
Excel: Formula inquiry, excel sheet, reagent
excel sheet, reagent, excel 2002: Barrett I hate to say it but some of the logic of that formula is faulty. I can t check it right this moment, I need to hit the road, but I should be able to take another look this afternoon. The true and false segments are the same, which is obviously...
Excel: Formula to Lock a Row/Column, visual basic programming, format cells
visual basic programming, format cells, table wizard: Good morning Ed! 1) I ll start out by saying you can lock cells, rows, columns, and an entire sheet in Excel with Protection . With a selection, choose format- cells- protection and then ensure there is a checkmark in locked . Now click on tools- protection-...
Excel: Formula/macro, help windows, dollar figure
help windows, dollar figure, lease term: Not sure I understand what you want, but assuming you need to know how many days are in a month, then here is one of many ways: =EOMONTH(A1,1) The formula assumes the date, 9/24/05. in your example, is in A1. Format the cell with the formula in it to...
Excel: Formula for matching numbers, array formula, matching numbers
array formula, matching numbers, absolute reference: Simplest solution is to use conditional formatting (format menu, conditional formatting) to shade the cells IF they match the criteria - use the formula option of the conditional formatting box. A more complicated approach would be something along the lines...
Excel: Formula with multiple criteria, multiple cells, array formula, completion times
array formula, completion times, autosum: Hello Rebecca Tonks =LARGE(IF(($A$2:$A$4293= Job1 )*($G$2:$G$4293= Task A ),$E$2:$E$4293),1) LARGE(IF(($A$2:$A$4293= Job1 )*($G$2:$G$4293= Task B ),$E$2:$E$4293),1) entered with Ctrl+Shift+Enter rather than just enter since this is an array formula worked...
Excel: Formula needed please, array formula, vlookup
array formula, vlookup, certificate numbers: The question doesn t quite make sense to me - certainly you can pull in information based on certain conditions - vlookup would do this, as could an array formula - if you can mail me a sample of what you are trying to do it will be easier to give you a working...
Excel: Formula Percent Increase or Decrease, aidan, year end
aidan, year end, previous year: $L253 should be $L$253 if you need to lock in place both column and row. You don t need the SUM =X-Y will give you a value. so =(L258-$L$253)/$L$253 appears to be what you want my email if needed (eg for attachments in any follow up) is...
Excel: Formula Problema, cell a1, sheet1
cell a1, sheet1, number 3: Hi, John. The experts here aren t linked up, so feel free to ask me when you don t get satisfaction elsewhere. Here s an example. Say you ve got 2 workbooks open, called Book2 and Book3. You re putting formulas in Book2, which you want to get data from...
Excel: Formula on percentage, decimal places, ogilvy
decimal places, ogilvy, a2: Michelle, A2: 23.94 B2: 10 C2: =(A2-B2)/A2 will produce 0.582289055973267 format C2 as Percent with the number of decimal places you desire (or none at all) and will display you 58% you can drag fill this formula down the column. -- Regards,...
Excel: Formula problem, cell a2, cell a1
cell a2, cell a1, whole number: Here s an example. You have a number in cell A1, for example 2.7895. In A2, you want to round it to a whole number followed by the words per hour. In cell A2, you d use the formula: =ROUND(A1,0) & per hour which would return: 3 per hour If...
Excel: Formula problem, b5, lower bound
b5, lower bound, ogilvy: Excel will nest 7 deep in xl2003 and earlier. =IF(B5 0, ,IF(B5 =250,0,IF(B5 =500,B5*0.1,IF(B5 =1000,B5*0.015,IF(B5 =1500,B5*0.025,IF(B5 2000,B5*0.3, 2000 )))))) Note that you don t need to check the lower bound as long as it is a progressive test...
Excel: Formula problem, douglas m smith, b19
douglas m smith, b19, brainbench: Hi Dave, The best way to trap this is with the ISNA function. It is used to evaluate if there is an #N/A error: =IF(ISNA(INDEX(B19:C34,Match(B43,B19:B34,0),2)), ,INDEX(B19:C34,Match(B43,B19:B34,0),2)) This will return a if there is an #N/A error....
Excel: Formula QUestion, douglas m smith, conditional formatting
douglas m smith, conditional formatting, format button: Hi Dan, You can do the with conditional formatting. I m assuming that there will only be one entry per row from column J to column BP. Select the cells in column A and then select Conditional Formatting from the Format menu. Select Formula Is from...
Excel: Formula Question, douglas m smith, cell d2
douglas m smith, cell d2, bonus payout: Hi Jerry, This can be done with a look up table. First construct a table like this, for example, in cell D2:E6: 0% $0.00 25% $48.45 50% $47.97 75% $47.50 100% $46.55 If you put the percentage to lookup in cell A1, the formula to retrieve...
Excel: Formula Question, b5, ogilvy
b5, ogilvy, g5: Hello Michelle, You don t indicate what I am to look for in H5. Assume if H5 is empty, then you consider it not paid =if(H5= ,Today()-B5,0) or assume the amount owed is in G5, so if H5 is =G5, then consider it paid =if(H5 =G5,0,Today()-B5) ...
Excel: Formula Question, column c, warm regards
column c, warm regards, perfect solution: Dear Shawn, To my knowledge, SUBTOTAL function will not support IF as additional argument. One suggestion could be, to add column which is combination of Filtered column and indication whether the number is greater than Zero. For instance imagine, (1)...
Excel: Formula Question of dates, cell reference, difference in time
cell reference, difference in time, public holiday: Sorry for the delay in replying - it s been a public holiday weekend here in the UK, and I forgot to update my profile to record that fact. Not totally sure what you are after here, but something like =text(A1, mmmm-yy ) & - & TEXT(DATEVALUE(TEXT(A1,...
Excel: Formula Question regarding Display of Date Ranges, cell reference, difference in time
cell reference, difference in time, 11 october: Hello Weston, Let us say you have Sep-07 in cell A1. Further what is actually stored in A1 is 9/1/2007 (in US date format), so that A1 has an actual date in it. In A2 you could put =TEXT(DATE(YEAR(A$1),MONTH(A$1)+(ROW()-2)*12,DAY(A$1)), mmmm-yy )&...
Excel: Formula Question w/in SUMPRODUCT, array formula, sumproduct
array formula, sumproduct, zero value: Yes, a + is just for addition. The above formula is taking the positive total number of records in column B that have a year 2003, the positive total number of records in column L that have the month of JUL, and the amount in column I and feeding it...
Excel: Formula Question, ms excel 2000, cell b2
ms excel 2000, cell b2, a1: Jeff, I would say that you have 2 options for this. The first is to just use the formula =A1 in any cell where you want that to happen. The other option would be to write or record a macro that made that happen and place a button on the toolbar to repeat that...
Excel: Formula question, david cowling, cell a1
david cowling, cell a1, cell reference: Hi, Dave, This has to do with the process of combining two elements together. The sign is text, but the cell reference is not. (The cell reference needs to be calculated, or translated it into its value.) Whenever you combine elements, you use the...
Excel: Formula question - partial matching of any cell in a range within a string, source cells, array formula
source cells, array formula, text result: In C1 enter this formula =if(isnumber(match( * &A1 * ,B:B,0)), Found , Not Found ) then drag fill C1 down the column to match the last of the A values. that would check each value of A1 against column B and indicate if it is in at least on instance...
Excel: Formula question with times, format cells, military format
format cells, military format, military time: As long as your times are within the same day, you can just use a simple formula like: =B1-A1 to get rid of error values if either field is null you could use: =IF(OR(ISBLANK(A1),ISBLANK(B1)), ,B1-A1) For this to work, you need to make sure all...
Excel: Formula question, ms excell, ms xp
ms excell, ms xp, column names: Hi John, Unfortunately, when using this service extra spaces, coloring and column names are cut out, so I m just guessing which set of values show up in your column I. In any case, the formula that should be placed in I14 is: =COUNTIF(I1:I13, x )/ROWS(I1:I13)...
Excel: Formula never reaches 0, depreciation table, 0000000000000000000
depreciation table, 0000000000000000000: I am not sure exactly why that happens, but if you want to avoid it you may use rounding formulas where you want to make the comprobation. In the case you have run, you can use: =ROUND(A1-0.2,3) IF this precision is enough for you it will avoid the problem....
Excel: Formula reference with multiple workbooks, statistical data analysis, isna
statistical data analysis, isna, excel 2002: Indirect will work if the other workbook is open (it doesn t work to create references to closed workbooks. A1: Subject.1.xls change . . .IF(ISNA(IF(VLOOKUP($C7,[subject.1.xls]Sheet1!$A$1:$H$771,7,FALSE)=0 . . . to . . .IF(ISNA(IF(VLOOKUP($C7,Indirect(...
Excel: Formula result, ms excel 2000, format validation
ms excel 2000, format validation, result format: format won t work because it changes the FORMAT not the VALUE of the cells (I presume you mean conditional format) VALIDATION WOULD work if you put it on the correct cell - if this is applied to the cell that contains the value the formula is going to use,...
Excel: Formula to Sort a list, comma separated values, coloum
comma separated values, coloum, sort key: The way to do this, Marc, is by creating a second column to help out. Let s say that your data is in A1:A8. Follow these steps: 1) If column B is not empty, create a new, blank column at B. 2) In B1, enter the following formula: =MID(A1,SEARCH(...
Excel: Formula to Sort a list, coloum, sort method
coloum, sort method, aidan: is the @ sign contained in the cell, or is it shorthand for a gap between columns? This would make a difference. Secondly, are the lengths always known? IF @ indicates a column break AND the lengths are known, then the SUMIF function would do what you want....
Excel: Formula to Sort a list, coloum, sumif
coloum, sumif, 4 steps: Hi Marc! How are you? Please think about the following: Assuming your data is in A1:A8 1st. Type in B1 =VALUE(MID(A1,8,6)) 2nd Type in C1 =VALUE(MID(A1,1,5)) 3rd Copy B1:C1 to B2:C8 4th Type in C1 =SUMIF(B1:C8,B1,C1:C8) 5th The data-sort 6th...
Excel: Formula to Sort a list, coloum, sort feature
coloum, sort feature, e numbers: Marc I suggest that you separate the data into two columns (I.E. numbers on the first column and length in the second) (sounds like it may be in this format now) then use the data/sort feature (to sort from top to bottom in length) then use the subtotal...
Excel: Formula to Sort a list, coloum, row header
coloum, row header, field settings: Marc, I hate to say it but, wake up man! Sort of just kidding. Specifically what you are asking can t be done with formulas. However, you can use a pivot table to summarize that data instantly, though it would have to be reversed, in that it would show...
Excel: Formula to Sort a list, data cells, coloum
data cells, coloum, cell a2: Hi Marc, This is going to be a multi-part solution. First, we will need to separate out the quantities and lengths. If your combined numbers (above) start in cell A2, the formulas for cells B2 and C2 are: B2: =VALUE(LEFT(A2,FIND( @ ,A2)-2)) C2:...
Excel: If Then Formula's, logical number, last thoughts
logical number, last thoughts, hyper link: I am not so sure of what you want to achieve, what I use to do when I have to extract data from one sheet to the other is to use the formulas MATCH and INDIRECT, in the following way: First, I use an additional column to track the rows in the original worksheet...
Excel: Formula's, array formula, array function
array formula, array function, lotus 1 2 3: Assuming: your data is in A1:B20, program codes are in A, payment type codes are in B, both program codes and payment types are entered as values, you want to count how many records in that range have a program code of 1 AND ALSO have a...
Excel: Formula's, cell a2, cell a1
cell a2, cell a1, mar c: Regina I can t give you a specific formula because I don t know what you data looks like, but myabe I can give you some ideas. First the running total If you have a total that is calculated form the entries to date, then its just a matter of adding...
Excel: Formula to spread costs based on start date and end date, average salary, cell references
average salary, cell references, spreadsheet: Russ: The simplest way is to divide your annual amount by 52 and apply that amount to each week. I uploaded a sample spreadsheet demonstrating this here: http://home.swbell.net/nate-sus/excel/weekly.xls NOTE - Each cell references the annual amount...
Excel: Formula in Time, finish time, time functions
finish time, time functions, time cell: The formula is missing the last part, that correspond to what happens if the statement is false. You can try with this one: =IF(M9 =TIME(8,0,0),M9+TIME(0,10,0),M9) In this case, if the time is less than 8:00 AM, it will show the time itself (the content...
Excel: Formula for time, minute slots, cell a1
minute slots, cell a1, time slot: crystal If you have 8:00 in cell A1 this formula will add 30 minutes to it, drag the formula cell down (click and hold the mouse button down on the bottom right of the cell with the formula in it, it will show a little black box in that corner) and it will...
Excel: Formula or VBA for this ???, consolidated budget, code framework
consolidated budget, code framework, sorry in advance: I m guessing that all files are in a common area, and only those files are in that area - if so, use this code snippet t fs = Application.FileSearch With fs .LookIn = C:My Documents .FileName = cmd* If .Execute(SortBy:=msoSortbyFileName,...
Excel: Formula for getting values from other sheet, douglas m smith, mumbai india
douglas m smith, mumbai india, brainbench: Hi Sanjay, If the part numbers are unique in the lists, MATCH and INDEX can be used. On sheet one, the following formula would look for a part number match on sheet two and then return the ajacent value: =INDEX(Sheet2!$B$1:$B$500,(MATCH(A1,Sheet2!$A$1:$A$500,0),1)...
Excel: Formula not working, cell b1, cell a1
cell b1, cell a1, conditional formatting: A macro is the only way an alarm or pop up will notifiy you in this case. You could add an additonal condition in the IF statement to check to see if todays date is after Mar 1 AND the cell is empty and if so have the formula return the warning message in...
Excel: Formula works part of the time, analysis toolpak, excel 2000
analysis toolpak, excel 2000, network server: The formula you are using is in the Analysis toolpak, and therefore ONLY works if the analysis toolpak (an add-in) is installed. You can put a macro in place to check for this and load it, but it is a simple fix - it s a one-off on each machine, they just...
Excel: Formula in another worksheet, minus signs, boolean values
minus signs, boolean values, k2: Hello Jane {=SUM(( Sheet 1 !E2:E1505=E3)*( Sheet 1 !K2:K1505=K5))} This assumes the sheet name is actually Sheet 1 with a space before the 1. It if is just Sheet1 with no spaces, you don t need the single quotes. {=SUM((Sheet1!E2:E1505=E3)*(Sheet1!K2:K1505=K5))}...
Excel: Formula, hth, excel 2000
hth, excel 2000, syntax: Hi Dan, You need to use the linking formula syntax. Here s what the syntax looks like if the file with the lookup list is currently open: =VLOOKUP(A26, [HouseLoan.xls]Bank Monthly Transfers !$A$27:$B$36,1,FALSE) Here s what it looks like if the file...
Excel: FormulaArray property, array formula, proper syntax
array formula, proper syntax, compilation error: Yes but you still have syntax problems. Why do you think you need a double quote BEFORE R1C1? Diuble quotes are only needed to add the liereal text of the VBA line to the variable AND then for the proper styntax for the VBA line itself Remember the double...
Excel: Formulae, target cells, random function
target cells, random function, random value: Kay, XeonFlux = ecks-e-on-flucks There are two ways to deal with this that come to mind. 1. After you make the random number in the cell, copy it and paste it as values in the same cell. The downside to this is that you ll have to do it each...
Excel: Formulae, b9, formulae
b9, formulae, c10: Harsh, In sheet 1, lets put the following values in these cells, A2 - 0% B2 - 5% A3 - 25% B3 - 8% A4 - 50% B4 - 10% A5 - 75% B5 - 12% A6 - 100% B6 - 15% B9 - where you enter your weekly target (2100) B10 - Achieved amount (e.g. 3675...
Excel: Formulae Protection, douglas m smith, password passwords
douglas m smith, password passwords, cell a1: Hi Martin, Protection can be set for each cell. It fact, all your cells are probably set to be locked. To check / set this, select Cells under the Format menu and then select the Protection tab. If Locked is checked, then the cells protection has been...
Excel: Formulae, douglas m smith, loop statements
douglas m smith, loop statements, loop statement: Hi Kay, Probably the best way to handle this is with a VBA Do loop. Something like: Do Until Result = False Code that tests your conditions Variable Result stores True or False based on the tests Loop This type of loop runs until the tests...
Excel: Formular to count number of entries in a row, douglas m smith, counta
douglas m smith, counta, brainbench: Hi Jake, To count cells with just numbers, the COUNT function will work. To count cell with both numbers and text, use COUNTA. In your example: =COUNT(A1:E1) would be 3 =COUNTA(A1:E1) would be 5 =COUNT(A2:E2) would be 0 =COUNTA(A2:E2) would be...
Excel: Formulas, dollar values, fortunately
dollar values, fortunately: Hi, Ken, Fortunately, AVERAGE is a function that will indeed operate across worksheets (as will SUM, and some others). Let s say that your worksheets are named Sheet1 through Sheet16. The cell you want to average on each sheet is F14. The formula would...
Excel: Formulas, aidan, many thanks
aidan, many thanks, tick: Not sure I follow the question - if you have put a formula in a cell and pressed Enter, or clicked on the tick in the formula bar, the formula would then be locked in place. Obviously, if values in cells that the formulas reference change, the result in the...
Excel: Formulas, exact details, many thanks
exact details, many thanks, cells: I m not sure I follow what s happening. But try pressing the Esc key when whatever it is that s happening happens. Maybe you could describe exact details, like what cell you enter =E54-E53 in and when this shows up again (in what cell are you typing what?...
Excel: Formulas, pivot tables, pivot table
pivot tables, pivot table, sheet1: There are a few options available for this problem, the best solution would be to use a Pivot table to show a count of what joe has or any other options. If you don t know about pivot tables have a look at this link otherwise get back to me (I will be out...
Excel: Formulas, input formulas, toolbar tools
input formulas, toolbar tools, spreadsheet: Joon When you say the formula doesn t work anymore I m not sure if you mean it isn t calculating at all, or it calculating incorrectly, etc. etc. Try this --copy the spreadsheet to another workbook and see if it will calculate there. Check your options...
Excel: Formulas, variable number, g20
variable number, g20, lpn: Assuming the other cells are A3 & A4: =C1*IF(AND(A3= RN ,A4= Medicaid ),2,IF(AND(A3= lpn ,A4= medicaid ),3,IF(AND(A3= RN ,A4= waiver ),4,IF(AND(A3= XX ,A4=xyz),5))))+D1 etc. but this way you re limited to 7 combinations. For unlimited combinations, it...
Excel: Formulas, many different ways, macro security
many different ways, macro security, c7: Hi Gary, There are many different ways to accomplish this depending on the functionality you want. Here are several choices: 1. Simply eliminate the formulas of the two cells, leaving only the values behind. You can do this manually by copying each cell,...
Excel: Formulas between 2 spreadsheets, target value, target address
target value, target address, cell b1: Hi Dan, It is possible with a formula but gets more complicated than a macro. You could use something like this macro. First create a Blank workbook named Book1.xls and a workbook named Book2.xls keep them both open. Switch to Book1.xls copy the following...
Excel: Formulas, microsoft office xp, home office computer
microsoft office xp, home office computer, target address: There s no such formula. A formula returns a value to the cell containing the formula. However, you can do this in VBA. Press Alt/F11, use Insert Module, enter this: Sub MoveA1ToA2() Range( A2 ).value = range( A1 ).value End Sub But this has nothing...
Excel: Formulas, microsoft office xp, colorindex
microsoft office xp, colorindex, red cells: Hi Alma, I am not sure if you are familiar with user-defined functions, but this is how I will answer this question. You need to follow these steps: 1) In Excel, click on Tools on the top menu, then Macros, then Visual Basic Editor. 2) When Visual...
Excel: Formulas, douglas m smith, t score
douglas m smith, t score, brainbench: Hi Denine, I m not totally clear on your situation but I see basically what you are trying to do. You are on the right track logically. If you use the COUNTIF function, it should solve your problem. For example, the following formula count the numbers...
Excel: Formulas, dollar change, comparison sheet
dollar change, comparison sheet, blank cell: Lenora Ok column D should be % of difference between col B and col C. I assume you want this to be % increase from 04 to 05. By running total I m guessing that you want it to change each time you change some of the sales numbers, but I m not sure what...
Excel: Formulas, vba macros, douglas m smith
vba macros, douglas m smith, john walkenbach: Hi Pimp, I would recommend you focus on functions, formulas, and non-macro features (conditional formating, data validation, text to columns, etc.) of Excel before you get heavy into VBA macros. A great book for tips and techniques: http://www.iil.com/iil_shortdescr.asp?sku=PT248&ld=home1005...
Excel: Formulas, douglas m smith, cell b3
douglas m smith, cell b3, cell b2: Hi Jason, My first guess is that the calculation mode is set to manual. Press F9 to recalculation. Tools / Options / Calculation / Automatic to turn it back on. My second guess is that formula references might be absolute instead of relative. Example:...
Excel: Formulas not calculating, littlewing, gmail
littlewing, gmail, excel spreadsheet: Hi again Go into tools options and calculation and see if the manual calculation is selected, if it is then use automatic. I am sure you have looked at this but you never know. If this doesn t work then can you send the file to richard.littlewing@gmail.com...
Excel: Formulas and Text, microsoft excel 2000, five dollars
microsoft excel 2000, five dollars, watermark: It sounds like you re trying to recreate a watermark in a cell, am I right? It s a great idea, but you can t have two different items in the same cell. You can add a picture or watermark to the background of the entire sheet, but not a single cell. Even...
Excel: Formulas, target cell, zip code area
target cell, zip code area, vlookup: Assuming that by database you mean list of codes and names , then the easiest solution is vlookup - which is a question I ve been asked almost more than any other and have therefore created a webpage explaining this - it s at http://www.aidanheritage.co.uk/excel/...
Excel: Formulating a correct formula, gross pay, belveder
gross pay, belveder, s gross: If the tax info is on Sheet1 and the value looked up is on another sheet, you can use: =(A6-INDEX(Sheet1!3:3,MATCH(A6,Sheet1!A3:D3)))*INDEX(Sheet1!2:2,MATCH(A6,Sheet1!A3:D3))+INDEX(Sheet1!1:1,MATCH(A6,Sheet1!A3:D3)) Your table wasn t set up right for a...
Excel: Formules in VBA, traditional formulations, collom
traditional formulations, collom, misstake: Aad, When you say put the calculations in VBA I m going to assume you want to be able to rewrite your formulas with code so that if someone deletes it you can replace with VBA. This is can be done but you may have to educate yourself with the R1C1 formulation...
Excel: Fraction trouble, douglas m smith, cell a1
douglas m smith, cell a1, cell b1: Hi Barrett, I don t think you can force the fractions from simplifying but there is a workaround. If A1 contains your total tests and B1 contains the positive count, the following formula would do the trick: =B1& / &A1 This simple version will work...
Excel: Free Chart Object from Cell References, dim arr, cell references
dim arr, cell references, macro code: Here s code which will work for one series - you can adjust for the others. Run DoFormula. Sub DoFormula() ActiveSheet.ChartObjects(1).Activate xvals = buildformula(Sheets( Area2 ).Range( B25:U25 )) vals = buildformula(Sheets( Area1 ).Range(...
Excel: Free Chart Objects From Cell References ?, cell references, macro code
cell references, macro code, b85: Hey Ken, Sorry about the delay in answering your question..this was a tough one! I think this will solve your problem: One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the Shift key, and choose Edit,...
Excel: Freeze the user form, gyula
gyula: Try to copy the Layout event into your Userform code: Private Sub UserForm_Layout() Application.ScreenUpdating = False Me.Left = 100 Me.Top = 100 Application.ScreenUpdating = True End Sub Change the left and top values to what you...
Excel: Freezing cells, freezing cells, empty cells
freezing cells, empty cells, data validation: lynne I don t know of a way to lock the individual cells, however two things usually work for me 1--hide the cells by hiding the columns or rows, 2--use data validation- and put a note in that lets the user know that they are not to enter any info...
Excel: Freezing panes across tabs, panes, tabs
panes, tabs, all sorts: Hi Nick, Unfortunately, I think you re going to have to do them individually. You can do all sorts of things by highlighting multiple tabs and making the same change across all of them, but freezing panes doesn t appear to be one of them. Sorry, ...
Excel: Frequency formula HELP!!!!, frequency formula, data names
frequency formula, data names, correct values: Hi Lena, Yeah just send me the file to cloverken@yahoo.com means does not equal means nothing. Therefore means does not equal nothing or blank cells in other words the formula will ignore blank cells. Also the question is not how many...
Excel: Frezzing a page, windows xp home, r50
windows xp home, r50, excel 2000: Hi, Jim, Unless you re willing to write a macro to make this happen, I would suggest simply hiding the unavailable rows and columns. Let s say that you only want users to see K10:R50. You can hide columns A:J and S:IV, and hide rows 1:9 and 51 to the...
Excel: Full Name in AFirst Name..., street suffix, mailing labels
street suffix, mailing labels, secondary address: OK, now you have TWO questions. First one re how to make mailing labels and the 2nd one is a new one I don t understand -- why not just delete the columns you indicated? Send me your email address and I can send a sample of how to make mailing labels from...
Excel: Function, excell spreadsheet, acomplishment
excell spreadsheet, acomplishment, change colors: Hi Riordan How are you? Please do the following 1. Type in A6 =Now() 2. Type in B6 01/31/07 3. Select B6 4. Format - Conditional formatting select from left to right Cell Value is Less than =$A$6 Press Format Select Tab Font Change color...
Excel: Function, array formula, array formulas
array formula, array formulas, horizontal array: I am not good at statistics, so I don t know if the results make sense, but this is how you usually work with array formulas: In this case, first type this figures in some place in the worksheet, one number per cell. For the example, the x values will be...
Excel: IF, Then Function, array formula, array formulas
array formula, array formulas, header rows: Hi Apollo! I think I have a good idea of what you are looking for. Can you review this spreadsheet and see if it meets your expectations? http://home.swbell.net/nate-sus/excel/prov.xls The formulas to accomplish the task you requested are fairly...
Excel: IF Function, decimal fraction, percentage value
decimal fraction, percentage value, d6: Hi Tai, Yes, the formula in cell E6 would be: =IF(D6 300,D6*0.1, ) This displays a null string (which will appear as blank) if D6 is less than or equal to 300. In your problem statement you didn t actually specify what you wanted to happen if D6...
Excel: Function:Address, e24, e15
e24, e15, e50: I had a typo - the funcion s name is INDIRECT, no IDIRECT. =COUNTIF(INDIRECT(A1), =W ) Not sure if this will work but try it anyhow. Create a formula in A1, for example, that builds the address you need, something like: =ADDRESS(I12-9,5,4)& : &ADDRESS(I12,5,4)...
Excel: Function to calculate age, format cells, number 0
format cells, number 0, decimal places: Hi Joe, How are you? If you want to compute the age in years: A5: birth date e.g. 15/03/1970 B5: =today() C5: =year(B5)-year(A5) Select C5 Format- cells- Number 0 decimal places If you want to compute the age in days D5: B5-A5 Select D5 Format-...
Excel: Function call from other modul, xlstart folder, xls workbook
xlstart folder, xls workbook, xls file: Hi Markus, You can copy your custom function to a personal.xls workbook. This workbook is available everytime you open up excel and for any workbook you are working on. If you have not done this before you will need to create a Personal.xls file. To do...
Excel: Function/Formula/Macro (Extract Names and paste to a new sheet), dropdown arrow, copy switch
dropdown arrow, copy switch, namex: You can list the names you want to check, say in M1:M10. Your formula, say in G2 (row 1 being column headers), is =IF(ISNA(MATCH(D2,M$1:M$10,0)),FALSE,TRUE) and fill down. Now filter the list (A1:G100, for example) (data/filter/autofilter) and in the G s...
Excel: Function/Formula/Macro (Extract Names and paste to a new sheet), array formula, blank cells
array formula, blank cells, namex: Okay...based on my interpretation it appears you have two lists. List A contains a bunch of names. List B is shorter and contains another list of names. You want to look at list B and export those names that appear in List A to a new list (List C). Something...
Excel: Function Question, logical test, true value
logical test, true value, andrea: Ben - You can do this. I will give you the function, you will need to change things to customize it to your needs. It is a basic If statement, except you need an And statement to allow you to check the range: =IF(AND(A1 =5,A1 =10), true ,0) Hope...
Excel: Function Question, logical test, excel functions
logical test, excel functions, true value: Hi Ben I dont do Excel functions as my profile says but I could write some code for you which would perform the same task. Let me know if you would like me to do that for you. You may wish to email your spreadsheet ; limbic_lesion@hotmail.com...
Excel: Function Question Again, catalog numbers, 3333333333
catalog numbers, 3333333333, 1111111111: You will need to create a unique listing of the catalog numbers first ONLY IF you don t want each catalog number s total repeated. You can use Data. Filter, Advanced Filter for that. To create a total of each catalog s total then: =SUMIF($A$1:$B$10,C1,$B$1:$B$10)...
Excel: Function Range, logical test, true value
logical test, true value, a2: Ben, I hope my understanding is correct. (1) Let us say A1 has a value between 1 to 40. (2) In A2 you want OK if the value is between 5 & 10 (Both inclusive). Else A2 will be 0 (Zero) In which case formula in A2 will be - =IF(AND(A1 =5,A1 =40), OK...
Excel: Function to Search cells, cell a1, excel sheet
cell a1, excel sheet, parfums: Here s a formula that says: If the string eau is found in A1, display eau-de-parfums , otherwise, if the string lotion is found in A1, display body-lotion , if neither is found, display a blank: =IF(ISERR(SEARCH( eau ,A1)),IF(ISERR(SEARCH( lotion...
Excel: Function that can search text and then average an adjacent column, blank rows, countif
blank rows, countif, adding new records: try this assuming your data is from =SUMIF(A1:A10, Device: name2 ,B1:B10)/COUNTIF(A1:B10, Device: name2 ) to get the average of the devices labeled Device: name2 Expand the range in the formula to include blank rows below the data now where...
Excel: Function using IF and COUNT, h12, g100
h12, g100, c3000: Hi Bob How are you? How is this 1. Write the bin range 59, 100,159,200,259 in G12:G100 in G12:G200 2. Write the numbers you want to count in C1:C3000 4. select H12:H200 3. Type in H12 =FREQUENCY(C1:C18,G12:G21) 4. Press Ctrl Shift Enter. ...
Excel: If Function, column c, several thousand
column c, several thousand, b2: Steve, I don t have the exact fix for you, but it should work OK. The formula would look something like this: =IF(VLOOKUP(A1,C:C,1,FALSE) 0, YES , NO ) and would need to be adjacent to all cells in column A, so just drag it to the bottom of the data. It...
Excel: Functions, square foot basis, estimate form
square foot basis, estimate form, exact price: I would use vlookup to do this, but it requires that the database is properly formatted - see my website at www.aidanheritage.co.uk/Excel for an example. You may wish to send me the file to be able to give you a specific working example - if so, my address...
Excel: Functions, cell a1, double quotations
cell a1, double quotations, cell a2: Hi Mansoor, The IF function evaluates a condition and returns a value you specify. For instance. If you have the value 1 in cell A1 and in cell A2 you type the formula =IF(A1=1, Red , Blue ) The value in cell A2 will return Red if there is a 1...
Excel: IF Functions, spredsheet, representitive
spredsheet, representitive, match: Mike, The problem is with the MATCH function. It returns an error if the lookup value isn t found in the look up array. In other words, if Entry isn t found in B10 an error (#N/A) is returned. That causes the rest of the equation to error out if the...
Excel: If Functions, excel 97, test result
excel 97, test result, many thanks: Yes, you can nest up to 7 levels of bracket - but I m SLIGHTLY puzzled in that a number between 1000 and 500 is already handled in the current test and would evaluate to 6 - it s the IF(A1 500,6 part of the formula the basic pricinciple is =if(test,result...