158 useful answers · Page 6 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicUse =TRUNC(A2,2) to keep two decimal places and discard the remaining digits. For 12.349, the result is 12.34. This is different from rounding to the nearest value. Confirm that truncation is the intended rule before using it in totals, because it systematically removes the discarded portion rather than balancing it through rounding.
Use =MOD(A2,B2), with the total in A2 and a positive group size in B2. For 23 items grouped by six, the remainder is five. Check for a zero divisor and decide how negative quantities should be handled. This returns leftovers, while a quotient or rounded division answers how many groups can be made.
Use =QUOTIENT(A2,B2). For positive values 23 and 6, the result is 3. The remainder is not included, so use MOD separately if you need it. Validate the divisor before calculating. This differs from ordinary division, which returns the fractional part as well and may simply look rounded because of cell formatting.
Use =ABS(A2-B2) to return the magnitude of the difference. Values 8 and 13 give 5 regardless of their order. Preserve the signed difference elsewhere if direction matters, such as whether a measurement increased or decreased. Absolute values answer how far apart two values are, not which one is larger.
Use =SIGN(A2). It returns 1 for positive values, -1 for negative values, and 0 for zero. This can support a classification rule without keeping the number's magnitude. Check very small calculated values before treating them as exact zero, since displayed rounding can conceal a nonzero result.
Use =POWER(A2,3) to cube the value in A2, or write =A2^3. For A2 equal to 4, both give 64. Keep the base and exponent in separate cells if they change frequently. Check the mathematical domain when using fractional exponents or negative bases rather than assuming every combination has an ordinary real-number result.
Use =SQRT(A2) for a nonnegative number. For 81, the result is 9. SQRT returns the nonnegative square root; it does not list both solutions of a squared equation. Check the source when a negative input causes an error instead of replacing it automatically with an absolute value, which changes the question.
Use =PRODUCT(B2:B5) to multiply the numeric factors in that range. A genuine zero makes the product zero, while empty cells in a referenced range are ignored. Check for missing factors before accepting the result. This is different from multiplying paired quantities and prices row by row, which calls for another calculation.
If values are in B2:B10 and weights in C2:C10, use =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). Keep ranges aligned and make sure total weight is not zero. Define what the weights mean and check missing values. A weighted average is appropriate only when those weights represent the importance or frequency you intend.
Use =SUMSQ(B2:B5) to square each numeric value and add the squares. For values 3 and 4, it returns 25. That differs from squaring their sum, which would be 49. Check which operation your calculation actually requires; similar-looking formulas can represent very different mathematical quantities.
Use =RAND() to generate a value from zero up to, but not including, one. It can change when the sheet recalculates, so copy and paste values if you need to preserve a particular draw. Do not treat this convenience function as a secure source for passwords, financial security, or other security-sensitive randomness.
Use =RANDBETWEEN(1,100) for an integer from 1 through 100, including both endpoints. Recalculation can change the result. If you need a stable sample, copy the generated numbers and paste values after checking them. Separate draws can repeat; the function does not guarantee a list of unique numbers.
Use =RANDARRAY(5,3) to return five rows and three columns of random decimal values. Leave the output area clear. Sheets' version does not use Excel's full set of optional minimum, maximum, and integer arguments, so do not copy an Excel formula unchanged. Preserve the generated grid as values when you need a fixed sample.
Use LET to give an intermediate result a name. For example, =LET(total,SUM(B2:B10),IF(total>100,total-10,total)) calculates the sum once, then applies a ten-unit reduction only when it exceeds 100. The name exists within that formula. Use a descriptive identifier such as total, not a cell address such as B2, and define each name before referring to it.
LAMBDA lets you name an input and describe what to do with it. For example, =LAMBDA(distance,distance*1.609344)(5) converts five miles to 8.04672 kilometers. The value in the final parentheses supplies the input; omitting it leaves a standalone LAMBDA uncalled. Names must be valid identifiers, not cell addresses. For ordinary one-off arithmetic, a direct multiplication formula may be easier to maintain.
Use =MAP(B2:B10,C2:C10,LAMBDA(first,second,MAX(first,second))) to return the larger value from each pair of numeric cells. Both input ranges must have the same dimensions, and the LAMBDA needs one named input for each range. Each calculation must return a single value. Put the formula outside the source ranges and leave space below it for the results.
Enter =BYROW(B2:E10,LAMBDA(values,SUM(values))) in an empty column outside the table. Sheets applies SUM separately to each row and returns a column of nine totals. Leave the cells beneath the formula empty. The LAMBDA receives one entire row and must produce one result for that row, so this pattern differs from calculations that return a new table for every input.
Use =BYCOL(B2:E10,LAMBDA(values,AVERAGE(values))) in an empty area. It returns four averages across a row, one for each source column. Exclude text headers and decide whether zeros should count before interpreting the result. Leave room to the right for the output. BYCOL expects its LAMBDA to turn each column into a single value, rather than another array.
Use =SCAN(0,B2:B10,LAMBDA(total,amount,total+amount)) to produce a running total for a numeric column. Zero is the opening balance; substitute another starting number if appropriate. The first LAMBDA input holds the accumulated result, and the second holds the next amount. SCAN returns every intermediate total, so leave room for a column of results and preserve the intended transaction order.
Put the starting value in B1 and numeric percentage changes in C2:C4, then use =REDUCE(B1,C2:C4,LAMBDA(balance,change,balance*(1+change))). A starting value of 100 followed by 10% and -10% becomes 99, not 100. REDUCE carries each result into the next calculation and returns the final value. Enter percentages as 10% or 0.1, not the number 10.
Enter =MAKEARRAY(5,5,LAMBDA(row_index,column_index,row_index*column_index)) in a clear area. It creates a five-by-five table whose row and column positions start at one. The top row is 1 through 5, and the bottom-right value is 25. Leave all 25 destination cells available. The LAMBDA requires two position arguments and must return one value for each position.
ISBLANK tests whether a cell actually contains nothing. A space, hidden character, or formula returning an empty string still counts as content, so =ISBLANK(A2) returns FALSE in those cases. Inspect the formula bar before deleting anything. If your rule deliberately treats a formula's empty-string result as blank, a comparison such as =A2="" may match your intended check better.
Enter =ISNUMBER(A2) in another cell. TRUE means the value is numeric; FALSE can mean it is text, even if it displays digits such as 123. This often explains inconsistent calculations after an import. Check the source and separators before converting. Item codes, ZIP codes and other identifiers may need to remain text so leading zeros are preserved.
Use =ISTEXT(A2) beside the first value and fill down. It returns TRUE for text, including an empty string produced by a formula, and FALSE for a genuinely empty cell. Review the flagged rows before converting them. Numeric-looking identifiers can correctly be text, while an amount intended for arithmetic may need a separate conversion after its format is checked.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.