You have probably been doing “if” logic ever since you were a toddler: If this is true, then this; otherwise, this. IF statements are great for solving undesirable, but predicable, problems with error conditions. IF statements are good at solving problems with negative days or time. If you’ve ever tried to subtract a later date […]
Excel’s AVERAGE() function evaluates zero values. If you want to exclude zeroes, use these alternatives. Excel’s AVERAGE() function considers zero values in its calculations, which is probably what you’d expect, but not necessarily what you want. Over the years, I’ve seen many formulas that ignore zero value and most are more convoluted than they need […]
Excel’s COUNTIFS() function is flexible enough to handle different ranges and data types. In Count the number of Excel records that fall between two dates, I used the COUNTIFS() function to count the number of dates that fell between two dates (inclusive of the dates themselves). Although the COUNTIFS() function allows you to specify multiple count […]
Do you need to insert the page numbers in printed workbook as well as author name, current date and file name? The Microsoft Excel’s Header/Footer command can help you show information in the header or footer at the top or bottom of each printed page easily. And you can also add some images in the header, such […]
VBA Macro for Conditional Formatting Private Sub Worksheet_Change (ByVal Target As Range) Set MyPlage = Range(“C3:I11,C13:I34”) For Each Cell in MyPlage If Cell.Value Like “A*” Then Cell.Interior.ColorIndex = 38 ElseIf Cell.Value Like “B*” Then Cell.Interior.ColorIndex = 35 ElseIf Cell.Value Like “C*” Then Cell.Interior.ColorIndex = 34 ElseIf Cell.Value Like “D*” Then Cell.Interior.ColorIndex = 40 Else Cell.Interior.ColorIndex […]
VBA Macro Code to Plot A “XYScatter Chart” on Fly Sub chartData() On Error Goto ErrHandler Dim xRow As Long Dim wsName As String Dim ws As Worksheet Set ws = ActiveSheet wsName = ws.Name xRow = Cells(Rows.Count, “A”).End(xlUp).Row ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = “='” & wsName & “‘!$A$17” ActiveChart.SeriesCollection(1).XValues = “='” & […]
Excel’s VLOOKUP() function is notorious for returning wrong results, but it’s not the function, it’s the way you’re using it! Excel’s VLOOKUP() function finds a matching value in a list. It’s one of Excel’s most popular and misunderstood functions. It’s possible to enter the correct formula and still get the wrong result but with just […]
How to convert Numbers to Words in Indian Currency Format There are almost 200+ similar requests from our page followers to how to convert numbers to words, below given code will suit to all the queries at the best. Hope this helps. How to create the function Called SpellNumber() 1) Start Microsoft Excel. 2) Press […]
Learn Goal Seek Method : Most Useful Forecasting Tool Example with detailed explanation, on request from Mr Karthi Keyan How Goal Seek Works. ============== Goal Seek feature allows you to alter the data used in a formula in order to find out what the results will be. The different results can then be compared to […]
Learn to find Top & Least Sales Person Name with VLOOKUP function You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN and MAX are nested to return the name of the salesperson associated with those functions. The formula in […]