Count number of values required to sum to total

[LAUGHINGCROW]

I've got a large data set (#8760 values taken at #8760 time points) listed in a column and I need to find out how many of the values i need to add together to reach a target value. However there are multiple target values (#280) and I need to perform the action for all #8760 time points.

To understand the problem its probably best to look at the example sheet I've attached. The example sheet is a cut down version of my data set but shows what Im trying to achieve. The full data set has the Time point values running from 1 - 8760, each with an associated value and has the target values running from 280 - 1.

I had created a very very long nested IF function (cut down version in the attached sheet), however, I ran up against the 64 nested IF function limit. Due to the nature of the data I do require more than 64 levels of nesting in order to reach the target value for all time points and all targets.

Is there another way of doing this so I wont run into the 64 nesting limit?

[oeldere]

Can you post the formula, so i can see what your up to?

Reading your text I think a VLookup will solve your problem.

[samba_ravi]

=LOOKUP(E$15,SUBTOTAL(9,OFFSET($D16,0,0,ROW($D16:$D$40)-ROW($D16)+1,1)),ROW($D16:$D$40)-ROW($D16)+1)+1
Try this in Cell "E16" and copy across

[samba_ravi]

The above will not work with exact match
Try this
=IF(SUM($D16:$D$40)<E$15,"",LOOKUP(E$15,SUBTOTAL(9,OFFSET($D16,0,0,ROW($D16:$D$40)-ROW($D16)+1,1)),ROW($D16:$D$40)-ROW($D16)+1)+IF(LOOKUP(E$15,SUBTOTAL(9,OFFSET($D16,0,0,ROW($D16:$D$40)-ROW($D16)+1,1)))=E$15,0,1))

[XOR LX]

Also, this in E16 and copied across and down:

=IFERROR(MATCH(TRUE,MMULT(0+(ROW($D16:$D40)>=TRANSPOSE(N(INDEX(ROW($D16:$D40),,)))),0+$D16:$D40)>=E$15,0),"")

Regards

[LAUGHINGCROW]

Wow, thank you both for the rapid and effective replies. Both options work, however, CPU useage is extreme when copied into all 2452800 cells (280 columns x 8760 rows). Its actually more considerably processor use than when I used my (limited) nested if statements. Currently doing it for a fraction of the entire sheet takes several minutes. Is there a way to make it more resource efficient?

I dont have the fastest PC int he world(32bit windows 7 OS, 4gb ram, intel i5 2.4ghz)

[XOR LX]

I think with that large a dataset you'd be best off considering a VBA solution.

Perhaps if you re-post in the VBA section of this forum with a link to this thread.

Regards

[LAUGHINGCROW]

Many thanks,

Thread posted in vba/macro section @

http://www.excelforum.com/excel-prog...ml#post3826215

[XOR LX]

Thanks for letting us know. It really does help a lot if links such as that are posted.

Hopefully someone will be able to help you on that new thread soon.

Regards