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

Discuss the spreadsheet application

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

Postby ironweasel » Thu May 22, 2008 10:49 pm

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? :lol:

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
(31.6 KiB) Downloaded 187 times
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?

Postby keme » Thu May 22, 2008 11:05 pm

Use ROUNDUP() instead of ROUND()
User avatar
keme
Volunteer
 
Posts: 3372
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Possible to create a forumla to do this?

Postby ironweasel » Thu May 22, 2008 11:15 pm

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

Thanks for the quick reply.
ironweasel
 
Posts: 2
Joined: Thu May 22, 2008 10:29 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests