I need to make CellA2 value dependable from value of CellA2.

For each 12 units of CellA1 make CellA2 1 more
A1       A2
12         1
13         1
14         1
24         2
25         2
28         2
30         2
36        3
in other words only when is a multiple I add one more
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Schnell SolutionsSystems Infrastructure EngineerCommented:
For your second column is the following formula:


It will returns you the integer value of dividing A1/12. Then after pasting the same formula for the rest of the cells it will change A1 for its corresponding value.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this.....

In B2

Open in new window

and copy down.

Is this what you are trying to achieve?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Good approach. That didn't strike me. :)
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also I assumed that row1 is the header row and numbers start from A2.
Schnell SolutionsSystems Infrastructure EngineerCommented:
Hehehe. But in the same way, with this simple formula you get the value and you don't need to worry for pasting it throw columns or rows.


Just with this simple thing you can get the desired result.
tonycromeroAuthor Commented:
sktneer Thanks for your answer but it is not exactly what I am lucky for. I did not explain myself well.

I have two item

Item 1
Item 2

For every complete 12 units (1 case) of Item1 I have to calculate 1 unit of Item 2

So for every complete case of Item 1 I give away 1 unit  of Item2.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I cannot correlate your requirement with the sample data you provided, probably I am missing the logic. My apologies.

It would be helpful, if you upload a sample workbook with two sheets Before and After.
Show what you have originally on Before sheet and show the desired output with some sort of explanation for your logic on After sheet.
That would help to understand your logic clearly.
Katie PierceCommented:
I don't see how schnell's formula does not achieve what you're going for.  By the logic in both your question and the follow-up comment, that should serve your purpose.

I think schnell's answer is the correct one, but just for the sake of anyone in the future researching this sort of thing, here's one more way to get there:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonycromeroAuthor Commented:
Yes that is what I need. Every 12 eaches of an item I have to add 1 unit as a gift.

So for 12 I give an extra 1
for 24  an extra 2
but in between if round down.

Thanks you all guys for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.