Jump to content


Check out our Community Blogs



Recent Status Updates

View All Updates

Developed by TechBiz Xccelerator
Photo
- - - - -

[Excel 2003] Algorithms for a business worksheet!

excel expense sheet algorithms help

This topic has been archived. This means that you cannot reply to this topic.
No replies to this topic

#1 Donovan

Donovan

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 633 posts

Posted 17 May 2016 - 11:00 AM

 

  • Short Version (TLDR)

     I have no problem using VBA scripts. I understand the language so explaining the scripting or linking references will be just fine for me.

     
    I need one cell to contain "Ink Colors" used and one to contain the "Color Count" used. Incremented by comma + 1 = 2. Here are my guidelines for what effects what in the pricing.

     We can do 2 Colors at a time per Run. It takes 1 Plate for every color used, 1 Wash-Up fee for every color except BLACK ink.. I want this to be easy for our boss to work with.. As he types in the names of colors it auto-updates the amount of plates needed, run time, run cost, ink cost. plate costs, etc..

     So when "Black" is included in the "Ink Colors" cell it needs to update the Wash-Up cost and retract from the price or negate even adding it for just that color.

     For every Run we need to know if we print on one or both sides of paper (1 or 2 Sides). If we print on both sides we double the paper count and that is the value for Run Count. We also need to factor this in with Run Cost because it increases the Run Time.

     For every 5,000 paper count we charge $50.00/Hourly Run Time (We run 5,000 an hour) If you order less than 5,000 paper count the price is a little different. 500 - 2,500 is $32.50 flat-rate. 5,000-Above = $50.00/Hourly Run Time. So I will already have to add in a Cell Box for Run Cost and allow that to be a reference

     It would nice to make a list of the Ink we use so that as he types colors he can just TAB or something and it Auto-Fills the color and then maybe each color has a specific value for the Ink Price so I can factor it in. It would also be nice for him to be able to press space-bar after the name he types and the comma is auto included.

     As I think of more information I will add to it.

    Thanks,
    - Donovan | CodeCall

     


     
  • Introduction

     First off, I have tried to work this out for a few days now and I am having many issues figuring it out. I am decent at math but by no means an expert mathematician. Secondly, I need this for work so it is not school-related.

     

     I am trying to layout and calculate the prices per job. I need to create two worksheets for our business possibly. One that our business uses to keep a record of and one that we can invoice to our clients. I was able to layout the spreadsheet so far and I will try and specify what we need without giving out too many pertinent details

     

     I have attached a PDF file of the spreadsheet for those who are willing to help me work this out..

     

  • Increment A Value Based-On Comma's

     I also need to create a cell that allows us to type out the names of colors and for every comma in the cell it adds that incremented value to another cell.

     It would be nice if we could possibly even program in a feature that allows Excel to add a comma every time we press the space-bar in this custom cell. 

     There would be a comma separating two or more color names. I believe we need a VBA script that makes comma a string equal to "," = 1 + 1 and if the named cell = Black , White the numbered cell would = 2. (Comma + 1 = 2)

     
  • Create an Appropriate Algorithm

     Okay, so we need to calculate costs. I have tried so many things but here is what I want to do and because math is so specific I need a one algorithm fits all format for this..

     You can even suggest something that requires me to change the layout of the Excel in any way. Whatever gets this worksheet to meet our needs of saving this data.

     I need one cell to contain "Ink Colors" used and one to contain the "Color Count" used. Incremented by comma + 1 = 2. Here are my guidelines for what effects what in the pricing.

     We can do 2 Colors at a time per Run. It takes 1 Plate for every color used, 1 Wash-Up fee for every color except BLACK ink.. I want this to be easy for our boss to work with.. As he types in the names of colors it auto-updates the amount of plates needed, run time, run cost, ink cost. plate costs, etc..

     So when "Black" is included in the "Ink Colors" cell it needs to update the Wash-Up cost and retract from the price or negate even adding it for just that color.

     For every Run we need to know if we print on one or both sides of paper (1 or 2 Sides). If we print on both sides we double the paper count and that is the value for Run Count. We also need to factor this in with Run Cost because it increases the Run Time.

     For every 5,000 paper count we charge $50.00/Hourly Run Time (We run 5,000 an hour) If you order less than 5,000 paper count the price is a little different. 500 - 2,500 is $32.50 flat-rate. 5,000-Above = $50.00/Hourly Run Time. So I will already have to add in a Cell Box for Run Cost and allow that to be a reference

     It would nice to make a list of the Ink we use so that as he types colors he can just TAB or something and it Auto-Fills the color and then maybe each color has a specific value for the Ink Price so I can factor it in. It would also be nice for him to be able to press space-bar after the name he types and the comma is auto included.

     
  • Suggestions, Specialty Advice, and Misc.

     If you have any suggestions to add or maybe even some specialty advice from personal experience, I am all for it!

     Also, please don't hesitate to provide any questions to help solve this more accurately. Any thing else? Not sure where to put it? Post it anyway!