Problem: We want to create a sequence of numbers where each member of the sequence is the sum of the previous N number of values. Solution: Use the SUM, OFFSET, INDIRECT, ADDRESS, ROW, and COLUMN functions as shown in the … Continue reading →

Problem: Finding the number of players (column A) who scored over 3 points (column C) in the first quarter (column B). Solution: Use the SUMPRODUCT function as shown in the following formula: =SUMPRODUCT((B2:B17=F2)*(C2:C17>F3)) Screenshot // Counting the Number of Players … Continue reading →

In this article we will learn how to change the cell format into fraction. When we enter the value in fraction format Excel read that value as date and convert the number into date format. Let’s take example and understand: … Continue reading →

If you have a sheet containing duplicate numbers and you want the count of unique numbers, try this formula = =SUM(SIGN(FREQUENCY(A1:A7,A1:A7))) See below -The data before applying the formula After applying the formula -

In Excel we have lots of functions, in which COMBINA function is one of the Math & trig function. This function has been introduced with Excel 2013. It returns the number of combinations, with repetitions, for a given number of … Continue reading →

Problem: Randomly selecting one of the letters from the range A1:C4. Solution: Use the INDEX and RANDBETWEEN functions in the following formula: =INDEX(A1:C4,RANDBETWEEN(1,4),RANDBETWEEN(1,3))

Apart from the standard font style and font size in Microsoft Excel, there are various font style and different font size available. In this article, you’ll learn how we can change the standard or default font style and size in … Continue reading →

Problem: Calculating the credit that remains after each purchase is made. Once the credit limit (stored in cell B1) is exceeded, zero is to be returned for all further purchases. Solution: Use the MAX and SUM functions in the following … Continue reading →

Problem: The range A2:B9 contains a series of dates and the corresponding number of hours worked on each of them. An empty cell in column B that matches a date in column A indicates that 0 hours were worked on … Continue reading →

Problem: Cells in the range B2:M2 contain numerical values, and the row above contains matching serial numbers. We want to subdivide the values into batches of three and calculate the sum of each batch. Solution 1: Use the SUM and … Continue reading →