[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA (design question, look at it if you like a challenge)

Posted on 2012-09-03
14
Medium Priority
?
363 Views
Last Modified: 2012-09-07
Dear experts,

I don't know if it's alowed to ask questions about design ideas and if my question is at al solvable in excel. But here's my situation, ill try to explain as good as i can:

I have imported a whole bunch of data, from an access database. in this database there is a deals table. From that table i get my values. here are the most important values.

1) volume certificates sold
2) period from
3) period to

and from another table i get my expected volume of certificates per month.

What i now have to do is create a table/sheet that calculates the difference between certificates sold and expected certificates. The difficult part is that Period from to is flexible. for example: if period from is january and period to is march, you can sell from january february and march. So what i have done is set all flexible volumes to the first month possible i.e. the month from. But what i now have to do is: if the result between volume sold and volume bought is negative, i have to put some flexible volumes to the next month. for example if there is a negative number in january. the flexible volume mentioned earlier will be set to february, this will be done with flexible volumes till the result is positive. i will make a little example file and add it to the question.

Hope someone has great ideas! Thanks in advance!

Mark
Example1.xlsx
0
Comment
Question by:MarkVrenken
  • 7
  • 5
  • 2
14 Comments
 
LVL 10

Expert Comment

by:ukerandi
ID: 38360245
Where is _xlfn file?
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38360265
Sorry what do you mean? the excelfile is included. I just made an examplefile without functions... I have no clue of what an xlfn file is;)
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 38360449
check this,hope this will help
Results.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:MarkVrenken
ID: 38360524
Hi Ukerandi,

Thanks for your response, but unfortunately i can´t see what you have done that should help;) or did you just make it an macro-enabled file? If you have questions about what i need i'll try to answer them as good as i can.

Thanks,

Mark
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38361170
@MarkVrenken, looking at the two tables, I'm still not quite sure how the data from Deals gets imported into Results. For example, row 2 seems to say there was a deal covering the period January to February (assuming the values 1 to 12 represent months) resulting in a volume of 50,000 units. Do all 50,000 units get applied to January, to February, or split evenly between the two months?

Does the "Volume Sold" column of the Results table exist in the Access database or is it to be computed in Excel?
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38366875
hi tdlewis

i'm sorry for the confusion and my late response, i have been sick the last days. The volume will be from 1 period. so the 50,000 will at first be from January and if there is a negative result it might be from February. The volume sold is derived from the table deals. Actualy there are two columns in the database 1 with buyer and 1 with seller. in excel i have made two tables one where we are the buyer and one where we are the selling party. for simplicity sake i only included where we are seller. Hope this makes it somewhat clearer.

Thanks for thinking along:)

Mark
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38368478
I think the "Results" worksheet in the attached file is what you're looking for.
Example1.xlsx
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38371130
Hi tdlewis,

Thanks for helping:) It does look good, but unfortunately it's not exactly what i am looking for. I think I indeed need some extra columns. But your example does not take in account that some volumes may not be flexible. For example if the period from = period to, then the volume cannot be from february. This is something I also need, and that's what makes it so damn hard;).  If you need more explanation...?

Thanks anyway:)

Mark
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38371132
So probably it will involve some kind of vba function I guess.

cheers,

Mark
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 2000 total points
ID: 38373101
I've been sitting here trying to rephrase the requirements for a deal covering January to February and the logic still feels a little fuzzy to me.

I sorted the Deals tab in the attached version (just so I could look at the January deals).

I also reworked the formulas in the Result tab because I think the logic was reversed and added a Monthly column. Here's what I see:
January had an abundance of volume that might be applied to later months.
February fell a little short so any January deals with a period that also covers January might be shifted there.
April has a bigger shortfall so it will be necessary to look at remaining January as well as March deals to find deals that might be shifted there.
May is so far behind that no amount of shifting will cover the shortfall.
I think you probably will need a VBA script since I cannot envision a complicated enough formula to cover all the bases here. But before someone can even begin writing such a script the requirements need to be very clear.
Example1.xlsx
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38375365
Dear tdlewis,

Thanks for still helping:). it's actually exactly the other way around qua volume downfalls. I'm sorry i didn't notice that earlier. I added the "corrected" version of the sheet. I agree that this cannot be done with formulas alone.

 I will try to give a good description of my requirements/case.

We have a monthly Expected production of product

We sell the products in advance

The expected production has a certain month and is not flexible

The volume sold is not flexible if month to = month from. for example: if month from = 1 ( january) and month to = 1 (january) then the volume MUST be from january

the volume sold is flexible if month from < then month to. For example: if month from = 1(january) and month to = 3 (march) then at first the volume sold will be "taken" from january. If the result of january is < 0 then the flexible volume will be moved to february. if february is also negative, the flex volume will be moved to march. If the result in march is also < 0 the volume in this example may not be moved because the month to was march.

If there are no flexible volumes left in a month but there is still a negative result(<0) . the result of that month will be < 0.

Think that covers my requirements.

I also think that my deals table will need at least two dynamic columns like:

1.

current month i.e. if a volume has month from: 1(january) and month to: 3(march) and the volume is being moved to february the current month will be february(2)

2.

a column like nrOfFlexMonthsVolume. which will be the result of month to - current month and then if the result = 0 the volume isn't flexible anymore.

Thank you for still thinking along:) The case is already far clearer since i started this post because you ask good questions:). if you have any questions I'll gladly answer them!

Mark,

PS can i give you more points then 500(because i think you earned them already;)?
Example1--1-.xlsx
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38376503
Looking at your revised spreadsheet, I think the label for column C should probably be "Overperformance". You sold more units in January than you're planning to produce so you won't need to sell quite as many in February.

What's the BIG question you're ultimately trying to answer? Is it perhaps:
How much do we need to produce in March to satisfy existing orders?
How much more do we need to sell in the first quarter to sell the amount that we plan to produce?

Once you've got a pretty good idea of the problem you're trying to solve, you'll need to decide the best way to solve it. Given the conversation we've had here, I suspect it will ultimately be VBA. Then you'll have to decide if you have the expertise to develop the code in house or need to bring in someone to develop it for you. If your requirements are clear, I think you can probably teach yourself enough VBA to develop the code. On the other hand, you might benefit from bringing in a consultant to do this development for you.

Regarding points, Experts Exchange only allows a single question to be valued by the author at 500 points. The amount awarded to the experts who participate in a question depends on the base value (in this case 500 points) and the letter grade awarded once the question is answered.

If you decide to teach yourself VBA, Experts Exchange can be a really good place to ramp up your skills. If you ask a series of discrete questions, you can ultimately get the information you need to do something very complicated. Break your problem down into manageable pieces. This will help you design your solution and pose questions that will elicit helpful responses. For example, I'd recommend that you not start off by asking "How do I write a macro to control a rocket ship?", but instead ask things like 1. "How do I ensure the following actions occur in the right sequence and at the right time?" and 2. "How do I implement this formula to calculate the burn rate of rocket fuel?"
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38376513
By the way, do you really plan to produce 10 million units in May? You've only got sales of 3.5 million units for the entire year.
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38376597
Good question! I need to answer the BIG question. How much more can i sell. the expected production is uncontrollable(for my department). I've just made an example file so the 10 million is fictive but is not unthinkable(in real life).

I have the skills and time to make this program since I have done a lot in access vba but thought that maybe someone could think with me on this. Because the design will be pretty complex.

I'll close this post, try to make a design and ask smaller questions.

Thanks for your help,

Mark
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question