Home > Excel Help Portal > Using Excel Formulas

<<  (Page 5)  Previous              Next  (Page 7)  >>

Using Excel Formulas

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to use Microsoft
Excel formulas.

251. Increment a Hour/Minute String Keywords: Formulas, Links, Printing, VBA, Macro

 
252. Extract Info from Text File Option Explicit Public xR, zZ As Long,... Keywords: Formulas, Links, VBA, File Name, Files, Text Files

 
253. Limitations of the Break Links Command Microsoft Excel 2002 includes a new feature that allows you to break external links within a workbook; however, the Break Links command cannot break the following types of... KB#291984. Keywords: Links, Defined Name

 
254. Sum the largest values in a range Q. I need to calculate the sum of the three largest values in a range of 100 cells. The range isn't sorted, so I can't use a SUM function. Do you have any suggestions about how I could handle this problem? Excel's LARGE function returns the nth-largest value in a range, in which n is the function's... Keywords: Charts, SERIES, Format, Arrays, Formulas, Printing, LARGE, ROW

 
255. Changing #N/A to "Your Response" text Vlookup is a powerful tool. Sometimes, if an exact match is not found, a value other than the standard #N/A is desirable. For example, if your look up range (table_array) is A1:C10, the formula VLOOKUP("cat",$A$1:$C$10,3,FALSE)will return #N/A if cat is not found in A1:A10. If instead you wanted to... Keywords: Formulas, VBA, Web, VLOOKUP

 
256. Compare List and Insert if Not Found This thing skims thru the list and if a code is not found, it makes a row and inserts it. The NumbAddits is used in another process to determine where to put totals. KLM = UBound(AbbrevArray) 'How many Abbrevs BOAC = UBound(LongNameArray) 'How many names Open DataPath & "JobLists.TXT" For Output As... Keywords: Arrays, Formulas, Links, Printing, VBA

 
257. OverPunch Code -106.68 65.00 -215.55 -27006 -32490 =IF(B19 The second function is actually the first one I wrote. Later I wrote newPunch to use a self-contained... Keywords: SERIES, Arrays, Formulas, Defined Name, FIND

 
258. Count Occurences in String My first reaction was, "it can’t be done!, and then I figured out how to do it. Option Explicit Public daSting As String, Z As Long, daRow As Long Public stringLen, daAnsw,... Keywords: Formulas, Links, VBA

 
259. Changing grades (in numeric format) to Letter Grades John D = 83 which is a B-John D2= 85 B+ and so on a so forth."The solution is simple. Create a Table that looks something like this:0 F60 D70 C80 B90 AWhere you put the LOWER limit of each range. For example, F goes from 0 to 59, so you put a 0 and next to it, an "F". After this, select this Table,... Keywords: Formulas, VBA, Web, LOWER, VLOOKUP

 
260. Adding every other cell in a column etc.or this one=SUM(A1,A3,A5,A7...)=SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))Remember, this is a CSE Formula, to enter it you must press at the same time Control Shift Enter, instead of just Enter as regular formulas.You can also use this... Keywords: Formulas, VBA, Web, MOD, ROW

 
261. Thai Bhat currency Keywords: Dates, Custom Number Formats, Formulas, Links, Files, Web, Comments, BAHTTEXT

 
262. Perform math operations without formulas Spreadsheets, by their nature, rely on formulas to manipulate numbers. But creating formulas isn't always the most efficient way to modify a range of values. For example, suppose that you have a worksheet containing a column of prices for various products, and you need to increase all prices by 5... Keywords: Add-In, Charts, Sorting, Download, Format, Formulas, Printing

 
263. Multiple Page Numbers The line for “Second” is the only thing to change as sheets are added or removed. Makes pretty footers with “Page 2 of 15”,... Keywords: Formulas, Links, Printing, VBA, Setup, Window

 
264. Perform two-way table lookups All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine the tax rate for a given income amount. The lookup functions in... Keywords: Charts, Sales, Format, Formulas, Printing, Natural language, VLOOKUP, INDEX, MATCH

 
265. How to calculate the nth Root of a Number in Excel 2000 This step-by-step article shows you how to find the nth root of a number in Microsoft Excel 2000. To find the nth root of a number, raise that number to the power of 1/n. For example, to find the cube root of 8 in a cell, type the following:... KB#271143. Keywords: Formulas

 
266. Removing text-string characters Mike asks "Is there any way I can simply remove certain characters from a text string. For example, I have a post code BN19 4GX and I want to remove all numeric charaters to leave BN GX."This is easier using VBA.Go to the VB Editor (Alt + F11), go to Insert, Module. Paste this code in... Keywords: Formulas, VBA, Web

 
267. Add today's entries to the next empty row on a master sheet Good question. Let's say the values are in cells C10 and E12 of Sheet1 and you want them appended to the next row in Sheet2. The real trick here is the code to find the next row on Sheet2. Starting at the last row in Sheet2 (A65536) and then using .End(xlup) will find the last row with data in... Keywords: Arrays, Macro, VBA, Web

 
268. Unlink a chart series from its data range Normally, an Excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data... Keywords: Charts, Plot, SERIES, Format, Arrays, Formulas, Printing

 
269. CountIf or Sumif with two conditions MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: Criteria, Years, Arrays, Formulas, VBA, HTML, Web, COUNTIF

 
270. Finding an average between two data sets something like a Sumif function but to calculate the median. Is there a way to do this?"This is an issue that calls one of the great things in Excel: Array formulas (Or CSE Formulae, as refered here at MrExcel.com, check this tip for hints on CSE Formulae). Let's assume that the Job codes are in... Keywords: Arrays, Formulas, VBA, Web, MEDIAN

 
271. Keeping a running total in an adjacent column If the data starts in Row 2, then put this formula in B2 and drag down !=SUM($A$2:$A2) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness... Keywords: Formulas, VBA, Web

 
272. Determine Years, Months, & Days between 2 Dates The next challenge is that one of these friends says that a tombstone says, “Died 10 October 1922 aged 50 years, 100 days”. What was the date of birth? Did the counter account for leap years? Excel cannot handle a date before 1900. I made one minor change because I always use ‘Option Explicit’ and... Keywords: Dates, Days, Months, Years, Formulas, Links, VBA

 
273. Automatically add a character in a cell For example, I put 12345 into cell A1. Then in cell B1, I want it to automatically say *12345* Ive tried doing a formula in cell B1 like ="*"=B1"*" or =("*"(=B1)"*") but i cant think or a way to do it. The reason im trying to do this is because im trying to make a bar code and it needs a * at the... Keywords: Formulas, VBA, Web

 
274. Color Unlocked Cells Option Explicit Public IteM As Variant, SheetR As Integer, DdD As Integer, SheetC as... Keywords: Colors, Fonts, Formulas, Links, Password, VBA, Macro, IS

 
275. Trap Those Errors See also my example for skipping past errors and simply writing them to a log for later retrieval. Module is already in progress On Error GoTo ErrHandler 'Tell VBA to use your trapping routine Module continues then Selection.Value = 123 Exit Sub 'You must exit the sub or the 'Error Handler is... Keywords: Errors, Formulas, Links, VBA, Macro, Object, Error Message

 
276. Custom Dialogs ------- Print List chosen from... Keywords: Formulas, Links, Printing, VBA, Object

 
277. Change cell values using Paste Special Q. I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices? Excel provides two ways to accomplish this. The "traditional" technique goes something like this: The other, more efficient approach also uses the Paste... Keywords: Add-In, Charts, Format, Formulas, Printing

 
278. Returning specific data on examining a range Assuming this data is in A1:A4{"Juan";"Pablo";"Gonzalez";"Juan Pablo"}And you want to test for the existance of "Juan", (Located in C1), you could use, for example:{=IF(LEN(C1)*OR(ISNUMBER(SEARCH("*"&C1&"*",A1:A4))),"Exists","Doesn't... Keywords: Formulas, VBA, Web

 
279. Hiding a range depending upon user selectons Thank you"Being a Control checkbox, you would have code that looks like... Keywords: Errors, VBA, Web

 
280. Button Editor Addin This add-in allows you to edit button images....To install the add-in use the Tools Add-ins menu and then browse for the ButtonEditor.xla file. This will add a new menu item to the Tools... Keywords: Add-In, Links, VBA, Toolbar

 
281. Excel Code Examples Several of my genealogy friends had asked for a simple formula to determine the number of days between 2 dates. I was able to do years and days but months escaped me. Finally someone came through. The next challenge is that one of these friends says that a tombstone says, “Died 10 October 1922 aged... Keywords: Dates, Days, Months, Years, Formulas, VBA

 
282. Trapping VBA Errors {Part 2} Let’s say that you are getting errors but you know there are errors and do not want to be reminded. Here is a little ditty that I wrote that still traps the errors but writes them to a Log file for later retreival. Please note: This is a hypothetical macro I wrote to prove my code. It always... Keywords: Errors, Printing, VBA, Macro

 
283. Learn How to Add Running Totals to a Worksheet You can add a running total to a single cell or a column of data. Includes a visual basic macro and example formulas. Excel provides several methods for creating running totals.You can use a worksheet function like =SUM($A$1:A1) where the values to total are in column A and the formula is copied... Keywords: Download, Format, Formulas, Macro, VBA

 
284. Returning Zero instead of #N/A One common issue I face is "How can I have VLOOKUP return a 0 or a empty string "" instead of #N/A! ?"Let's assume that the current formula is=VLOOKUP(A1,$B$2:$D$100,3,False)or more condensed=VLOOKUP(A1,$B$2:$D$100,3,0)one approach to do this is as follows=IF(ISNA(VLOOKUP(A1,$B$2:$D$100,3,0)),””,... Keywords: Formulas, VBA, Web, VLOOKUP

 
285. Using Ubound For years, I did not use arrays because I did not understand them. Since so much of what I get called on to do is parsing text files, I place the parsing elements in arrays and only have one place to look for changes. ChgOarRay = Array(5, 13, 26, 41) ChgoCT = Array(7, 8, 13, 14) Line Input #1,... Keywords: Arrays, Formulas, Links, VBA

 
286. Referencing a sheet indirectly Q. My Excel workbook has a sheet for each month, named January, February, and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is: =SUM(February!F1:F10). Is there any way that I can enter the month name into a cell on my... Keywords: Charts, Sorting, Format, Formulas, Printing, INDIRECT

 
287. Add a percentage to all cells in a range MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: VBA, Web

 
288. Counting distinct entries in a range Q. Can I write a formula that returns the number of distinct entries in a range? First, let's clarify the question. We're hunting for a formula that, given the range that contains the values 100, 99, 98, 100, 98, 100, 98, would return 3. In other words, this range contains three different values,... Keywords: Charts, Format, Arrays, Errors, Formulas, Printing

 
289. Trapping Errors This is the conventional way of trapping errors as your VBA code runs. This method always displays a dialog box with the error message. As I run my code and find out how to handle each error, I change the code to tell me what I should have done. See also my example for skipping past errors and... Keywords: Errors, VBA, Macro, Object, Error Message

 
290. Change Case to Mixed There is a routine that adds a space after MC and changes an O' to O’. The first makes the name look correct and the second is just my housekeeping. If the space is not added, MCCULLOUGH becomes Mccullough; with this Mc Cullough. I would like to fix the DEs but there is no hard fast... Keywords: Formulas, Links, VBA, UPPER

 
291. Increment a Counter everytime a Userform is opened In the Workbook module (Or, if it’s a Userform, in the Initialize event of that Userform) put the... Keywords: Formulas, Macro, VBA, Web

 
292. Add a Single Quote After talking to him awhile, I discovered he did not know what he really wanted but he needed to convert 5,000 Social Security numbers and put a tick (') mark in front of every one of them. This piece of code ran in about 9... Keywords: Formulas, Links, VBA

 
293. Broken lines The breaks in the line are masked by the use of a dummy data series. This dummy data series is formatted with the same colour as the plot... Keywords: Plot, SERIES, Colors, Formulas, Links, VBA, NA

 
294. Write Contents of Multi Choice Box My example may be a little hard to follow because I had to parse the data into 2 fields as it was written to cells. For U = 0 To usrCreateNew.cmbTEaccts.ListCount - 1 'Items in Multi-select If usrCreateNew.cmbTEaccts.Selected(U) = True Then 'Items selected Cells(DaRow + G, 1) = _... Keywords: Formulas, Links, VBA, Parse

 
295. Excel -- Named Ranges Name Box Create a Dynamic Range You can use a dynamic formula to define a range. As new items are added, the range will automatically... Keywords: Formulas, Defined Name, OFFSET, COUNTA

 
296. Move the Minus Sign to Front Keywords: Formulas, Links, VBA

 
297. Convert Text to Numbers Keywords: Format, Formulas, VBA

 
298. Four Methods of Adding Totals to a List Example of using subtotals, array formulas and database formulas to add totals to a list of data on a worksheet You can add totals to a list of data on a worksheet in many ways. You can use an AutoFilter and sort and subtotal the data. Excel provides array formulas that can be used to process an... Keywords: AutoFilter, Criteria, Sorting, Download, Format, Arrays, Formulas, PivotTables

 
299. Add Data to Next Row Keywords: Charts, Formulas, Links, VBA

 
300. Data Validation Demonstration of custom dialog box procedure that checks for error and prompts user for correct input. A Visual Basic for Applications procedure and a custom dialog box can be used to prompt user to correct input errors. If you assign a Visual Basic for Applications procedure to the frame of the... Keywords: Download, Format, Errors, VBA, Validation

 

<< (Page 5)  Previous              Next  (Page 7) >>