Blog of Calc LLC

Suppose you have a column of numbers and the data can be positive or negative. How do you count the number of times the data changes from positive to negative, or vice versa? We will use three functions, SIGN , INT and SUM to solve the problem. First we add a helper column B, name it "sign", then use SIGN function to get its sign. If the data is positive, it returns 1. If the value is negative, it returns -1. The formula is like: =SIGN(A2) Then we add another helper column C, call it "sign change", which checks whether sign of the current value is different from the last value. INT function is used to return the logical value TRUE or FALSE into 1 or 0. At last, at the end of column C, we use a SUM function to sum all the value changes. In this example, the data sign changes for 4 times (-1 to -3, -3 to 4, 2 to -5, and -5 to 6). VBA method Below is the VBA code to achieve it. You need to add a clickable button on the spreadsheet and assign the following code t...

Suppose you run a grocery store and below is the sales amount of your products. The SUM function calculate the sum of total sales. The formula is: =SUM(B2:B6)

SUM function calculate the sum of a range of numbers. The formula is: =SUM(B2:B5)

This example tells you how to calculate sum of values using VBA. Of course, you can achieve this by using the SUM() function without any VBA. This example demonstrates how to achieve it using VBA. Suppose you have a sales data for four regions, and you want to know the total of sales. See the data below. The VBA code is this: Sub sum_sales()     Dim row As Integer     Dim sum As Single     sum = 0     Dim sale As Single     For row = 2 To 5         sale = Cells(row, 2) 'grab cell values         sum = sum + sale     Next     Cells(6, 2) = sum 'put the calculated result in the right cell     MsgBox ("done") End Sub By running the code, you get the sum: