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

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

- Calculated Ranges for each second of time
- Creating a macro to calculate medians for every 18-row range
- Calculate quantity for given weight factor based on two known criteria
- What's New in the C API for Excel 2007
- Calculating Cells for no reason
- How to calculate total for closed order only?
- CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS!
- C API for Excel 2003
- Calculate costs for the whole project
- Creating a macro to calculate medians for every 18-row range
- Calculate percentage for a group
- Calculate day for day
- Function help with Calculating Interest for two different investme
- Calculated Ranges for each second of time
- Repost -calculate % change for each phrase.
- I am looking for a formula for calculating APY on CDs.
- Calculating Overtime for Over 8 hrs and/or 40 hrs
- Calculate Weights For Weightbar Based On Specific Weight
- Calculating Formula for Calendar Month
- Calculate totals for various projects based on multiple criteria
- Calculate Mileage For Discrete Date Ranges
- Loop through rows to calculate median for several dynamic ranges with numerical data
- Simply calculating STDEV for many companies
- Calculate Pay For Shift Work With Different Rates Based On Shift Hours

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

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.

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 ...

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?

How to calculate total for closed order only?

Exapmel:

Or Here:

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

Regards.

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

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.

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!!!

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

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 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.

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..

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

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.

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!!

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?

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 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 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

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

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.

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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines