Calculate An Average Daily Expense Cell

Feb 6, 2009

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.

ADVERTISEMENT

Calculate Daily Average Of The Several Variables

Jun 3, 2009

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.

View 3 Replies View Related

Calculate The Daily Average Of Rainfall At A Time?

Feb 16, 2014

I have a set of 7 years daily rainfall data. I need to calculate the daily average of rainfall from this 7 years data set. I don't wanted to calculate it by for example copying and pasting the first day rainfall of each year rainfall to a new sheet and average it for all the days. I need to calculate it all at a time.

View 1 Replies View Related

Categorized Daily Expense To Monthly Basis

Jan 8, 2010

While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre

=SUMPRODUCT((TEXT($A$2:$A$14,"mmm")=$E3)*($B$2:$B$14=F$2)*$C$2:$C$14)

See the attached file to get more idea of my question. Then I would Like to Highlight Entire Rows which contains a Specific text.

View 5 Replies View Related

Average Daily Values

Sep 14, 2009

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?

View 11 Replies View Related

Daily Average Formula

Nov 13, 2009

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.

View 9 Replies View Related

Daily Average Based On Day Of Month?

Mar 2, 2014

I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.

I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).

View 1 Replies View Related

Average Formula On Daily Basis

Feb 5, 2010

How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?

View 13 Replies View Related

Average Daily Data By Month

Jan 15, 2009

I have a workbook with two sheets - DATA and SUMMARY.
DATA has two columns - date and data_value. Data will be added to this sheet on a regular basis

SUMMARY has two columns - month and average

In the column for average I would like a formula to calculate the average of data_value for each month without having to manually determine the range for the particular month.

View 2 Replies View Related

Monthly Average From Daily Data

Oct 28, 2011

I have data in the following format (columns A & B):

Code:
Day Value
1/1/2002 2.1
2/1/2002 4.8
3/1/2002 0.1
" "
" "
" "
31/12/2011 5.2

I'd like to be able to write a formula so that I can get the daily data averaged over each month, e.g so that I have something like:

Code:
Day Value
Jan 2002 3.3
Feb 2002 4.3
Mar 2002 3.1
" "
" "
" "
Dec 2011 4.2

I'd like the data in monthly averages so that I can graph them. I have read about array formulas and averageif statements, but I can't seem to combine them to make them work.

View 7 Replies View Related

Formula For Average Daily Balance

Mar 30, 2005

Am trying without success to create a formula to calculate average daily balance from a ledger that has a variable amount of entries per month. The variability of # of entries has me stumped. For example:

date, amount
1/1/2005, 10
1/5/2005, 1
1/10/2005, 4.65
1/18/2005, 7
1/22/2005, 20

Aver Daily Bal = 23.78. and I can get this easily manually, but I'd like a more automated solution. I'm trying a sumproduct angle, to no avail.

View 9 Replies View Related

Function To Count And /or Average When Data Changes Daily?

May 22, 2014

I have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.

For example:

I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?

View 2 Replies View Related

How To Make Daily Average If Have Dataset With 15 Min Sets

May 27, 2014

HOW TO MAKE DAILY AVERAGE IF I HAVE DATA SET WITH 15 MIN SETS

View 3 Replies View Related

Summarize Daily Data Into Weekly Average

Jan 17, 2009

I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?

View 5 Replies View Related

Formula To Calculate Average Of Every Other Cell

Oct 29, 2009

I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...

View 9 Replies View Related

Average Based On Criteria :: Count Daily Usage

Aug 18, 2006

I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.

View 9 Replies View Related

Average Daily Balance - Recursive Equation For Calculating Interest

May 21, 2013

I create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)

In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.

My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0).
I will manually make adjustment for February where 28 or 29 days applicable.

View 1 Replies View Related

Formulas To Calculate Daily Results

Dec 10, 2013

I am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.

please follow this link [URL] .....

Part A
2/12
3/12
4/12
5/12
6/12

plan
0
0
50
50
0

[Code] .....

View 5 Replies View Related

Calculate Daily Interest Rate

Nov 8, 2008

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.

View 2 Replies View Related

How To Calculate Daily Compound Interest

Apr 10, 2008

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 -

View 9 Replies View Related

Calculate Overtime From Daily Hours For A Whole Week

Feb 13, 2014

calculate overtime hours from daily time entries.
Normal hours are 7.6 per day
Time 1/2 is hours over 7.6 but no more than 2 hours
Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

View 5 Replies View Related

SUMIF To Calculate Daily Gains/losses

Nov 18, 2006

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.

View 13 Replies View Related

Calculate Weekly Totals From Daily Figures?

Mar 4, 2012

I have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.

View 7 Replies View Related

Track / Calculate Overtime Hours - Daily Basis?

Sep 14, 2006

I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.

Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?

View 6 Replies View Related

Calculate Monthly Standard Deviation From Daily Data

May 11, 2008

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.

View 5 Replies View Related

How To Calculate Camp Meals Statement Based Only Daily Sheet

Jun 29, 2009

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

View 9 Replies View Related

Average Daily Data Into Month And Year Data

Feb 26, 2014

I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.

View 13 Replies View Related

Auto Copying Cells Value That Changes Daily Without Affecting Daily Value?

Mar 20, 2014

I have the following scenario:

Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.

So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.

I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.

View 4 Replies View Related

Summary Different Day Expense

Feb 4, 2010

I am trying to summary my differentday food data. i want to add total in SUmmary table.

View 6 Replies View Related

How To Calculate Average Time

Aug 6, 2014

how to find the average times of two different sets of groups. There are the baseline times and intervention times. Both groups have three subtopics: Time Fell Asleep, Woke Up, and Duration (how many hours I slept). I am not sure how to find the average of each subtopic. Here are the times....

Baseline times:
Fell AsleepWoke UpDuration
2:30 AM10:00 AM7:30 hrs
4:30 AM12:30 PM8:00 hrs

[Code]....

View 3 Replies View Related

Calculate Average Percentage

Apr 5, 2012

I have a column of percentages, how do I get an average % rate at the end of my column?

View 7 Replies View Related

Calculate Average Across Different Worksheets

Sep 27, 2012

I want to calculate the average for 6 cells across 6 different worksheets.

For instance in sheet 7 i want to calculate what the average of sheet 1 A1, Sheet 2 A1, Sheet 3 A1, Sheet 4 A1, Sheet 5 A1 and Sheet 6 A1 is. However some the cells contain blanks/zeros which are resulting in an error message. The overall figure still needs to be divided by 6 to get the average.

View 4 Replies View Related

Match And Calculate Average

Mar 15, 2013

I need to calculate the average based on SKU patterns.

For example, if the SKU begins with "CZD", then take the average SKU that meets that pattern.

SKU
Items Sold

CZD80
45

[Code] ......

View 8 Replies View Related

Calculate Average If Condition Met

Apr 3, 2008

I have two columns of data. Column B is age column C is gender. I want to calculate average male age and average female age. Suggestions?

Sample
B C
57 f
53 f
47 m
40 f
42 m

Average female age is ___
Average male age is ___

View 9 Replies View Related

Calculate The Average Time

Mar 16, 2004

I need to find the average time it takes students to take exams . I use the following formula =text(end time - start time, "h:mm"). I am able to calculate the amount of time it takes a student to take the exam. Now I need a formula to calculate the average time students take to complete a test. I have over 80 times i need to average. Whenever i try a formula I keep getting 0.

View 9 Replies View Related

UserForm For An Expense Report

Jun 24, 2008

I have an expense report with one row labeled as "Auto", and 7 columns labeled with Sunday, Monday, Tuesday, etc. I'd like to double-click a cell within that row and have a userform (or something else) pop up with 2 spots for data entry: "Personal" and "Company". A user would enter dollar amounts in one or both fields. After they're finished, I would like the total of what they entered to populate the cell that was double-clicked, but still have that breakdown available or even be able to change it by re-double-clicking the cell. Is that even possible?

To add complication, at the end of the row are two additional columns that total personal and company expenses. I'd have to have all personal expense amounts sum together in its column, as well as all company expenses sum together in the other column.

View 10 Replies View Related

Expense Account Lookup

Aug 19, 2007

The expense account we work with involves several currencies due to the international nature of the business. With that, each row must show the currency involved and the formula used from one line to the next makes it repeat the currency until changed i.e. if cell B22 has a date entered then Cell H22 will reflect the currency from Cell H21 =IF(B22="","",H21). If Cell H21 showed CDA for Canadian currency then H22 would also become CDA. Once changed manually then all cells below will now reflect the new currency until changed again.

Using the lookup function we collect the individual amounts of each currency and run totals at the bottom. Therefore, if there were three entries in SGD (Singapore Dollars) and two entries in EUR (European Euros) etc. that each row will do a lookup by the three letter currency code and collect the sum of each currency. SGD could repeat later again and when changed manually will be included in the lookup.

At the present we are entering the three digit codes manually i.e. SGD. What we would like to do is automatically have the bottom be able to pickup the currency change when a new currency is entered on the individual rows on the top part of the expense sheet. So if Cells H21:H23 were SGD and Cells H24:H25 were ERU that Cell A58 would show SGD and cell A59 would somehow be able to grab the ERU etc. One of the same currencies may repeat itself and the lookup will capture the additional totals but SGD would only show once.

View 9 Replies View Related

Payment Expense Spreadsheet With Gst

Nov 3, 2006

I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.

My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................

View 5 Replies View Related

Calculate Average On Some Of The Columns In A Pivot?

Jul 1, 2014

I have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.

I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.

View 1 Replies View Related

Array To Calculate Bottom Average?

Jun 10, 2014

I have an array formula in the attached file to calculate the average of the Top 3 (cell F6). I put that same formula in cell F14 to calculate the average of the Bottom 3 (just change the "large" to "small", however it doesn't work. The result shows zero. How do I fix this?

View 10 Replies View Related

Calculate Average Price For Various Periods

Feb 14, 2014

Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula

Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price

I thought this formula (an array) would work for me

=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.

what adjustments I can make to my formula or is there a better formula to use?

View 1 Replies View Related

ADVERTISEMENT