Home > Excel Help Portal > Using Excel Formulas

<<  (Page 6)  Previous              Next  (Page 1)  >>

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.

301. Change a Cell Value without Changing the Formula Well…. No. I mean, if you have in a cell=1+2The result will Always be 3. You can’t change the value to “4” without replacing or changing the formula. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not... Keywords: Formulas, VBA, Web

 
302. Delete single legend entry With charting it is sometimes necessary to use additional data series in order to achieve the effect in a chart you want. See my Label last point... Keywords: Charts, Legend, SERIES, Links, VBA

 
303. IRR, XIRR, NPV, XNPV Functions Demonstration of financial functions used to calculate internal rate of return and net present value. You can use IRR and NPV to calculate internal rate of return and net present value for even cash flows. For uneven cash flows, use the XIRR and XNPV functions found in the Analysis Pak Add-In.... Keywords: Add-In, Download, Format, Formulas, Auditing, XNPV, IRR, XIRR

 
304. Find Number of a Letter Then all you need to do is fill down. Even if you sort, the find continues to work.... Keywords: Days, Arrays, Formulas, Links, VBA, FIND, LEFT

 
305. Cascading Style Sheet Keywords: Colors, Styles, Formulas, Links, VBA, Web

 
306. Custom Document Properties To achieve these ends, we hold the items in the CustomDocumentProperties and pull them out as needed.Our spreadsheets are created from a master template and the Properties must be defined in the template before they can be used. ThisWorkbook.CustomDocumentProperties("ActMonth") = ForecastMon BUKRS... Keywords: Formulas, Links, VBA, Templates

 
307. Mail from Excel In this example, there is a list on another page that contains BranchIDs and email addresses to send to. The list is read one Branch at a time and that branch’s data pulled... Keywords: Arrays, Formulas, Links, VBA

 
308. Count Numeric Values in String Public X As Long, Y As Long, D As Long, G As... Keywords: Formulas, Links, VBA, COUNT

 
309. Compare 3 Lists Conditionally "I have been racking my brains for the last week trying to do a conditional count between two or three lists and this pointed me in the right direction. Below is the modified formula.... Keywords: Query, Formulas, Links, Arrays, VBA, LEFT

 
310. Pad Number of Digits Keywords: Formulas, Links, VBA

 
311. Count Numeric Values Function Public X As Long, Y As Long, D As Long, G As Variant 'User-defined function to count numeric values in a string... Keywords: Formulas, Links, VBA

 
312. Compare 2 Lists A new twist came recently with: {=SUM(IF($A7=Details!$B$2:$B$622,IF(Details!$D$2:$D$622=Sheet1!$H$1,1,0)))} Remember, to tell Excel that you are making an array, hold Ctrl+Shift while pressing Enter. The new one looks at a city in A7 and if it is in B2 thru B622 of the Details sheet; then compare... Keywords: Arrays, Formulas, Links, VBA

 
313. Change the formatting of your subtotal rows Excel's Data, Subtotals command inserts subtotal formulas into a list. This is a very useful command, but it's often difficult to identify the subtotal rows. You may want to make the subtotal rows stand out by applying special formatting. However, this can't be done by any of Excel's autoformats.... Keywords: Charts, Format, Outline, Formulas, Printing

 
314. Bond Maturity Value of Securities maturing less than 1 year. Value of Securities maturing 1 - 2 years Value of Securities Matruing 2 - 5 years etc. ABC SecurityValueMaturity Security A Less than 1 =SUM(IF(C2:C6 1-2 years =SUM(IF(C2:C6TODAY()+365,IF(C2:C6 2-5 years =SUM(IF(C2:C6=TODAY()+730,B2:B6,0))... Keywords: Days, Years, Arrays, Formulas, Links, VBA, TODAY

 
315. Write Error to Log Remember to do an "Exit Sub inside your code before the Error Handler so that the Handler is not invoked each time your macro runs. Exit Sub ErrHandler: Open "ErrorLog.log" For Append As #2 'Open file Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _ ; Error(Err); Err 'Write data Close #2... Keywords: Errors, Formulas, Links, Printing, VBA, Macro

 
316. Move Minus (Revisited) Keywords: Errors, Formulas, Links, VBA, Text Files, TRIM

 
317. Spotlight Chart The chart displays a matrix. The marker represents the value at each intersection....At first glance this chart may appear very similar to the Tile chart and to some extent it is. But this chart uses a different approach to the construction of axis labels, markers and the conditional... Keywords: Charts, SERIES, Formulas, Links, VBA

 
318. Delete all input cells, but keep the formulas Your worksheet may be set up with formulas that operate on a number of input cells. Here's an easy way to clear all input values while keeping the formulas intact. Press F5 to display the Go To dialog box, and click the Special button. In the Go To Special dialog, choose the Constants button and... Keywords: Charts, Format, Formulas, Printing

 
319. Easter Function Can't remember where I got it, but here is the Function for figuring Easter. To get Good Friday for my list since it is a company holiday, I use... Keywords: Formulas, Links, VBA

 
320. Avoid error displays in formulas Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error. For example, the formula below displays a blank if the division results in... Keywords: Charts, Format, Errors, Formulas, Printing, Error Message

 
321. Count Number by Month Remember To tell Excel that your formula is an array, hold the Ctrl & Shift keys while pressing Enter. Brackets {} will be displayed around it. {=SUM(IF(LEFT(TEXT(B3:B14,"mmddyy"),2)="12",1,0))} Starting from the middle, take the dates in B3 through B14 and convert to text in the format mmddyy.... Keywords: Dates, Format, Arrays, Formulas, Links, VBA

 
322. Automatic list numbering It's fairly easy to create a formula that generates consecutively number items in nonconsecutive cells. Refer to the figure below. Column A consists of formulas that refer to column B. The formula in cell A1 is: =IF(B1"",COUNTA($B$1:B1)&".","") This formula, which is copied down to the other cells... Keywords: Charts, Format, Formulas, Printing

 
323. eBay Commissions The Final Value Fee is calculated per item sold, based on the final sale price of the item. I wrote the formula for one person, verify with eBay befopre accepting as exact.... Keywords: Formulas, Links, VBA

 
324. OverPunch Code Keywords: Formulas, Links, VBA

 
325. Tile Chart The chart displays a matrix. The colour of the tile represents the value at each intersection....The chart is constructed from a combination of Bar, Column and XY-Scatter. The Bar and Column chart are used to provide category axis labels. The tiles are derived from the XY-scatter chart. The chart... Keywords: Charts, Colors, Formulas, Links, VBA

 
326. Compare ranges by using an array formula In Excel, you can compare the cells in two ranges with an array formula. For instance, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula: =SUM(IF(A1:A100=B1:B100,0,1)) Note: This is an array formula and it must be entered using Ctrl-Shift-Enter. The... Keywords: Charts, Format, Arrays, Formulas, Printing

 
327. Force a global recalculation Q. I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with VBA. Microsoft has acknowledged some problems with the Excel calculation engine in some version of Excel. In order to be assured that all of your formulas have been... Keywords: Charts, Format, Formulas, Printing, VBA

 
328. Pie chart zero removal The workbook contains an example of a pie chart that automatically removes zero values from the pie and the... Keywords: Charts, Legend, Formulas, Links, VBA

 
329. VBA Techniques for Excel Charting Keywords: Charts, SERIES, Fonts, Gridlines, Errors, Formulas, VBA, Links

 
330. Progress Meters A Progress bar is a good way of letting you know that your code is still working, especially if you have disabled screen... Keywords: AutoShapes, Links, VBA

 
331. Pie chart tricks The workbook contains examples of the following pie chart tricks. With a step by step explanations. All the examples are based on a single chart using the secondary axis. So NO overlaying of 2 separate... Keywords: Charts, Format, Formulas, Links, VBA

 
332. Traffic light chart A very specialized conditional chart which uses a traffic light to illustrate the relationship between 3 value bands and an actual value. The download illustrates how the conditional formulas are constructed and used within the chart. It also contains a step by step guide to creating this... Keywords: Charts, Download, Formulas, Links, VBA

 
333. Variable width column chart The column chart has bars of varying width. This is achieved using an Area chart....It is also possible to create stacked and 100% stacked... Keywords: Charts, Links, VBA

 
334. Stacked positive/negative chart This chart uses a combination of built-in stacked bar, a reconfigured side by side bar and some formula. It tackles the problem of maintaining legend order and the bars not obscuring each other. The download contains a step by step... Keywords: Charts, Legend, Download, Formulas, Links, VBA

 
335. Variable width column chart The column chart has bars of varying width. The effect is achieved using Error bars....With some additional data it is possible to add... Keywords: Charts, Colors, Errors, Links, VBA

 
336. Broken axis on column chart The workbook contains an example of how to create a column chart with a broken value axis. The chart is based on a stacked column chart with additional data series to hold the break and continuation... Keywords: Charts, SERIES, Links, VBA

 
337. Scrolling Data Chart The chart uses Named Ranges to define the data to be included in the chart. The scroller allows the user to dynamically change the... Keywords: Charts, Links, VBA

 
338. Clustered column chart with floating gap The workbook contains an example of how to create a clustered column chart with a floating gap. The gap can be either positive or... Keywords: Charts, Links, VBA

 
339. Step Line charts Here are a selection of alternative chart types and methods for plotting step line charts....The workbook contains step by step explanation for chart... Keywords: Charts, Links, VBA

 
340. Clocks The download contains 3 variations on working clocks. The rotating clock is calculated using vba whilst the LED and standard clock use worksheet formula. The vba is available for... Keywords: Download, Formulas, VBA

 

<< (Page 6)  Previous              Next  (Page 1) >>