Formula to change 2 inputs to create higher/lower results

**si1989si** · 11-30-2009, 08:33 PM

I apologise if this is is the wrong area etc. I really need help on the following.
I have a spreadsheet for university in which I have two spaces to input percentages. When a percentage is put into one space the other percentage is automatically added so they add up to 100. Easy.

So I then have other data etc spread around and basically I have one box which combines data from elsewhere and BOTH percentages in relation to data in a formula to create a number. This is also not the problem.

Finally and to the point my question is: How can I create a formula or command or anything so that the spreadsheet will automatically find the two percentages which produce the highest number from my formula, also the lowest number? Currently I just input percentages but I know that it must be possible to have excel work out which two yield the highest number.

Thanks, I apologise for gramma, spelling, not being very clear and the long winded nature of the question.

**si1989si** · 11-30-2009, 08:35 PM

Sorry, to clarify, although the percentages always add up to 100, different combinations result in different outcomes due to the nature of the other data and the formula I am using

**teylyn** · 11-30-2009, 08:37 PM

Hi,

When you registered for this forum you accepted the forum rules. Please take a moment to read them again and then change your thread title to something more meaningful.

Think of terms that may help the posters here to decide if they can help you. What would you search for in Google to find help?

**si1989si** · 11-30-2009, 08:45 PM

Sorry, I came here because I could not work out how to word it in few enough words for google to help, that is why my title was unclear, I hope it is better but if I was able to word it better I would have found results on google

**teylyn** · 11-30-2009, 09:03 PM

Finally and to the point my question is: How can I create a formula or command or anything so that the spreadsheet will automatically find the two percentages which produce the highest number from my formula, also the lowest number? Currently I just input percentages but I know that it must be possible to have excel work out which two yield the highest number.

you maybe after something like

=MAX(A1,A2,A3) or
=MIN(A1,A2,A3)

but it's difficult to tell without seeing your workbook. Can you post a small data sample?

You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

**si1989si** · 11-30-2009, 09:11 PM

Ok so at the bottom, A34 (Risk of portfolio containing A and B), 2 of the inputs are on the right D34 and D35 (the weightings). I need to change the formula in a34 so that it will automatically find the percentages (and change them on screen) which yield the highest possible result in A34. Then perhaps a formula beneath it to do the same for the lowest possible result. I am attaching it, this is my first time so bare with me if it doesn't upload.

**teylyn** · 11-30-2009, 09:20 PM

Sorry, I'm not the subject matter expert for that topic, so some more questions ..

automatically find the percentages (and change them on screen) which yield the highest possible result.

Can you put into words how you would identify these "manually"? Is it the highest / lowest number of Risk of A, B C?

**si1989si** · 11-30-2009, 09:35 PM

Erm, the best way I can put it is, if I change the percentages manually, they create a different result. So If I spent AGES trying each combination of percentages eventually I would have the combination that yields the highest result in my particular formula, again I could do this for the lowest result. However A: I don't have AGES and B: I will never be able to do it as there is an infinite number of combinations of percentages e.g 54.1111122222 and 46.99999888888 or whatever. So I was hoping there is a calculation that will automatically find the combination of percentages that will lead to the highest result and then the lowest result.

**teylyn** · 11-30-2009, 10:02 PM

Which cells do you change manually? As I said before, I'm not a SME on what you're doing, so I have no clue which of these cells hold your "percentages".

You can use Goal Seek to let Excel work out the value required in cell A to get the calculated result that depends on cell A arrive at a desired value ....