Cube Root Of Number

Explanation

The cube root of a number can be calculated manually with the exponentiation operator (^) or with the POWER function .

Manually with ^

The cube root of a number can be calculated manually by raising a number to the (1/3) using the exponentiation operator (^). In the example shown, the formula in C5 is:

=B5^(1/3)

Be sure to enclose 1/3 in parentheses to control the order of operations .

With the POWER function

The cube root of a number can also be calculated with the POWER function, by supplying 1/3 as the power argument. The equivalent formula is:

=POWER(B5,1/3)

Explanation

This formula uses an expanding range for the criteria range inside COUNTIFS:

COUNTIFS($B$5:B5,B5)

Because the first reference is absolute and the second reference is relative, the range expands as the formula is copied down the column. The criteria is simply the value in the current row of column B.

COUNTIFS returns the count of the current customer up to that point in the data. This means the first occurrence of a customer is 1, the second is 2, and so on. Because we only care about the first occurrence, we compare the count to 1:

COUNTIFS($B$5:B5,B5)=1

This expression will return TRUE when the count is 1 and FALSE for any other value.

Finally, to force a 1 or 0 result, we add zero. The math operation causes Excel to coerce TRUE and FALSE to equivalent numbers, 1 and 0.

Note: The example above uses first name for customer id. This is not realistic, but it makes it easy for the human eye to track. In normal data, customer id will be a unique number of some kind.