## [Solved] Possible to create a forumla to do this?

### [Solved] Possible to create a forumla to do this?

I'm trying to figure out how to create a formula that will take a number, divide it, give the result and the divisor and THEN, if the result is over 400, round the divisor up to the nearest whole number until the result is less than 400.

Make sense yet? I'll try and give an example.

2100 / X = 400 (or less).
If we do 2100 / 400, we end up with 5.25
Since 5.25 won't work in this application, logical rounding would round down to 5.

2100 / 5 = 420.
The result is over 400, which also won't work for this application.
Round up to 6, then divide.

2100 / 6 = 350

350 is less than 400, and 6 is the nearest whole number, so this particular equation works.

I've uploaded my chart in it's current, "not working correctly" form so someone can take a look at what I'm working with.

I'll use Line 2 as another example.

200 * 10.5 = 2100
E2 is giving me a divisor of 5.
J2 is giving me a result of 420.

I need J2 to be 400 or less, which means the divisor in E2 needs to be 6 instead of 5.
Problem arises because I don't know (and can't figure out how even after reading the help file) how to write a formula to find a divisor that gives the required result.

Any help would be much appreciated.
Attachments oc_chart.ods
Last edited by ironweasel on Thu May 22, 2008 11:15 pm, edited 1 time in total.
ironweasel

Posts: 2
Joined: Thu May 22, 2008 10:29 pm

### Re: Possible to create a forumla to do this? keme
Volunteer

Posts: 3400
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Possible to create a forumla to do this?

Wow, can't believe it was that simple, yet I couldn't figure it out.