# Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

1. ## Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

Hi everyone. I'm in need of a little excel help.

I have to work out a formula that uses two lots of numbers to give a final figure (whilst rounding down to a complete value and not leaving a decimal).

For example:
In order to create Product A, it requires 8 parts of Ingredient A and 1 part of Ingredient B.

The result of the formula:
If I have 16 parts of Ingredient A and 2 parts of Ingredient B, the formula should identify that I can create Product A twice.

If I have 18 parts of Ingredient A and 5 parts of Ingredient B, the formula should identify that I can still create Product A only twice without leaving a decimal remainder.

The other thing in which I'm having trouble with is creating sortable fields from the header that you can click to sort (I'm not sure if that makes sense, can expand upon it further if necessary).

Any help would be immensely appreciated.

2. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

Apologies if this is in the incorrect forum section as well.

3. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

See if this suits your needs.

4. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

This is perfect and the speed that it was delivered is outstanding! Thank you so much for such a speedy response. Its very appreciated.

5. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

Take care and good luck!

6. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

One more request if that's alright. In order to create another Product, I only need one ingredient; I can mostly get away with using a simple divided formula of "=B12/B4" with B12 being 35, B4 being 8 and giving the value of 4.375 products that can be created. What's the best way to make a formula that will do this and round down to the nearest complete unit?

7. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

Use the same formula I did - INT. It rounds the number down to the nearest integer. There is also ROUND, but in this case it will not do, because it also rounds the numbers up, for example 4.98 is rounded up to 5.

8. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

I've used "=ROUNDDOWN((L5/C5),0.5)" for the moment and have tested it with a few numbers that differ immensely. Is there any reason it may not necessarily work?

9. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

I believe ROUNDDOWN will work just fine for you

10. ## Re: Product formula that requires 2 Ingredients to create 1 Product and sort droplist.

Thanks again! :D

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1