Calculate Total using specific numbers

But is it possible, that column B numbers are being generated automatically, based on the required result.

Example:
Let's say required result is 12.5.
First it will check how many times required result can divide be divided by 18. If answer is lower than 1. Then it moves on to the number 9 and checks how many times you can divide with required result. With number 9 you can divide it only one time. So It subtracts 9 from 12.5. Result is 3.5. So again it checks how many times can you divide 3.5 with 2.7 and so forth.

Problem would be with the last calculation. Since 3.5 minus 2.7 is 0.8. But 0.8 doesn't fit with number 1.

Is there any kind of solution for this problem?

I think you will need VBA to do this.

Lets say I sell 18, 9, 2.7 and 1 liters of paint. It will be cheaper to buy 18 liters rather than buying two 9 liters of paint. So that's why formula should always check if the highest number fits to required amount first, then the rest.

Please Login or Register  to view this content.

=get_numbers(A2:A5,B1)

B1=Target value, A2:a5 =18 ,9, 2.7, 1

I'm bit confused with your VBA code.

I created a new .xlsm file. Copied code to VBA with no Sub and End Sub. Added B1 target value, A2-A5 added those four numbers. Now I don't know what to do.

Added attachment.

There is no Sub / End sub as it is a function.

in a cell put .. e.g, B2

=get_numbers(A2:A5,B1)

Excel says "There's a problem with this formula".

May need to change "," to ";"

Now it's working and got my problem solved. Thanks again JohnTopley and Kaper for your time.

But I got one question remaining. How do separate loops results to each cell and not in one string? Like in Kaper excel file. Where he output input numbers and used number to different column and rows.

Right click on Sheet1 tab, "View code", copy/paste code below

Please Login or Register  to view this content.

Add the code below to standard module

Please Login or Register  to view this content.

Enter data in B1 and code will be invoked.

Okay got it. Thanks again for your help.

I have changed original position of the cells and now I'm trying to figure out how to make it work again.

Target number is at E19, those four numbers are at B12:B15 and outputted numbers are at D12:D15.

Here's my attempt at the code.

Please Login or Register  to view this content.

Code changes required ....

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Code is working. But the issue is that it doesn't calculate automatically, since the target number uses formula. More specifically it clones a value from another cell.

Try

In Sheet1 Code

Please Login or Register  to view this content.

in Module 1 code add

Please Login or Register  to view this content.

in "Thisworkbook"

Please Login or Register  to view this content.

In attached, enter data in A8:

E19: =A8

Its working again. Thank you.