I have numbers in column A, which sums up to a total of say 450. Now, I want to copy the values of A1 to B1, A2 to B2 and so on until the sum of column B is 100. Then the remaining cells will have number 0. So at the end, cells in column B will either have the same value as in the corresponding cell of Column A, or will have a zero but ONE cell will have a non-zero value which is less than the corresponding cell in column A (to keep the sum of B to be 100).
Can someone help me to write a formula for this?
Hi ssrao and welcome to the forum,
Find the attached with the answer. It looks like:
=IF(SUM(B$1:B1)=100,0,IF(SUM(B$1:B1)+A2>100,100-SUM(B$1:B1),A2))
One test is worth a thousand opinions.
Click the * below to say thanks.
And another way!
Change the value in B1
Last edited by Marcol; 10-27-2011 at 09:47 PM.
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks