How to Divide and Double Every Three Hours in Excel

To divide and double every three hours in Excel, we need to use two functions: POWER and MOD. The POWER function returns the result of a number raised to a power. The MOD function returns the remainder after dividing one number by another.

We can use these functions to calculate the value of a cell after dividing and doubling it every three hours. For example, if we start with 100 in cell A1, and we want to know the value after 12 hours, we can use this formula:

=A1*POWER(2,MOD(12,3)/3)/POWER(2,INT(12/3))

This formula works as follows:

• The MOD(12,3) part returns the remainder of dividing 12 by 3, which is 0. This means that there is no extra hour left after dividing 12 by 3.
• The MOD(12,3)/3 part returns the fraction of an hour that needs to be doubled, which is 0/3 or 0. This means that there is no need to double the value for the extra hour.
• The POWER(2,MOD(12,3)/3) part returns the result of raising 2 to the power of 0, which is 1. This means that the value is multiplied by 1 for the extra hour, which does not change the value.
• The INT(12/3) part returns the integer part of dividing 12 by 3, which is 4. This means that there are 4 full cycles of 3 hours each in 12 hours.
• The POWER(2,INT(12/3)) part returns the result of raising 2 to the power of 4, which is 16. This means that the value is divided by 16 for the 4 full cycles of 3 hours each.
• The A1*POWER(2,MOD(12,3)/3)/POWER(2,INT(12/3)) part returns the result of multiplying the value in A1 by 1 and dividing it by 16, which is 6.25. This is the final value after 12 hours.

Procedures

To apply this formula to any cell and any number of hours, we can follow these steps:

• Select the cell where you want to enter the formula.
• Type = and then click on the cell that contains the initial value. This will insert the cell reference in the formula, such as A1.
• Type *POWER(2,MOD( and then enter the number of hours you want to calculate, such as 12.
• Type ,3)/3)/POWER(2,INT( and then enter the same number of hours again, such as 12.
• Type /3)) and press Enter. This will complete the formula and return the value after dividing and doubling every three hours.

Explanation

The formula we used is based on the following logic:

• Every 3 hours, the value is divided by 2. This means that the value is halved every 3 hours, or multiplied by 0.5 every 3 hours.
• Every hour, the value is doubled. This means that the value is multiplied by 2 every hour, or raised to the power of 2 every hour.
• To calculate the value after a certain number of hours, we need to combine these two effects. We can do this by using the POWER function to raise the value to the power of 2 for the fraction of an hour that needs to be doubled, and then divide the value by the power of 2 for the integer part of the number of hours that needs to be halved.

For example, if we want to calculate the value after 5 hours, we can use this formula:

=A1*POWER(2,MOD(5,3)/3)/POWER(2,INT(5/3))

This formula works as follows:

• The MOD(5,3) part returns the remainder of dividing 5 by 3, which is 2. This means that there are 2 extra hours left after dividing 5 by 3.
• The MOD(5,3)/3 part returns the fraction of an hour that needs to be doubled, which is 2/3 or 0.67. This means that the value needs to be doubled for 0.67 hours, or 40 minutes.
• The POWER(2,MOD(5,3)/3) part returns the result of raising 2 to the power of 0.67, which is 1.62. This means that the value is multiplied by 1.62 for the extra 40 minutes.
• The INT(5/3) part returns the integer part of dividing 5 by 3, which is 1. This means that there is 1 full cycle of 3 hours in 5 hours.
• The POWER(2,INT(5/3)) part returns the result of raising 2 to the power of 1, which is 2. This means that the value is divided by 2 for the 1 full cycle of 3 hours.
• The A1*POWER(2,MOD(5,3)/3)/POWER(2,INT(5/3)) part returns the result of multiplying the value in A1 by 1.62 and dividing it by 2, which is 81.25. This is the final value after 5 hours.

Example

To illustrate how this formula works, let’s use a scenario and an example with real numbers. Suppose we have a bank account that has an initial balance of 1000 dollars. The bank offers a special interest rate that divides the balance by 2 every 3 hours, but also doubles the balance every hour. We want to know how much money we will have in the account after 24 hours.

To solve this problem, we can use the formula we learned in the previous sections. We can enter the initial balance of 1000 in cell A1, and then use this formula in cell B1:

=A1*POWER(2,MOD(24,3)/3)/POWER(2,INT(24/3))

This formula will return the value of 39.06 in cell B1. This means that after 24 hours, we will have 39.06 dollars in the account.

To see how the balance changes over time, we can use an Excel table to show the values for every hour. We can enter the numbers 1 to 24 in column A, starting from cell A3. Then we can use this formula in cell B3 and copy it down to cell B26:

=A1*POWER(2,MOD(A3,3)/3)/POWER(2,INT(A3/3))

This formula will use the value in column A as the number of hours, and calculate the balance accordingly. The table will look like this:

We can see that the balance fluctuates between doubling and halving every hour, and reaches the lowest value of 39.06 after 24 hours.