Please help.
I am trying to put together a formula (arrays / macros not available) that tells me the number of cells in a row that it takes to get to a cummulative sum of 100%.
My row looks something like this:
{0,0,0,30%,0,0,30%,40%,0,0}
the formula should result in 8 (because it is only until cell 8 in the row that I reach 100%).
Help is much appreciated.
Hi Rpulido, welcome to the forum.
Assuming your row of data is in A4:Z4, use this formula in A5:
=SUM($A$4:A4)>=100
Fill that to the right (to Z5). You will get a string of FALSE's and eventually a TRUE when the cumulative sum is >=100. (When dealing with percents, 100% actually equals 1, so you may need to adjust it to >=1 instead.)
In another cell you can then use the formula:
=MATCH(TRUE,5:5,0)
This will tell you the position of the first instance of TRUE in row 5. You can hide row 5 if you don't want to see the TRUE's and FALSE's.
Or, try this.
Please Login or Register to view this content.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks