Calculate Average Weekly Income For Business?

Mar 3, 2014

I have created a table for a business to monitor all income and expenses within a financial year of that business. This table contains raw data for example, date, income/expense, wholesale amount, retail amount etc. So when the business makes a sale for example I enter in the date of the sale, type of sale, wholesale and retail amounts etc, and i do this for every sale I make.

What i need to do is figure out a way to calculate the average monthly and weekly income to date so as the financial year continues and i make more sales i will continue to enter more data into this table and it will automatically adjust to the new weekly or monthly average income. This way i always have an actual and live average for every cent the business has made as the year progresses and i can use this to budget for the business. I would also like to know what the income is for the current week or month. that way i can say for example on average the business makes $4000 a week and in this week it has made say $5000.

View 13 Replies


ADVERTISEMENT

Calculate Rolling Weekly And Monthly Average

Feb 19, 2012

I am wanting to calculate a rolling monthly average and a rolling weekly average.

The following cells have the headers k2 has Allan, Cell L2 has Bill, Cell M2 has Charlie, Cell N2 has Don, cell o2 has Ellen and Cell P2 has Flora

Column J3 to J14 respectivley has Jan to Dec

The balance of the cells will have the data.

I then need to plot the rolling averages for each person on a gaph as teh months data is filled.

Below is the table:

Monthly Totals 2012AllanBillCharlieDonEllenFloraJan0.0000.0000.0000.0000.0000.000
Feb0.0000.0000.0000.0000.0000.000Mar0.0000.0000.0000.0000.0000.000
Apr0.0000.0000.0000.0000.0000.000May0.0000.0000.0000.0000.0000.000
Jun0.0000.0000.0000.0000.0000.000Jul0.0000.0000.0000.0000.0000.000
Aug0.0000.0000.0000.0000.0000.000Sep0.0000.0000.0000.0000.0000.000
Oct0.0000.0000.0000.0000.0000.000Nov0.0000.0000.0000.0000.0000.000Dec0.0000.0000.0000.0000.0000.000

View 1 Replies View Related

Calculate The Tax On Any Income

Apr 21, 2009

I'm trying to write a function that takes a single argument grossIncome of type Currency. It should calculate the tax on any income using the following tax schedule

1) If income is less than or equal to $15000 there is no tax.
2) If income is greater than $15000 and less than or equal to $75000 the tax is 15% of all income greater than $15000
3) If income is greater than $75000 the tax is 15% of all income between $15000 and $75000 plus 20% of all income greater than $75000

Then I want to write a sub that asks for the user's income and have the above function calculate the tax on the income and say the tax in a message box.

View 2 Replies View Related

Calculate Tax Based On Income

Jan 26, 2008

I am working on a tax deduction formula for payroll. I need 3 formulas. The first formula needs to find two cells that are on the same row based off the employees status and gross income.

View 5 Replies View Related

Calculate Due Business Day Date

Jan 9, 2007

I need to calculate a due date for the fifth business day of the next month following the input date. I am using Excel 2000.

View 2 Replies View Related

Calculate Date Of First Business Day Of A Month

Sep 19, 2012

I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.

For example:

I have a workbook with two worksheets in it.

The first worksheet is the input page.

On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.

Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12
Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)
(in this example, this should be 3) and displayed as 03-Sep-12
Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1

The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.

The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.

Daily
Date
USD

29 June 2012
1.0191

1 August 2012
1.0507

[Code] ........

Worksheet 1, Cell C1 should display the value of 1.0266

View 5 Replies View Related

Find Median Of Large Set Of Numbers To Calculate Median Income?

Mar 6, 2014

We are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?

View 8 Replies View Related

Weekly Average Commission Calculation

May 5, 2009

I hope this question has not been addressed but the closest I can find is in this link:
[url]

I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income.

I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. -Although it would be better to base it all around the financial year if that can be done? (March 1st - April 31st in NZ)

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

Cumulative Sum For Weekly Average Hours Over A Month

Apr 21, 2009

I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday.

In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday.

Please see attached file. I am referring to columns J and K ....

View 12 Replies View Related

Calculate Elapsed Time Based On Business Hours

Dec 8, 2007

I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours.

Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day

example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete

example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete

View 7 Replies View Related

Calculate Business Hour Downtime Excluding Weekends

Feb 15, 2008

I am trying to calculate downtime for a Service Level Agreement.

The data that I have is the start date/time and the resolved date/time for an incident.

The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours.

As example(s)
- if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours.
- if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours.

The above assumes working hours are 8:00 AM through 5:00 PM.

I have tried using the NETWORKDAYS and WORKDAY functions with little success.

View 9 Replies View Related

Calculate Future Dates On The Business Day Preceeding The 15th Of Each Month

Sep 16, 2009

Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..

View 2 Replies View Related

Calculate Time Spent On Ticket Minus Non-Business Hours?

Jun 6, 2012

I have a requirement of calculating time spent on ticket, basically a difference between the time a ticket was logged and when it was resolved.

First, working days are Saturday through Wednesday, i.e weekends are Thursday & Friday.

Second, there are different resolver groups or teams which work on a ticket. These teams have different working hours, some work from 07:30 to 15:30 on weekdays, and others work 24/7. Each ticket will be assigned only to one resolver group.I also need to consider holidays.

So, my requirement is to check the resolver group for each ticket, and decide the working hours based on it. After this check has to be done for holidays, and then difference between logged time and resolved time needs to caluculated in minutes.

Tickets can be logged at any time of the day / week, it may not always be logged during working hours only. in such cases calculate should consider next business day / hour as the start time.

Raw data for tickets is available in =Sheet1!$A$1:$E$21

Ticket NoResolver GroupLogged DateResolved DateTime Spent123456
Team A5/21/2012 1:56:28 PM5/29/2012 2:10:53 PM343543Team B5/21/2012 9:31:02 PM5/27/2012 3:41:22 PM853732
Team C5/22/2012 2:28:06 PM5/26/2012 2:34:31 PM

[Code].....

View 4 Replies View Related

Formula To Calculate Overtime On A Weekly Basis?

Apr 21, 2014

I am looking for a formula to calculate overtime on a weekly basis, entered daily, on a sheet with multiple weeks. It need it to calculate the overtime each week.

View 8 Replies View Related

Time Sheet - Calculate Bi-Weekly Totals

Feb 25, 2014

I have a time sheet that I can figure the daily OT and DT on, but how to calc the the Bi-Weekly totals.

I can only have 80 hours per week, the rest needs to be carried to the OT field, but I can only have 40 of OT per week and the rest needs to be carried to the DT.

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

Calculate Weekly Sales With A Midweek Start

Dec 9, 2007

I'm trying to create a simple sales report. No VBA code, only excel formulas.
I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.

Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and
if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.
I want it done automatically.

I've included a zipped excel sheet example of the worksheet for a visual example.

View 9 Replies View Related

Calculate Hours Between Date And Time Within Business Hours?

Apr 4, 2013

find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?

08/03/2013 13:32:00
02/04/2013 09:32:50

View 4 Replies View Related

Weekly Stock Without Listing All Products Weekly

Jun 19, 2007

In the attached workbook, the CASTINGS worksheet has a SOH ( stock on hand) column.
It looks up the SOH from the SOH worksheet (column 3) for each product on order for that week.

But how do I make week 2 use the SOH figure minus the previous weeks order, IF there was an order?

And so on until week 52.

The problem I have found is what if that product wasnt ordered for the last 4 weeks? Maybe I need a temp worksheet with all the products listed for all the weeks and a running total of SOH weather it is ordered or not?

View 5 Replies View Related

Sum Of Income By Month

Jun 26, 2009

I have a spreadsheet that contains entries for each order of a product and the product amount. What I want to do is have a summary of this for income. So, if there is a date completed for the order, I want a sum of this for the month.

Order No. Order Amount Date Ordered Date Complete
A2 B2 C2 D2

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

Add Income Only Between 1st And 15th Of Each Month

Dec 7, 2008

I am a car salesman and made a sheet to track my sales from month to month. I would like to make a formula to only add the entries that fall between the 1st and 15th of each month to show me what my check will be for the first half of the month. Column A has the dates and column J has the amount. I need to calculate only the first half of the month in J24 to know what my mid month check will be......

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







Copyrights 2005-15 www.BigResource.com, All rights reserved