Free Microsoft Excel 2013 Quick Reference

1 50 50

2 50 100

3 50 125

4 50 175

my formula:

=(B3*0.75)*(A3-2)+(A3-2)*B3

the per diem rate i am trying to get is for travel, the 1st and last day of

travel is 75% of the per diem rate and the days in between are at 100%, i am

trying to get this formula to calculate for 1, 2, and 3 or more days this

formula only does it correctly for 3 or more days, 1 and 2 days of per diem

is calculated at 75%, is this possible

I am new to the forum and new to VBscript programming. I recently was given the task to program a calculator with VBscript that would be able to accurate price out the most cost effective way to buy laboratory rats. So, I pretty much had to teach myself VBscript. I have a basic case structure macro, but not much else. This is what I came up with (see attachment).

There are a few things I want to fix and do with the spreadsheet:

1. I would like to make a code for the 4 yes/no questions that can override the input in cell B5. I would also like to make it spit out a specific value in B12 when answered with YES in one of those drop down menus. Hopefully there is no overlap because these are all separate prices

2. Whenever I have a set weight in Male or Female, the price value comes up right, but if I keep that same weight value and change to the opposite gender, it doesn't change. I'd like to fix this.

3. Cell B6: I want this to be reflected in the total cost because it will be something like how many rats we order per week.

4. Cell B14: I want the utilization rate to reflect in the total cost by affecting the per diem cost (i.e., if I use one rat a week and the cost would decrease based on how many rats I used).

5. Cell B15: I would like to have this cell have an input of days and multiply that input by a specified dollar amount to give the per diem total. (is there a VBscript code to do this?)

6. I would like to create a separate table within this spreadsheet that can extrapolate 1 month, 2 month, 4 month, 6 month, and 12 month costs.

I am not necessarily looking for someone to do this for me, just help with the coding and macros I'd implement. I hope to learn a fairly good amount so that I can keep these macros for later or build upon them myself.

I may have not been the clearest in this post, but if you have any questions please ask --- this task has been driving me nuts! Also, I have attached the PDF of costs that I am referencing.

Thanks!

Does this make sense?

Thanks soooo much again for your help!!

I am trying to establish a Commission Calculator in Excel. I have designed it in four parts which are also four separate sheets: (1) the employee listing with hours worked (2) the data entry including date, salesperson, customer name, amount, and program (3) my pivot table (which is the only way I was able to achieve a breakdown by program of each salesperson and their deal per day) (4) the calculation of each salesperson's commission per deal on a per diem basis. I am having trouble constructing the fourth part.

Basically, if a salesperson has x number of deals per day, they get a flat dollar amount per deal; but if they have +x deals per day, they get a higher flat dollar amount per deal.

So I am thinking that I need a GetPivotData formula that will retrive the pivot table's calculation of each program, each salesperson, and their deal per day, but I don't want to hardcode the salesperson's name into the syntax. I would like to reference the employee listing on the first sheet and have the formula search the pivot table for the name and then return the deals per day.

Notes: This department has a high turn over that will create insane maintenance. Salespeople also can move between programs. The Commission Rates are different for each program and are seasonal, so again, I am reluctant to hardcode.

Is this even possible without a degree in computer programming? I have searched the message boards, but have a hard time believing I have a unique question.

Thanks in advance,

Liz

No luck finding an answer? You could always try Google.