"Must have" app for Microsoft Excel users. XLStyles Tool is an Open XML based application, which can analyze and clean out corrupted cell styles in your Excel workbook. XLStyles Tool will also perform analysis of named ranges and report potential issues. User of the tool is able to take actions to remove identified corrupted file content. Files processed by the XLStyles Tool in most cases will show noticeable improvement in file size, opening times, restored ability to support cell format edits (including copy/paste operations) and better overall performance.
This channel is dedicated to helping Excel Freelancers reach their dreams of financial independence with the ultimate in tricks & tips videos that teach them how to design, create and market Excel based software and sell it forever.
If n points (x, y) from the curve are known, you can apply the previous equation n-1 times and sum the results. For example, in the sample workbook, we had the function y = 4*x^2; we knew 10 points, so we applied the formula 9 times. For the first point the result was (1 – 0)*(4 + 0)/2 = 2, for the second (2 – 1)*(16 + 4)/2 = 10 and so on. The picture above contains the entire set of calculations.
2nd method:SUMPRODUCT formula
With this method, you avoid the intermediate calculations, and by using only one function, you get the result. However, the difficulty level is a little bit higher than the first method (especially if you are new to Excel). The method involves the SUMPRODUCT function, the syntax of which is given below:
SUMPRODUCT(array1, [array2], [array3], …)
The SUMPRODUCT function multiplies the corresponding components in the given arrays and returns the sum of these products. Array1, array2… are the ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns, and you must enter at least 2 arrays (you can have up to 30 arrays).
The tricky part is the array/range definition. If n curve points (x, y) are known, the function can be written:
In the sample workbook, the SUMPRODUCT function is used with the following ranges:
=SUMPRODUCT(A5:A13-A4:A12;(B5:B13+B4:B12)/2).
In reality, we applied the same function as in method 1, but instead of single cells, we had multiple cells/arrays. The function performs the following calculation:
Without any doubt, the second method is much more straightforward than the first one.
3rd method:Custom VBA function
At your Excel file, switch to VBA editor (ALT + F11), go to the menu Insert Module and add the following code lines.
Option Explicit Function CurveIntegration(KnownXs As Variant, KnownYs As Variant) As Variant '--------------------------------------------------------------- 'Calculates the area under a curve using the trapezoidal rule. 'KnownXs and KnownYs are the known (x, y) points of the curve. 'Written By: Christos Samaras 'Date: 12/06/2013 'Last Updated: 21/06/2020 'E-mail: xristos.samaras@gmail.com 'Site: https://www.myengineeringworld.net '--------------------------------------------------------------- 'Declaring the necessary variable. Dim i As Integer 'Check if the X values belong to a range. If Not TypeName(KnownXs) = "Range" Then CurveIntegration = "Xs range is not valid" Exit Function End If 'Check if the Y values belong to a range. If Not TypeName(KnownYs) = "Range" Then CurveIntegration = "Ys range is not valid" Exit Function End If 'Check if the number of X values is equal to the number of Y values. If KnownXs.Rows.Count <> KnownYs.Rows.Count Then CurveIntegration = "Number of Xs <> Number of Ys" Exit Function End If 'Start with zero. CurveIntegration = 0 'Loop through all the values. For i = 1 To KnownXs.Rows.Count - 1 'Check for non-numeric values. If IsNumeric(KnownXs.Cells(i)) = False Or IsNumeric(KnownXs.Cells(i + 1)) = False _ Or IsNumeric(KnownYs.Cells(i)) = False Or IsNumeric(KnownYs.Cells(i + 1)) = False Then CurveIntegration = "Non-numeric value in the inputs" Exit Function End If 'Apply the trapezoid rule: (y(i+1) + y(i)) * (x(i+1) - x(i)) * 1/2. 'Use the absolute value in case of negative numbers. CurveIntegration = CurveIntegration + Abs(0.5 * (KnownXs.Cells(i + 1, 1) _ - KnownXs.Cells(i, 1)) * (KnownYs.Cells(i, 1) + KnownYs.Cells(i + 1, 1))) Next i End Function
