Free Microsoft Excel 2013 Quick Reference

# Calculating per diem rates Results

## Calculating Per Diem Rates

days perdiem total
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

## Help with multiple issues in my Excel calculator

Hi,

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!

## Calculating finance charges on simple interest

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

## Formula to get a number to prepopulate from the row above by way of the sumif formula

My husband is a pilot and I am making an excel spreadsheet to help calculate his per diem wages at the end of the year. I have my spreadsheet set up so that if he enters the city code, say "DAL" in Column A it now prepopulates with "Dallas, Texas" in column B and I used the sumif formula to have column C prepopulate with "\$71" which is the going per diem rate for Dallas. If I type in OMA in column A, column B prepopulates with "Omaha, Nebraska" and column C says \$61 which is the per diem rate for Omaha. This is what I want it to do. But I have a bit of a complication with the way per diem works. If it is the last day of his trip (which is always OAK) he has to claim the day before's per diem rate. So, how do I get it to look up the per diem rate from wherever he was the day before when I type in "OAK" to column A? Is there a way to tie the word OAK to column C from the row above?

Does this make sense?

Thanks soooo much again for your help!!

## GetPivotData to retrieve conditional detail data

Hello! (first time, long time)

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.