Tracker
HOME    TRACKER    Excel

# How To Calculate Daily Compound Interest

## I am trying to set up my excel to calculate daily compound interest. The amount is 10,000 at 0.75% per day for 6 months. I have tried several different things with no success -

Related Forum Messages:
Calculate Compound Interest
I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations.

Calculate Compound Interest With Capped Earnings
I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:

Principal: \$120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: \$6 (fixed)
Max interest generating money: \$150 (variable dependant on %interest, = \$150 or \$300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: \$170.84
\$124.80 (4.80 interest)
\$129.79 (4.99)
\$134.98 (5.19)
\$140.38 (5.40)
\$146.00 (5.62)
\$151.84 (5.84)
\$157.84 (6.00 reached the max interest level)
\$163.84 (6.00)
\$170.84 (6.00)

my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................

Calculate Daily Interest Rate
Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:

B3=Capital
B4=Interest Rate
B5=Starting Date
B6=Finishing Date

For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.

Calculating Compound Interest
I have a column of years and a column of numbers representing annual
amounts placed in a savings account for the year. I would like to calculate
the balance of interest earned added to the balance of the account and then
calculate the interest earned on the accumulating amounts each year. The
results would be the account balance displayed in an adjoining column. So
far, I have not found a worksheet function for that. Could someone point me
in the right direction? Perhaps there should be several columns of data?

Compound Interest Rate Formula
I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula

r = n * nt root (A/P-1)

where;
r = the effective interest rate
n = the number of times interest is added per year
t = the total number of years
A = the current value
P = the original value

The 2 problems I face are;
1. Confirming this formula would provide the correct answer (need maths expert here) &
2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel

Compound Interest After Mortgage Free Point
I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra \$267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.

Update Specific Cells Within A Column Used For Compound Interest
I have a column of 30 cells, each showing retirement dollars earned based on years of service (cell 1 = year 1, etc) and salary. Here is the predicament: A new hire is brought in with 10 years service, stays 5 and then quits. How do I change only cells 10-14 reflecting his/her salary and retirement dollars? Remember, cell 10 is the first year for the compound interest, cell 11 the second year, etc. I currently use the following formula for cell 10 (compounded salary from cell 9 * (1+.05)^1). Cell 11 is (compounded salary from cell 10 * (1.05)^1.

Simple Daily Interest
does anyone know how to calculate the interest so it matches this? .......

Calculate Interest Per Month
Sample data:

02-06-09 to 18-06-09 earned \$2500
19-06-09 to 23-08-09 earned \$3500
24-08-09 to 31-12-09 earned \$4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

Calculate In Memory For Each Portfolio If The (Interest - B/F)
I need an array formula, that will peform a calculation in memory for each portfolio if the (Interest - B/F) = >20, or Excel Jeanie HTML 4

Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column
The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.

Calculate Daily Average Of The Several Variables
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.

Calculate An Average Daily Expense Cell
I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.

I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.

SUMIF To Calculate Daily Gains/losses
I currently have a profit/loss spreadsheet where I use a SUMIF to calculate daily gains/losses.

=SUMIF('Daily Sheet'!B:B,B377,'Daily Sheet'!H:H)

Basically, on the 'Daily Sheet' page when a sale is made I write it in a row. Each sale gets its own row. One of the column's has the date. (Column B) Column H has the respective gain or loss for that sale/purchase. B377 in this case contains the date 18-Nov. (B360 to B389 has Nov 1 through Nov 30) So this cell will calculate all the gains and losses in column H for each sale/purchase entry I have for that day. Some days I have 30 rows, others only 2 or 3, so this works great.

My problem now is that my rows have gotten much more complicated and detailed, and now I'd like to be able to only sum column H:H if it meets TWO criteria. (I'd like to add in Column C that specifies the type of sale or purchase being made, which is R, T or S for my purposes.) So I'd like to somehow write a sumif that only adds up the sales for 18-Nov AND sale type R.

Calculate Monthly Standard Deviation From Daily Data
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

How To Calculate Camp Meals Statement Based Only Daily Sheet
I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..

Compound IF Functions
See attached spread sheet as its a difficult one to describe

We are looking to apply labels to a cell depending upon criteria, the spread sheet contains the scenarios.

Compound Sum Or Subtotals
Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)

At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.

Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())

Compound Filter With Top/Bottom X
I have selected a custom autofilter for one column of my data. After that, I want to use the top 10 autofilter in another column, but this appears to filter the top 10 overall, not the top 10 of the custom-filtered records. My data is transformer sizes.

First I custom filter the secondary voltage column to get only distribution transformers (less than or equal to 480). Now I want to know the 10 largest distribution transformers, but the top 10 filter on the size column makes all the records disappear. I believe it is selecting the top 10 overall, and it happens that none of the top 10 have secondary voltage less than or equal to 480. (When I change the custom voltage filter, I can get anywhere between 0 and 10 records for the top 10 by size.)

This seems to be an aspect of Excel, not an error. So far the workarounds I can see are:

1. paste the custom filtered data into a new sheet and then do the top 10
2. use the custom filter, sort descending by size, and count 10--but this problem was first brought up by someone doing the top 100, so this is not preferable.

Does anyone know a better solution that will make it easier to manipulate this data frequently?

Function To Work Out Monthly Compound Rate
Im trying to work out the formulae or fuction that will work out the monthly compound rate of a loan.

The loan details are £140,000 at 7.55% APR for 20 years.

Interest Functions
The file I am working with is available here

(1) I need to work out the value of 2 years worth of interest on a loan - details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)

The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...

what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee

Interest Calculation
I have a macro that formats a spreadsheet to show outstanding invoices, grouped and subtotalled by month. To add to this I need VBA code that will use the subtotals to calculate interest on overdue accounts.

Interest becomes due a calender month after the month in which the invoice is dated. So for example a January invoice would start to accrue interest on 1st March.

Below is the subtotals code (sadly the totals don't adjust if data is added or removed but perhaps that is another question for another day.)

Dim LastRow As Long
Dim NextMonth As String
Dim R As Long
Dim Rng As Range
Dim SubAmount As Currency
Dim ThisMonth As String
Dim TotalAmount As Currency
Dim Wks As Worksheet

Set Wks = Worksheets("Reconciliation")
LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Wks.Range(Cells(2, "A"), Cells(LastRow, "D"))

Compounding Interest

Here's my issue:

I'm trying to be debt free roughly around 10-11 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 5-10-15-and 20 years.

Compounding Interest Formula
I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible.

I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want

Here is my question.

I have two fields, Mortgage and auto loan

I want to have a field that does a calculation for me.
I know following fields as an example making numbers up
Interest rate is fixed at 5.5%
Amount left on loan is 150,000
Loan is fixed at 30 years with 28 years left

I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I don’t think so as it is property tax?))

Same example for car loan.
5 year loan, 4.5% interest, payment 438 a month.

Interest Rate Function
Is there a function that calculates what interest rate is needed to turn a \$2000 investment into \$9000000 in 30 years based on compounding interest?

Cumulative Interest Formula
I have a person who was paid £1000 pension pa for ten years. I've found out that the pension should have been split 50/50 with half increasing by 5% pa. Thus in year 1 total pension would be £1000. In year 2 the total pension would be £1025 ((£500 x 1.05) plus £500)

I know that in year 10 the total pension should be £500 (non increasing) plus £814 (£500 x 1.05 to the power of ten). My problem is how do I work out a formula which calculates the total arrears due in year ten? I'm thinking the arrears due after the ten years is £314 but something is telling me it's a lot more.

Display Rows Of Interest Only
I have this sheet at work which contains a bunch of courses my employer runs, alongwith the dates and other details (which spans columns A through O)

Course-rows which require action/follow-up are highlighted with a certain color. The "completed" course-rows have no fill.
Note: the courses requiring action may have a past date, the only distinguishing feature is the highlight color.

Is there any way by which i could generate a pop-up box of some sort which would display the "highlighted" rows (maybe from A through I or something).

I could add 'follow-up' flags on another column if the highlight color won't do.

Interest Only Payment Formula
I have been raken by brain for a simple formula to calculate an Interest Only Payment on a mortage note, example,

sales price = \$162000
term = 360
interest = 8.5

I was able to calculate the payment of principle and interest, but I need to validate what would be an Interest Only payment on this?

Bank Interest Calculator
I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)...

Public Function BankCalculator()
'Bank Calculator for different accounts
'declare variable
Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double
Set shtBank = Application.Workbooks("Bank Calculator.xls")
'input box for amount of money, assign address
intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................

Show Only Records Of Interest
Following is the excel database. B11 is the dropdown menu. When I select the team from drop down menu. it shud give me sorted Name list from B12. I tried the following formula: =IF(INDEX(\$C3:\$E\$11,MATCH(\$C\$12,\$D3:\$D\$11,0),2)=\$C\$12,INDEX(\$C3:\$E\$11,MATCH(\$C\$12,\$D3:\$D\$11,0),1)," ")

But it is repeating the name or leaves the cell blank ( which i don't want). When I select Team1 it shud give me Names : a,d,e (without blank cell), Even i tried Array and some vba programmimg but it ...........

ABC
1SrNameTeam
21aTeam 1
32bTeam 2
43cTeam 3
54dTeam 1
65eTeam 1
76fTeam 3
87gTeam 3
98hTeam 4
10
11Team 2
12b
13b
14#N/A
15#N/A
16#N/A

Interest Template: How To Sum Up According To Date
I have created a template that I use to determine interest calculations (it is based on simple daily interest, but monthly compounding and adjusts for any variations in interest rates during the month), and it seems to work fine so far.

However, I am trying to make it better and would like to eliminate as much manual input as possible. I have attached an example of the template to help explain what I would like to achieve

The Running Balance is dependant on column "J" - Interest for the Month. At the moment, I am manually adding up interest accumulated for the month, however if I add one cell too high or miss a cell when adding, it can lead to compounded problems further down. So in short I am looking for a method that would automatically calculate "interest for the month" by adding all the amounts in column "I" (Interest Daily) at month end for that month only (dates are entered in Column B)

In the attached template, grey areas indicate capital movements and/or interest rate changes that may occur during a month (ie movement in the month in question that affects the daily interest calculated)

I hope I've covered everything!

Any help in this regard comes most appreciated. If anything is unclear, I will do my best to explain what I am trying to achieve in greater detail.

Quarterly Interest Calculating Formula
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.

But I want to make it compact , as d one I created is long enough.

A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest

Now in F3 I want the Interest amount , compunded quarterly.

Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?

Calculating Annual Interest Rate
If I invested \$350 per hectare into a project and at the end of 12 years that investment yielded me \$150,000, what would my rate of return be over the life of the project?

IF Statements (create Negative Interest)
=IF(AND(ISBLANK(A3),NOT(ISNUMBER(I3))),"",IF(TODAY()-A3>=30,G3*0.5%,"\$0.00"))
How can I keep H from working if there is a negative balance in G? This will create negative interest.

Grid To Determine Interest Rates
I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors - the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720-739, etc). See the screen shot below.

I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720-739.

What I need to do is this - when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those add-ons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear.

I've thought of using VLookup, but I don' think that would be applicable here.

What's the best method, and can you give me an example of what a formula might look like?

Base Rate 6.25 6.25

Credit Score 741 >740

LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 3-4 unit 1 1 1 1 n/a n/a n/a

720-739 all -0.25 0 0 0 0 0 0 720-739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720-739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720-739 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720-739 3-4 unit 1 1 1 1 n/a n/a n/a

Formula For How Much Interest Would Be Paid In A Given Timeframe
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.

I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.

For example

Balance | Rate | Payment | Time Period | Interest Paid
\$1,000 | 10.00%|\$20.00 | 6 | \$48.56

Canadian Mortgage Calulator - Interest Savings
I'm attempting to build a Mortgage payment calculator which calculates interest semi-annually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Bi-weekly, and Accerlerated Weekly vs. Monthly.

I've already calculated what the payments would be for each payment type, however I'm not able to determine what the interest savings would be.

Effective Rate Calculation, Interest Only Loan
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?

Here is the effective rate calcs on a random normal amortization loan:

this is in B2, and answer is 7%
=RATE(B4*B5,-((B3+B7)/B6),B7)*12

B3 = Total*Interest 279017.8
B4 = #*Years*in*Loan 30
B5 = #*Payments*/*Year 12
B6 = Total*Payments 360
B7 = Beginning*Principal 200000
B8 - Ending*Balance 0
problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.

Here is the example I'm working on... A client's loan is the following:
Loan amount - 131,538
interest rate - 6.15
30 year amortization
10 years interest only

normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about \$178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.

Cell Lock, Interest Calculator Formula
1. Can I lock particular cell If Yes,How? I want to lock cells which contains formulas.

2. If cell K7 shows 11 I want to see G11 in H9. Same if Cell K7 shows 201 I want to see G201 in H9.

Its a interest calculation sheet where K7 is number of days amount invested and COLUMN G shows interest accured.

Compound Rate: Annual Growth Rate %
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.

r = 100((Y/X)^(1/n))-1)

So for X = 200, Y = 750, n = 10, we have

r = 100((750/200)^(0.1))-1) = 14.1309%

Daily Average Formula
I need to count the daily average of a task to a week ending number.
I need to see the current average after each day during the week. Example – Mon = 2, Tues = 4 AVERAGE is 3 – Wed = 2 AVERAGE IS NOW 2.6…and so on averaging out after each day is added.

Average Daily Values
I have a table of data covering the last 9 months based on values automatically collated from 15 minute intevals.
The date/time is in column A (01/01/2009 00:00) with the data collected in column D.

My wish is to get the average daily data from column D and I am slowly losing my head!!!

Is there anyway of getting a formula to auto-average the daily values bearing in mind there are currently 96 daily entries.

I have tried converting the first 5 digits of column A to numeric (i.e. 31894 for 01/01) then trying to write a formula saying =average(D1:D24577,if(range="31894",1)).

I can now see a simpler way but am so confused after an hour or so of trying.

Each day has 96 readings so I need an auto adding formula. average column cell A would say =average(D1:D96).

Is there are way to have the cell below auto-update itself to look at the next 96 values and so on and so forth?

Updating Graph Daily
Im handling a graph, line type, that needs to be updated daily, as daily, another cell in the row will be filled.

Anyone can tell me how I can make it update daily and still only show untill todays data. For instance: today is the 7 of May and I want only to show the evolution from the first of the month to the 7th but tomorrow I want it to automatically show from 1 to the 8th,and so on...

SUM Of Daily Inventory
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--(\$A\$5:\$J\$13=A19),\$B\$5:\$K\$13)).

BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.

Calculating Daily Quantities
I have 3 worksheets: Income; Expense; Consolidate.

In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse.

My code copies that information in the consolidated sheet.

What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.

Store Data Daily
I have a two rows of data one containing names and the other containing corresponding numbers. The names are static and the numbers change on a daily basis. I want to be able to copy the numbers to a static table next to each name on a daily basis (so I can see what the value was a few weeks ago).

Is there anything I can write to do this job?

My thinking was to set a vlookup to grab the data but i'm not sure how this would work because the vlookup would change daily when the numbers change

I have a spreadsheet that needs to reference another spreadsheet to obtain a daily target figure. Unfortunately the way the system is set up at work, each day of the year has it's own spreadsheet in it's own folder, and the figure I need needs to be updated each day from the corresponding spreadsheet.

At the moment I simply have 366 (a spare unused one for leap years) different formulas to compare dates and return the figure from todays date. The downside here is that it takes excel 50 seconds to open the spreadsheet because of this, so I assume it's checking all of these figures in all those spreadsheets instead of just the one that's true.

so I have =IF(E2=AF2,spreadsheet address,0) Where E2 is todays date and AF2 is a date from a list.

What I'd like is a method to do something similar but with one or two formulas that will simply update the address of the file I need the figure from based on what date it is so that it will only look at one spreadsheet when it opens instead of all 365.

I tried the following:

Where the section in bold replaces the part of the address with the date folders (20091091) for example, and instead has a cell reference which is formatted to replace this section and updates automatically each day.

It does not work obviosuly and I wanted to know I'm just not formatting the formula correctly or if this idea is a dead end.

Amend To A Database Daily
I have a daily log for work that keeps track of purchases and returns among other items and I was wondering if there was a way I could have all this information get put into a log that will amend everything for each week, month and year.

Sheet For Daily Sales
I have a query regarding making a Excel Sheet for Daily sales.

here I go,
Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total..

M givin you an example in the Below Sheet.

Calculating Daily Averages
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.