Jump to content

Conversion formula question

This topic is 6070 days old. Please don't post here. Open a new topic instead.

Recommended Posts


I need to make a formula that will help me with a project I am trying to do - I am trying to show common things in Cubic feet in relation to other cubic feet (real world objects with text) the user will enter in my database. IF they entered an example it would look up in another table and find the closest match and then bring the text into a new field called (result)

Here is my example but I don't know how to write a formula for this.

Say I have the cubic feet for the follow commonly known objects

1 a match box

2 a cereal box

3.a garbage can

I have a the cubic inches of all of them in the other file

the matchbox = 1.182 cubic inches

the cereal box = 288 cubic inches

The garbage can = 7372.8 cubic inches

The thing I am trying to do is let a user enter an amount in a field called cubic amount and then through a calc let them see what their amount would translate into.

For example if they said

894 Cubic Inches

I would like the calc to make the answer come up with an answer for them like

894 cubic inches is about the volume of 3 cereal boxes with 30 cubic inches left over. ( This would be from another table )

I made another table with those statistic in it.

I am concerned on how to make this happen with a formula

I could obviously make thousands of records that were all increments of a cubic inch but that would be real silly in my opinion. That is why I am asking here.

Thanks very much for your time. I know this is challenging.

Link to comment
Share on other sites

From your example, there are two basic methods to pull in the next lower item from a related table. Either using a Lookup with the "use next lower" option, or using a range relationship and sorting it descending. Attached is a demo showing both methods.

You'll notice that it only gives one item type for a given volume, even if another smaller item could be used to take up the remainder. You'd need a custom function to recursively check each remainder to get a result like:

"8784 cubic inches is about the volume of 1 Garbage Can, 4 Cereal Boxes, 219 Match Boxes, with .342 cubic inches left over."


Link to comment
Share on other sites

Thanks so much for the good example . That is exactly what I am looking for and I can modify for other things. I do have one question though ...

I have converted everything along the way to cubic inches. But if I were to get the volume of a railroad box car then I would like the left over answer to be in cubic feet at least. How could that I make it jump to a larger unit when necessary?

Link to comment
Share on other sites

This topic is 6070 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.