Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Calculating APY for **

Hi,

What formula can I use to determine the Annual Percentage Yield (APY) for a
Certificate of Deposit. The interest rate is 5.10% anually with the
interest added back daily. The bank told me that the interest rate is 5.10%
and that would be 5.20% APY. How would they know what the APY would be?
Hope I'm making sense.
Thanks for the help,
Starlin


Post your answer or comment

comments powered by Disqus
Attached is my sheet. As the title states, I am calculating ranges for every second using known range measurements measured at different time intervals. I have input the expect/wanted results in column N and the total time in Column O. I am also wondering if it is possible to list Column K (As it is displayed without using an array formula?) Not sure if this would help but I have been doing some reading online about the interpolation function in excel. (Not exactly sure how to use it). But im wondering if this could be an application? Your help would be greatly appreciated.

Hello,

I am really new to VBA programming and I now need to calculate medians for my experiment data (over 200 participants). I believe Excel macro can do the job, because I have searched and read some old threads in the forum on calculating median but am still not sure how to create one that is appropriate for my data set.

I have attached a sample data file from one participant. In this file, the first column is the response accuracy (1: correct, 0:incorrect) and the second column is the response time (RT) in millisecond. Every 18 trials (rows) is a block, and there are 10 blocks in the sample data. I want to get the median response time for each block (i.e., every 18 trials) for the correct trials only. And the 10 median response times should then be put in the third column.

Please help. I would really appreciate it!

Thanks,
Sandra

Hello,
This is my first time posting to a forum.

The jist of the worksheet.
There are 5 constants on the PostageCosts worksheet.
I am given 2 pieces of data, Mail Volume and Postage, col B & C.
Starting on Col G through K, I need to be able to find out how many 1oz pieces of mail to Canada mail there is based on the Postage and Volume numbers that were given to me.

The problem is if the Mail volume is over 200, the macro takes forever.

Is there a way to speed up the FOR loops or is there some other route I should take?

If you select a row that is highlighted yellow and then click on the postage button at the top, the calculations take for ever. I need to find a way to speed this up.

Thanks for the help.

What's New in the C API for Excel 2007

With Excel 2007 (version 12), the C API has been updated: New worksheet functions are now available through the C API. Analysis Toolpak functions are now available directly through ...

If I enter anything in a cell on one work book I get calculating cell for about 3-5 seconds.

I don't have any formulas looking at the book. and I have deleted, renames, copy and pasted, exported to text and reimported and still it does it.

The thing that is odd though is that if I press return again it clears the calulation cells message.

What could be causing this?

Hi,

How to calculate total for closed order only?

Exapmel:

Or Here:
[img=http://aycu32.webshots.com/image/44751/2005401807983014304_th.jpg]

Regards.

JUST TO LET YOU KNOW THAT I HAVE 17 SPREADSHEET FILES AVAILABLE FOR
YOU TO DOWNLOAD FREE TO YOUR COMPUTER! THESE INCLUDE A COMPOUND
INTEREST CALCULATING SHEET (NO NEED FOR TABLES OF THESE NOW!),
FRACTION TO LOWEST TERMS SHEET, INTERPOLATING AND EXTRAPOLATING SHEET
AS WELL AS MANY DAYS/DATES CALCULATING SHEETS (ALSO RETIREMENT DATE
CALCULATING SHEETS) PLUS DATE AND/OR TIME CALCULATION SHEETS AND "DAY
OF THE WEEK" RETURNING SHEETS. ALL THE DATE RELATED CALCULATION
SHEETS ARE OFFERED TO YOU AS EITHER A UK DATE FORMAT SHEET OR A US
ONE. THE 17 URLs FROM WHERE YOU CAN DOWNLOAD EACH RESPECTIVE
CALCULATION SHEET ARE:

http://uploads.savefile.com/redir/80049.xls
http://uploads.savefile.com/redir/80059.xls
http://uploads.savefile.com/redir/80060.xls
http://uploads.savefile.com/redir/80061.xls
http://uploads.savefile.com/redir/80062.xls
http://uploads.savefile.com/redir/80063.xls
http://uploads.savefile.com/redir/80064.xls
http://uploads.savefile.com/redir/80065.xls
http://uploads.savefile.com/redir/80067.xls
http://uploads.savefile.com/redir/80068.xls
http://uploads.savefile.com/redir/80069.xls
http://uploads.savefile.com/redir/80070.xls
http://uploads.savefile.com/redir/80071.xls
http://uploads.savefile.com/redir/80072.xls
http://uploads.savefile.com/redir/80073.xls
http://uploads.savefile.com/redir/80074.xls
http://uploads.savefile.com/redir/80076.xls

P.S. THE 80061.XLS AND 80062.XLS URLs CORRESPOND TO MY AGE CALCULATING
SPREADSHEETS. REMEMBER TO CLICK "ENABLE MACROS" ON THE BOX WHICH
APPEARS WHEN YOU OPEN EITHER OF THESE SHEETS ON YOUR COMPUTER. THESE
WILL CALCULATE ANY TYPE (YES ANY TYPE!) OF AGE (NO: DAYS BETWEEN "AGE
TYPES" ARE CALCULATED, SO THE USER CAN "TAILOR" THE CALCULATIONS GIVEN
BY THE SHEET TO ANY AGE TYPE THAT THEY LIKE - I HAVE SEEN SOME PAST
DEBATE THREADS ON USER GROUPS ABOUT THIS - LEAP YEARS AND THE LIKE!).

THE 17 SHEETS COVER ALL THE FUNDAMENTAL AGE, DATE AND DAY CALCULATIONS
FOR PENSIONS AND LIFE INSURANCE/ASSURANCE FUNDAMENTAL CALCULATIONS
PLUS MORE (E.G. THE GENERAL DATE AND TIME CALCULATING SHEETS). THE
SHEETS ARE ALL THE "RICHARD MARYTREE" RANGE - A "TOOLSET" OF
CALCULATION SHEETS FOR YOU TO DOWNLOAD! NJOY! RICHARD

Hi,

I am trying to find a C API and SDK for Excel 2003. I can find these for later versions but not for 2003. However, one of the links I found for Excel 2007 has the heading "What's new in the C API" which leads me to believe that there was an API for 2003.

Any advice is appreciated.

Regards.

We have necessity for things to manage the company. It is the company for network installations. I have designed the sheet that I fill for every installation during the day and calculates our costs of material, work, gasoline etc and calculates our gain. Now, most of the daily installations are parts of projects that last several days. So we need to calculate costs for the whole project. I did the other sheet that would calculate it for the whole project but the bad thing is that I have to put in by hand links to various fields in various sheets for every project. Is it possible to make some macro to organize those things automatically? I want to learn VB, I just need someone to give me ideas what can be done.
And more, later we need a database where we will search for clients that had the most rentability, the most expensive projects, the longest etc. Is it possible to make access to read from those excel sheets for that, what do I need, how to organize, what would you suggest?

Please I need a lot of suggestions and help how to run the company production! Thanx!!!

Hello,

I am really new to VBA programming and I now need to calculate medians for my experiment data (over 200 participants). I believe Excel macro can do the job, because I have searched and read some old threads in the forum on calculating median but am still not sure how to create one that is appropriate for my data set.

I have attached a sample data file from one participant. In this file, the first column is the response accuracy (1: correct, 0:incorrect) and the second column is the response time (RT) in millisecond. Every 18 trials (rows) is a block, and there are 10 blocks in the sample data. I want to get the median response time for each block (i.e., every 18 trials) for the correct trials only. And the 10 median response times should then be put in the third column.

Please help. I would really appreciate it!

Thanks,
Sandra

Hi i know this is kind of silly question. but i am unable to solve it. please help me.

i have two columns group and value. I wanted to calculate percentage for the values for that group. i.e for 1st one YG-110 with value 5 the percentage is [5/205*100] .

please tell me the formula

Group        Value   Percentage
YG-1100	   5	       2.44
YG-1100	  20	       9.76
YG-1100	 178	       86.83
YG-1100	   2	       0.98


I need a formula to calculate day for day for a 365 year. The only function
I can find in excel is days360.

For example: If I took a vacation from 1 mar 04 thru 10 mar 04 that would
be 10 days. Which I can do with SUM +1 but if the cells are blank I need the
amount of days to be zero (or blank) not 1.

1. Firstly i want to calculate interest for an Fixed Investment
eg.. 2500 is invested at 9% p.a. rate of interest for 120 months i want to
calculate the accrued interest and Amount of Investment (Principal Amount +
Interest) for end of 12, 24, 36 ....etc. till 120th month.

2. Second i want to calculate interest for recurring deposit say 100 is
invested every month at a rate 9% for total period of 120 month then interest
and principal amounts at end of 12, 24, 36... etc. till 120th month..

Attached is my sheet. As the title states, I am calculating ranges for every second using known range measurements measured at different time intervals. I have input the expect/wanted results in column N and the total time in Column O. I am also wondering if it is possible to list Column K (As it is displayed without using an array formula?) Not sure if this would help but I have been doing some reading online about the interpolation function in excel. (Not exactly sure how to use it). But im wondering if this could be an application? Your help would be greatly appreciated.

Hi,

I apologize for the confusion of my last post.
Your assistance will be greatly, greatly appreciated.

Attached is a sample of the data. The workbook has 3 data sets; one for each
worksheet. The data will be consolidated in one Access table. The objective
is to analyze trends on searches and results of keyword phrases by:

* calculating % change for each phrase and
* graphing the top 20 increases and decreases in terms of percentage change
over time.

The data comes from a web tool that track keywords searches from google and
other search engines.

I am actually thinking about a pivot report as the solution.

Thanks
Gwen

In Excel there is a canned calculation for Yield, but I am looking for a
specific calculation for the OCC required APY disclosure. Does anyone know
where such a calcualtion can be found. I am having issues in finding the
actual formula to build such a function on a spreadsheet.

Hello,

I could really use some assistance. We are required to pay OT for any time worked over 8 hours daily as well as over 40 hours in a week. I am trying to get a formula for one OT cell that would factor in both, if possible. Where:

Cell F7 calculates regular daily hours
Cell G7 calculates OT hours (this is the problem cell)
Cell H7 calculates Total hrs for the day (based on start/end times and/or sick/vac/holiday entered)
Cell I7 is used for leave (sick/vac) hours used (manually entered)
Cell J7 is used for holiday hours used (manually entered)

If H7 is greater than 8 hrs, then F7 needs to be populated with 8 and G7 needs to be the overage. That is not a problem. However, if, at any time during the week, the total of F7 exceeds 40 hrs, then all the remaining hours for that week get dumped into G7 (OT). THAT is where I am having a problem getting one formula to work.

I can find plenty of online examples with either the 8-hr or over 40-hr OT situation separately, but nothing that helps when they are combined. ANY help with this would be greatly appreciated!!

I have the following weight plates for my home gym:

4 plates weighing 10 kg
6 plates weighing 2 kg
8 plates weighing 1kg
2 plates weighing 20 kg

I want to make a calculator in Excel where I say I want a total of 12 kg (for example), and it automatically assigns the available plates for each side of my weight lifting bar (so 6 kg per side). The answer for this one would be tell me to put 3 2 kg on each side.

It has to consider that I only have those available plates. And if it can't find a perfect match, it has to find the nearest weight.

Any ways this can be done in Excel?

Hi There, my first post!

I've created a spreadsheet that calculates how much overtime will be paid to individuals at normal rate, 1.5 x normal rate, and 2 x normal rate.

There's a main sheet listing peoples names across the top, with each day listed down the left hand column. This is where the number of hours of overtime worked is input.

I have two other sheets, one detailing weekly totals (at 1 x normal rate, 1.5 x normal rate, and 2 x normal rate) and one that I need to detail the calendar monthly totals at 1, 1.5, and 2 x normal rate... this is where I've hit trouble.

The rules are: Full Timers will be paid 1.5 x normal rate for all overtime, except Saturdays where 1.5 x normal rate will be paid for the first 3.5 hours, and 2 x normal rate thereafter. Sundays it's 2 x normal rate all day. Part timers get 1 x normal rate (Mon-Fri) until 35 hours have been worked, then it's 1.5 x normal rate. Weekends for the part timers are the same as full timers.

I can't figure out a formula to calculate the calendar monthly totals... please can anyone help? Example File attached.

I have a workbook that includes two tables: one that has hours scheduled by person and date, and one that has each person's pay rate. I'd like to sum the projected cost for each date by project.

I can do all of the parts of this (vlookup to find the rates, sumif to only sum if its a specific phase), but I'm having a hard time making it happen in a single cell. I'm trying to avoid adding any rows or columns, because they'll screw up other parts of the workbook that refer to the hours chart. I could live with doing the calculations on a separate worksheet, but I'd prefer a more elegant solution. I was thinking maybe a macro might be able to do it, but while I could write the for/next and if statements needed, I get really confused with VBA syntax when it comes to referring to cell locations and after a lot of googling I'm still confused about how pass the function which column I'm in. Among other problems.

SampleSheet.xls

Just in case my explanation of what I want to do isn't clear, here's a specific example using this worksheet:
For cell C13 (total cost for Phase A for the week ending 4/15), it should:
- take all of the people who are working on A (Anderson, Faldwell and Henderson)
- figure out what their rates are from the other sheet
- multiply each of their hours by their rates
- then sum the results
i.e.: (Anderson's Hours * Anderson's Rate)+(Faldwell's Hours * Faldwell's Rate)+(Henderson's Hours * Henderson's Rate); or (40*$55)+(40*$15)+(32*$50)=$4,400 Hopefully that makes sense.

Thanks for any assistance anyone can provide, and also I'm sorry if my title isn't following forum guidelines. I had a hard time figuring out how to say what I wanted to do concisely.

I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.

I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input. Thanks!

EDIT: Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.

1/1/00 - 3/31/00 - 32.5 cents per mile
4/1/01 - 12/31/01 - 34.5
1/1/02 - 12/31/02 - 36.5
1/1/03 - 12/31/03 - 36
1/1/04 - 12/31/04 - 37.5
1/1/05 - 8/31/05 - 40.5
9/1/05 - 12/31/05 - 48.5
1/1/06 - 1/31/07 - 44.5
2/1/07 - 3/18/08 - 48.5
3/19/08 - 7/31/08 - 50.5
8/1/08 - current - 58.5

Hi Experts,

Im looking for some help to do some kind of loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.

Hope someone can help me.

Thanks in advance
Anders

Hi guys,

My worksheet currently contains data for thousands of different companies over the span of 10 years (although some companies have fewer than 10 years of data). Each row represents a company's data for a specific year, and I have it currently configured so that the data is arranged first by company name, and then by year, both in ascending order.

One of the columns is "Sales." Let's say we're looking at Ford. Ford has 10 different "Sales" values, one for each year. What I want to do is create a new column that will spit out the standard deviation of those 10 "Sales" values, so if Ford occupied rows 2-11 and "Sales" is in column B, then I would want to create a new column, let's say column C, where I would calculate =STDEV(B2:B11) and paste the result into every cell between C2 and C11. The problem is that I cannot do this manually since I have too many companies.

Logically, it's not too difficult. I simply want to go down the column that contains company names, and for all rows with the same company name, calculate the standard deviation of Sales and spit it into the corresponding column C cells. Of course, the hard part is transferring the logic into code. Would I need VB or is this doable without? Thanks for any help, I realize this post is way too long

PS: I have 26103 rows and company name (it's actually company ID #) is in column A if that helps.

Hi

I need help with this user defined function that i`m working on.

first let me give you the logic:

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59.
The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

the function as follows:

	VB:
	
 
    Dim shour As Integer 
    Dim smin As Integer 
    Dim ehour As Integer 
    Dim emin As Integer 
    Dim stod As String 
    Dim etod As String 
    pday = 8 
    pnight = 12 
    shour = Hour(st) 
    smin = Minute(st) + shour * 60 
    If (shour >= 8 & shour < 20) Then 
        stod = "day" 
    Else 
        stod = "night" 
    End If 
    ehour = Hour(en) 
    emin = Minute(en) + ehour * 60 
    If (ehour >= 8 & ehour < 20) Then 
        etod = "day" 
    Else 
        etod = "night" 
    End If 
    If (shour > ehour) Then 
        mw = 1440 - smin + emin 
    Else 
        mw = emin - smin 
    End If 
    dmw = 0 
    nmw = 0 
     
    If (etod = stod) Then 
        If (stod = "day") Then dmw = mw 
    ElseIf (stod = "night") Then nmw = mw 
    ElseIf (stod = "day") Then 
        dmw = 1200 - smin 
        nmw = mw - dmw 
    ElseIf (shour > ehour) Then 
        nmw = 1920 - smin 
    Else: nmw = 480 - smin 
        dmw = mw - nmw 
    End If 
     
     
    daypay = dmw * pday / 60 
    nightpay = nmw * pnight / 60 
    prod = daypay + nightpay 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



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