Optimising a 3 value problem (well 2 really)
I have tried to find a way to optimise value selections so a range of results based on 3 values will be as evenly spaced as possible as close as possible to a geometric series.
I have a thought experiment that requires the selection of 3 values. These values must have a sum of 100 (not important as final result can be scaled as required but it keeps the numbers civilised) The values can be combines by adding them or by combining the reciprocal or their reciprocals or even a combination of reciprocal and sum. They are all three different in value as experimentation has shown that the distribution of results gets worse if the numbers are the same.
My goal would be to figure out a way to optimise the selection of the numbers such that the results have the lowest error from a geometric progression.
Is there a mathematical result that will minimise the error or do I just keep messing around with two of the numbers (the third is constrained by the chosen full sum)?
Can I use Excel to goal seek on the R value of a trend line on a plot?
This is the list of combinations I have found, the result size sequence changes depending on the three values so only the first and last four are always in the same place.
A||B||C, A||B, A||C, A||(B+C), A, B||C, (A+C)||B, (A+B)||C, B, A+B||C, A||C+B, A+B, C, A||B+C, A+C, B+C, A+B+C
Backstory. This relates to electrical technology and consumer appliance repair and repair technician stock management. I had occasion to look at a list of motor start or run capacitors for appliances on a web store listing. I was rather taken aback by the large selection that was available. They had 74 types with 34 unique electrical values of capacitance. The capacitance spanned 2 decades ranging from 1uF to 100uF.
I though what if a house call technician could stock just two multi section capacitors to cover the full range and configure them on site, so I started to think how many ways can we arrange three capacitor sections in series or parallel and how many evenly spaced values can I find that would allow selection of close fit to desired value with minimal error. Turns out there are 17 combinations I came up with and if one hoped to space them perfectly evenly they should be 1.145 apart in value to fit the 17 possible values in the one decade of capacitance. The idea that a single capacitor that contains 3 elements could be wired up in such combinations to give any value between 1.145uF and 10uF and another unit that would offer values from 11.45uf to 100uf. Certain restrictions came up but I found a close fit with the values 21.739uF, 34.783uF, 43.478uF. They make up 100uF in parallel and 10.230uF in series so not bad. However they do not stay within my +-10% error bars for the geometric sequence that would span the decade.
My go to mathematical modelling software is Excel but I could try other tools as well, might have to learn VBA to automate testing and error calculations.
I will insert a random plot of some of the values I was testing. A surprisingly good fit is 22.22uF, 33.33uF and 44.44uF
1 answer
The following users marked this post as Works for me:
| User | Comment | Date |
|---|---|---|
| KalleMP |
Thread: Works for me Late to react as I did not realise the react was the button or even a button to accept the answer. This answer deserves all the kudos it can get. |
Jun 21, 2022 at 12:37 |
This is the list of combinations I have found, the result size sequence changes depending on the three values so only the first and last four are always in the same place.
A||B||C, A||B, A||C, A||(B+C), A, B||C, (A+C)||B, (A+B)||C, B, A+B||C, A||C+B, A+B, C, A||B+C, A+C, B+C, A+B+C
I think it's only the first three and last three. This is the Hasse diagram which I obtain: 
By considering the contours of equalities between incomparable terms in the Hasse diagram, I obtain this as a "phase diagram" of the possible total orders:
I count 30 possible total orders.
Can I use Excel to goal seek on the R value of a trend line on a plot?
If this is a question about Excel, I don't know and this isn't the right place to ask. But treating it as a mathematical question about the nature of the goal function within each of the areas on the phase diagram, I think (although I haven't attempted to prove) that it should be smooth enough that hill climbing would work, so it seems like a viable strategy to pick one point away from the edges of each area and goal seek from there.
There is one additional point to raise about the goal function, which is that you may have a fencepost error. Do you want 17 steps per decade or 17 values and 16 steps?
Experimenting with Plotly and some simpler cost functions (although I think least squares error from the target line is closer to what you actually want than the "R value"), there doesn't seem to be a parameter set which fits within your desired 10% error bound. The best results seem to be $A \approx 27 \pm 0.1$, $B \approx 33.5 \pm 0.5$
Graph for least squares (L2 norm applied to errors):
Graph for total error (L1 norm):
With a somewhat different goal, if we just aim for even spacing and forget about trying to fit a decade, $A \approx 16.1$, $B \approx 29.6$ is quite good.
0 comment threads