Formual Needed (Excel 2002 – XP)

Topic

New Reply

I need a formula that will place in a cell the whole number in increments of 8 (8, 16, or 24) and place the remainder in another cell.
Cell C1 contains 4 (C1=4)
Cell A2 contains 8 (A2=8)
Cell B2 should contain the formula. It should add cells A2+C1 (8+4=12) then display the whole number 8.
Cell C2 should display the remainder 4 (C2=4) or zero if the sum of A2+C1=8.

If the total of A2+C1 is equal to or greater than 16, then the formula should display 16 and any remainder should be in cell C2 including zero. The totals of A2+C1 will never be greater than 24.

Hope this makes sense and thanks in advance.

Reply | Quote

Replies

In cell B2, enter the formula =INT((C1+A2)/8)
In cell C2, enter the formula =MOD(C1+A2,8)

Reply | Quote

Hans,
The formula =int((c1+a2)/8) results in 1.5. It should display 8.

thanks

Reply | Quote

=int((c1+a2)/8) can not equal 1.5, no matter WHAT a1 and c2 equal.

it equals 1 if c1+a2 = 12.

use
=int((c1+a2)/8)*8 or the formulas of Andrew.

Steve

Reply | Quote

Andrew and Steve have already helped you, so this is just for completeness.

My formula should have been =INT((C1+A2)/8)*8, as Steve pointed out. But if you got 1.5, you must have forgotten a pair of parentheses. The formula =INT(C1+A2)/8 would result in 1.5, but =INT((C1+A2)/8) results in 1 if C1=4 and A2=8.

Reply | Quote

Thanks again to you, Andrew, and Steve. I did get 1 and not 1.5 as I earlier indicated (duh).

Reply | Quote

If I understanfd you, try the following in C2

=MOD(A2+C1,8)

and in B2 use

=A2+C1-C2

The MOD fucnction returns the remainder when a number is divided y a given number. For more detail check the Help.

Andrew C

Reply | Quote

Andrew
This works great. It was so simple, guess I could not figure it out (duh) and thanks also to Hans.

Reply | Quote