# Complicated issue in Excel

#### richard1234567891011

##### Board Regular
Hello guys. I have an issue. I have to sort the data, but optimised in order to be less than 100.000 but the sum nearest possible.
Virtually I need a way in order to have some data, I want that data selected by the excel (preference for the parameter of the data, but the main preference has to be in order to reach 100.000). I think it is not clear so I will make 2 examples.

Example 1

Parameter Data
1 _______10.000
0,9_____ 8.000
0,8 ______10.000
0,7______ 12.000

Parameter are the best solutions in order, but the main goal is to reach a value nearest possible to 100.000. So the solution here is all. Because the sum is less than 100.000

Parameter __Data
1_______ 15.000
0,9 ______10.000
0,8 ______10.000
0,7 ______10.000
0,6 ______10.000
0,5 _______5.000
0,4 ______10.000
0,3 ______10.000
0,2_____ 10.000
0,1_____ 10.000
0 ____________0

This point is tricky because this is in fact the main point. In this point I want excluded 5.000 because even if it has a better parameter the sum is not optimised in order to reach 10.000. The best way in order to reach 100.000 is to sum everything and excluding 5.000.
I hope is it clear.
How can I set something like that? I thought of the solver but how can I set a solver in this way?

Thank you. I really hope that it is clear.

Last edited:

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hello, what is the relationship between Data and Parameters? What gives a certain Data value a certain Parameter? I see in your second example that identic Data values has different parameters.

Last edited:
Supposing the values from your second example are in column A:
Code:
``````Dim x, val, Sum
Range("A1:A11").Sort Key1:=Range("A1"), Order1:=xlDescending

For x = 1 To 11 Step 1
val = Cells(x, 1).Value
'MsgBox Cells(x, 1)
If val <> "0" Then

If Sum < 100000 Then
Sum = Sum + val
If Sum >= 100000 Then
MsgBox "Limit reached: " & Sum & " We are Substracting last Value. (A" & x & " value: " & val & ")"
Sum = Sum - val
MsgBox "Current value reached:" & Sum
Else
MsgBox "Current value reached:" & Sum
End If

Else
MsgBox "Limit reached: " & Sum
End If

Else

MsgBox Sum & " :is the optimum value."

End If

Next``````

Last edited:
Thank you very much Exceladd1ct. I am not very skilled with macros. How can I used it? Can I used it with a Botton or I have to use something different?
The parameters are based on formulas. It is pointless if are the same. I used the same in order to simplify.

Last edited:
That macro is for testing purposes, it might need to be improved. The code will do the following:

1.It will sort all values descending.
2. It will start summing the values until 100.000 limit is reached or exceeded.
3. Once the limit is reached or exceeded, it will subtract the last added value in order to go back under 100.000 and it will go to the next value in the list, repeating step 2 until the end of values list.

You can insert your values from the second example in colum A starting with A1, then go to Developer tab, Click Insert, draw a button on your sheet, click new in the pop-up window and insert my code inside that Sub.

Please let me know if it works and if it does what you need.

Last edited:

Replies
151
Views
4K
Replies
23
Views
11K
Replies
1
Views
3K
Replies
5
Views
778
Replies
1
Views
1K

Threads
1,203,096
Messages
6,053,510
Members
444,668
Latest member
OneCat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back