Sum LARGEST UNTIL + build a table from summed amounts, without not summed

[qqry]

Hey guys!

Would be awesome to receive help here as I am thinking about this for too long.

Idea is very simple, eg.
Capture.JPG

I do have, in this example, 4 different amounts in Table 1 - 2, 3, 5 and 6. I also have a threshold of 10.

What I do want?

1) Receive first possible amount being sum above threshold built from largest number - in this case it will be 11, sum of 5 and 6. This can be done by using Ifs and LARGE function but is not very handy with larger number of values.

2) More importantly, I do want to have in Table 2 only numbers being summed from table 1. So instead of 2 and 3 I will have 0, but 5 and 6 will be moved there.

Here is this example file:
example.xlsx

Any ideas? Thank you from the mountain.

[mcmahobt]

Why not use this in F3:

Please Login or Register  to view this content.

And this in H3, and drag across:

Please Login or Register  to view this content.

[qqry]

Hey, thanks for reply.

About point 1 - because it will sum just two largest. If I change amounts it may not work (eg. 1,2,3,6 or 1,2,3,11 - in first case three amounts are needed, in second just one).

About point 2 - well, this is some interesting way to think forward. However in current format will work only if sum is built from two largest. As said it may be any amount from 1 to 4.

Above is just example, final version of the file should do something as above for around 30 records.

Thank you again, hoping for more ideas! .

[mcmahobt]

I'm not sure if I'm understanding correctly what you need. Could you try rephrasing your problem?

[qqry]

As above, may it is just worth highlighting:

I do have a threshold of eg. 10 and four RANDOM numbers each time. So formula may not be "static" - it may not refer to fixed amount or largest numbers.

As said, sometimes a sum of all four may be needed to exceed threshold, sometimes just one is needed, sometimes two or three. You never know! .

For amounts up to 3 largest required to be above threshold formula within example's cell F3 do the work (well if I would add one missing parenthesis :P).

I dunno how I can explain this further.

Thanks for help.