Excel trial and error facility available in Numbers?

Question

Level 1

83 points

Excel trial and error facility available in Numbers?

It's some time ago now since I used Excel but I'm sure there was a facility for it to automatically adjust the figures in a spreadsheet so that a particular criteria was satisfied. For example if you had a schedule of irregular payments due under a loan agreement you could produce an Excel spreadsheet showing how much would be due at the end of the loan at a particular rate of interest then ask Excel to use trial and error to adjust that rate of interest so that there was nothing due at the end of the loan. You could of course do this yourself by experimenting with the rate of interest until this criteria was met but Excel did it for you automatically.

Is there such a facility in Numbers?

iMac, macOS High Sierra (10.13.6), iPad Air 2, iPhone 7 +

Posted on Aug 22, 2018 3:48 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,006 points

Aug 23, 2018 12:18 PM in response to RobbieSnr

You might try using sliders or steppers to help hone in on the answer. One slider for the most significant bits and one or more to fine tune.

Maximum interest rate for your trial and error in cell B1

Slider 1 with a range of 0-99 in cell B2

Slider 2 with a range of 0-100 in cell B3

Interest rate to test = (B2*100+B3)*B1/10000

With a stepper vs a slider it is easier to get to a specific setting but it is slower to make big leaps in value.

You couls also rewrite this so there would also be a minumim to your range and then ther sliders would give you values in between min and max.

Reply

Answer 2

jaxjason

Level 5

5,047 points

Aug 23, 2018 12:10 PM in response to RobbieSnr

I think most of what you are asking for i think can be accomplished using the included financial formulas and tables.

Look at this list of functions and see if they might help. this wont change the values of other cells, but would assist in the manual calculations.

https://www.apple.com/mac/numbers/compatibility/functions.html

Jason

Reply

Answer 3

Badunit

Level 7

28,006 points

Aug 23, 2018 8:57 AM in response to RobbieSnr

There is no such facility in Numbers, though it can be done with an Applescript. I remember one from years ago but maybe it was specific to a specific problem, I don't recall exactly. I'm not sure I can find it or if it will still work but I'll look.

Reply

Answer 4

RobbieSnr Author

Level 1

83 points

Aug 24, 2018 8:47 AM in response to jaxjason

Yes I did consider using the financial formulae but in my example it becomes difficult where the payments are irregular. In any event this was just an example, I did want to use an automatic trial and error system for other situations where financial formula wouldn't help.

Reply

Answer 5

Badunit

Level 7

28,006 points

Aug 23, 2018 8:58 AM in response to RobbieSnr

The script I was thinking of was for a specific application and was pretty basic in how it iterated to a solution.

Reply

Answer 6

RobbieSnr Author

Level 1

83 points

Aug 23, 2018 9:02 AM in response to Badunit

Many thanks for your replies. I have written a bit of Applescript in the past but it'll be easier for me just to carry out the trial and error manually.

Reply

Answer 7

RobbieSnr Author

Level 1

83 points

Aug 24, 2018 8:53 AM in response to Badunit

That's grand, I hadn't come across steppers and sliders but this is a great way to achieve what I wanted in the particular situation I mentioned and other similar ones.

Reply

Excel trial and error facility available in Numbers?

Similar questions