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

Free Microsoft Excel 2013 Quick Reference

Calculate total by week Results

Hi all,

I am trying to make a spreadsheet for my office that we will use to calculate how our day is split up. So my dilemma comes in that I need to make it so that when a Cell = 7.5 another cell will then change from a 1 to a 0.

If you need more detail, please read below. I have also submitted a copy of this spreadsheet to further explain what I am trying to accomplish. Warning, this explanation is long and complicated...

The goal is to take the 37.5 hour work week and after deductions - give an accurate number of hours we actually spent "processing". However, since we are allowed 30 minutes total in break time and another 30 minutes for coffee/bathroom etc, I have added a column (titled Other) that manually deducts a 1 from our 37.5 total - thus making the weekly total only out of 32.5 hours.

Heres how it looks. Columns B, C, D, E, F, and G represent headers such as time off, meetings etc.

Rows 3,4,5,6, and 7 represent Monday through Friday.

Cells G3 to G7 are all defaulted with a "1" as described above.

So if I was to be out all day monday, I would put 7.5 in the time off column. My dilemma comes now in that my 7.5 in time off now adds to the defaulted 1 in the "other" column to make 8.5 hours being taken off from my 32.5 total - which thus represents an inaccurate end of the week total of processing hours. I am essentially cheating myself of one processing hour. Now I know the solution is simple - just delete the 1....but it's not that easy because this spreadsheet has to be used by hundreds of people all within the ages of 18-80...some of them might get confused. The other solution would be to delete the "1" column entirely and make the spreadsheet out of a 32.5 hour week, except that then if you took the day off you would have to put 6.5 - not 7.5, otherwise if you were hypothetically out all week - you'd be at negative 5 processing hours rather than 0. People are so accustomed to 7.5 I dont want to start making them type 6.5, and they want to just type in their time and not be deleting things, that I need a formula so they dont have to.

Thus why I came to the conclusion to keep my spreadsheet as originally designed, except that when someone types a 7.5 in a specific column, it will then automatically change the "other" columns 1 into a 0. This way, nobody has to delete anything, all they need to do is type their time in and the end result will be totally accurate.

I can't think of any other way to make this work as accurate as possible, and I'm very sorry for the long post! If anyone reads this and is able to help me I would be extremely grateful!!!

Thank you very much for your time and help,
Adam

Hi,

I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.

This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)

I have made drop down lists to select each of the values for the three conditions and want to show the total for them

i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.

i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.

I want to be able to calculate this total in each cell for that day.

I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.

Hi all!

Is it possible to sum across worksheets from the first sheet to the CURRENT sheet so that a week on week average total can be calculated which will not be altered by the addition of future worksheets?

eg. We have 10 weeks named week 1, week 2 etc.

In week 2 I want a total average (in this case leads/hours) of week 1 and week 2.

In week 3 I want a total average of week 1, week 2 and week 3.

Can this be done?

I am trying to figure payroll in Microsoft Excel. I have the hours figured
accurately. I have the wages entered. But when I try to multiply the two to
get a daily (and weekly) gross pay, it gives me a very small number from
multiplying the time (hours and minutes) by the serial number rather than the
hours and minutes. This gives me a total of roughly $13.00 for 55 hours of
work @ $8.00 per hour. This isn't correct, but I cannot figure out how to
get it to do the calculations correctly. Does anyone know how to do this?
Thank you,
Walt

I go to a few regular poker games every week. The number attending varies from week to week as does the buy-in amount to play so I've compiled a few tables to help calculate the amount to pay out for each position in £'s to help the organizer.

The number of places paid out depends on the number playing. The percentage of the total pot paid to each winner also varies depending on the number playing.

E.g up to 8 playing and 2 places are paid in a 70% 30% split of the total pot.
up to 40 playing and 5 places are paid in a 45% 25% 15% 10% 5% split of the total pot

My problem is that the formulas I've used given my limited skill with excel mean that sometimes when all the payouts are counted up they exceed the pot total, usually by £1.

For example in the second table (£15 buy-in) 5 people are playing for a total pot of £75. The 70/30 split is given as £53 and £23 which exceeds the amount available by £1. This is repeated throughout the whole file in various places.

How can I make it so that the amount shown to be paid out never exceeds the total money available but sticking (roughly) to the percentages indicated? All amounts to be paid must also be to the nearest £1.

I don't have my laptop at the tournaments or I could just use the top table and substitute the figure in B3 to whatever the buy-in amount is and it should work fine (apart from the above mentioned problem). I need this to be printed out and therefore it needs to cover all combinations we might use

Hi! This looks like a great place for an Excel newbie, like myself!

I'm a dog trainer by avocation. I hold classes on Saturdays and weekday evenings and most of my classes are held once a week. I also hold advanced classes twice a week on weekday evenings. Here's my problem.

Right now I have three worksheets and two of them are involved in this problem. One worksheet provides the starting date of each weekly class. Weekly classes run for 10 weeks each. My class roster worksheet picks up the starting date for each class and runs a +1 addition formula to each day in the 10-week period to display the actual date at the top of each column. At the end of the 10 weeks, I have a formula that calculates how many sessions each student attended. This is pretty straightforward and not part of my problem.

The problem is the twice-weekly classes. There is no ending date for these and some of my students are with me for years. I'd like to set it up so that each twice-weekly class has a starting date and then, on it's own roster worksheet, displays the actual date for 364 days from the starting date. I would then continue any students who take more lessons to the following year. It seems like an awful lot of work to have a starting date and then a plus 1 formula for 364 days and I'm hoping that there is an easier way to do this. As far as the display goes, only the actual class-meeting days are displayed but, of course, the hidden columns must all contain the formula, anyway. There is the usual "Days Attended" sum at the end. Is there an easier way to enter the formula than to do a "sum=+1" for each and every column?

In short: Data picked up in starting date.Second class lesson is Starting Date plus 2 (approx); third lesson is Starting Date plus 4 (approx) with each intervening date being Starting Date plus 1.Data continues for Starting Date plus 364.Total Dates Attended at end of year.
I thank you, very much, for any help you can provide.

I have been trying to solve the following problem using the example worksheet:

Sheet "Sales Report" is produced on a weekly basis. Using the data provided in Sheet "Master List" I want to show the total in Sales Report Cell A10 as the number of leads provided by a DIRECT sales team but only for the 7 days prior to the date the report is issued (Sales Report B1)

I have tried using SUMPRODUCT formulas combined with date calculations but keep getting errors.

Appreciate any help that can be offered!!!

I have a timesheet that has Sunday thru Saturday on it. I have a table made
where absent hours are entered. I want my absent code letter (say the letter
S for sick) to show up on the particular day of the week that it was used off
of the table of paid hours. How do I get excel to look at the Sunday thru
Saturday absent hours and determine which code to put.... V for vacation... S
for sick.... P for personal... ect. In the example below I would want the
letter S to show on Thursdays Absent hrs and the letter V to show on Mondays
Absent hours. The rest should show a blank cell. This table calculates to
a daily absent hrs collum followed by a absent code collum. I would like it
to calculate as followed.

Abent Hours Absent Code
Sun
Mon 8 V
Tues
Wed
Thur 8 S
Fri
Sat

Example of table:
It is set up like this off to the side of the time card:
Mon Tues Wed Thur Fri Sat Sun Total hrs
Sick 0 0 0 8 0 0 0 8
Vac 8 0 0 0 0 0 0 8
Pers 0 0 0 0 0 0 0 0
Float 0 0 0 0 0 0 0 0
Other 0 0 0 0 0 0 0 0
Holiday 0 0 0 0 0 0 0 0

I am trying to create quite a complex working shift rota and forecast document for my business.

I own a hotel with a bar, restaurant etc and have created a document in excel that will allow me to forecast my takings etc and create a staff rota. I am now a little bit stuck on a situation. I have the excel document split into sheets named

Forecast Document
Rota
Employee List
HR & Budget
HR Dashboards
Settings

In rota I have created Sunday throught to Monday and a drop down list in each one to choose the staff start and finish time for each day. This then totals and gives total hours at the end of the week.

In HR and Budget I have created a sheet that breaks down the daily hours worked per person and then multiplies by their hourly wage to give their pay for that day.

The formula to calculate the hours worked is

=TEXT(Rota!D9-Rota!C9,"h:mm")

The formula to then calculate the hourly pay per employee is

=C34*'Employee List'!E9*24

What I am struggling with is that if i list and employee as off in the rota it gives and error of
#VALUE! in the formula

How do I stop it doing this and just have it display "0" hours?

I am trying to create a spreadsheet that tracks daily capacity on a 5-day rolling basis (please see attached). I need to be able to manually change CAPACITY and REPORT values on an nightly basis and have the spreadsheet automatcially do the calculations throughout the remainder of the week.

For example; if the value entered into C8 exceeds the value entered into C6 I need the spreadsheet to automatically subtract the difference from the value entered into E6. I need this functionality to roll throughout all five days of the week (hope that makes sense).

As mentioned the goal is to create greater visibility to daily CAPACITY based on report totals for each day. I am not “new” to Excel but this is by far the most advanced table/spreadsheet I have tried to create. I’m not really sure if it can even be done so any assistance would be greatly appreciated.

Thanks for the time!

Hello: I have a spreadsheet with a list of employees by department, the number of hours the employee worked last week in that department, the employee's pay rate, and the number of vacation hours each employee accrues per week. I am attempting to determine the total dollar amount of a weekly benefit accrual expense, which is normally done thusly: hours accrued x pay rate. The problem I'm running into is that we have a number of employees who are split between 2 or 3 departments, and the column with the number of hours they accrue is not pro-rated per department. That is, if an employee accrues 1 hour of vacation each week total, and they work between two departments, that hour shows up twice on two different rows, even though the employee is only accruing 1 hour total. What needs to happen is that the number of hours they worked in any given department needs to be divided by the total hours they worked in all departments, and this number needs to be multiplied by the total vacation hours. Eg: Mary Smith accrues 1 hour/week. She works 20 hours in Department 1 and 20 hours in Department 2. The 1 hour needs to be multiplied by 1/2 for the row with Department 1 and 1/2 for the row with Department 2.

I have a simple enough formula which weeds out the employees who show up more than once: if(countif(d:d),d1)>1,"",I1*f1), with D1 representing the employee name, I1 representing their rate and and F1 representing their vacation accrual. If necessary, manual calculations can be used for the exceptions; however, I was hoping there may be a way to automate this calculation. It would need to do the following: If d1 (from the formula above) shows up more than once, the corresponding values in column k (hours worked) need to be added together, so that the value in k1 can be divided by that total, and then that amount should be multiplied by I1 and F1 (rate and benefit accrual).

Is this possible to do without a long macro? Thank you in advance.

Hey there!

I have recently worked out a budget worksheet that solves for hours needed to earn a certain amount of money, including the hours required to earn the money for tax. The hours worked per week is calculated for a defined time period.

I used the solver function with a few constraints,
-Actual hours worked (the one that taxes are influenced by) must equal projected hours worked
-Actual needed in savings per week must equal projected available for savings each week.

This works great, but when I add an "index(match(" function that references a table and adds in future expenses to the total expenses needed, the solver can no longer find a solution. I'm sure it has something to do with the time distribution, but I can't figure out how to include those expenses without breaking the solver.

I've attached the sheet because the formulas and constraints are easier to look at than to explain.

The projected additional costs are down at the bottom of the sheet.

Joshua College BudgetV1.65s.xlsm

Any ideas?

Josh

I just started working at a restaurant and word going around is their payroll accounting is horrid.
I looked back at my last two paychecks and have noticed major discrepancies and have decided to go hardcore in recording my hours worked, gratuities, etc.
I can understand how the payroll accounting has a difficult task because there is only one clock-in station, and employees' jobs can change midday which would not reflect their scheduled job; if they are not informed of the job switch, pay rate isn't switched and can easily be overlooked.
I've had that problem already, so I'm trying to solve it...

I currently have 5 sheets set up: "Training", "Bussing", "Banquet", "Houseman", and "Total Hours".
Each sheet has the same layout with dated weeks labeling the rows, and the columns labeled Friday-Thursday (reflecting the pay period) and four cells under each day with punch in, break out, break in, punch out.

What I'm trying to do is feed the data from all the sheets into "Total Hours" and if they overlap have an error come up or something.
In theory, I think I need to VLookup multiple sheets and make sure they don't overlap. Can't figure it out.

Once I do that I can reflect the "Total Earnings" line of my paycheck with the hours. Each sheet has the sum of the respective hours per pay period and I can multiply the hours by the rate and have current total earnings per job and eventually construct a sheet called "Total Hourly Earnings".

I also have noticed the gratuities portion has been significantly low. My paycheck has two sections for gratuities, one based on banquet and the other on bussing (a Server sheet will be added once I start serving).

Banquet is based on a portion of total gratuities amongst all banquet servers, and I'd like to calculate the portion the house takes based on # of servers and total initial gratuities; that is, if they're willing to disclose that.
Secondly, servers are advised to tip 4% of sales or more and I busted my butt this last paycheck and it seems rather low. So something on my paycheck is wrong, either the house is taking it when they shouldn't, the servers aren't tipping out right, etc.; so I would like to backwardsly find out what total server sales should have been and request that number as well.

Any help would be appreciated, and if it all works out I'd be glad to share my template.

Hello all,

I have the following problem. As you can see in the attached spreadsheet I have a table with a number of columns that have different values.

One of the columns has values of the days of the week.

What I want to do is the following, get the average of the values of each day of the week, for each column. How can I do that with a formula?

I have tried using MATCH, SUBTOTALS etc but could not manage to get anything different other than the overall average.

In the real spreadsheet I have there are about 13000 lines where the days of the week are repeated, so I need something that can find all the records of a specific day and return me the average of all the values (column by column) that relate to that day.

Any help appreciated.

Regards,

Kostas

p.s I have tried the =sumif(x:x,"text",s:s)/countif(x:x,"text")
It seems be working really strange cause I only get extremely low values which shouldn't be right,

at the end of each column I also have a total average of the column, but when I use the sumif/countif trick it includes the average at the bottom in the new calculation. Is there a way to avoid that without specifying a range of rows for each column? (probably something with the autofilter)

cheers

I am being tested by Excel right now and need a very 'For Dummies' version on how to do this, if it is even possible;

I currently have hours 00:00 to 23:00. For this exercise, I am not interested in Minutes or half hours. For each Hour there is a number (Ex. 2, 3, 4). The number represents staff available at the given hour. The purpose of this excercise is to give support to these staff members. I have a 7 day schedule as the shifts change from Monday to Tuesday, etc. My bottom 2 rows consist of Total Weekly Hours and Total Annual Hours for the corresponding Hour. For this exercise, I am also using $20 as the fixed amount per hour.

What I want to show;

I would like to add an interactive clock to this spreadsheet that has a start hand (maybe green) and a finish hand (maybe red) that I can adjust. When the adjustment is made, I am looking to have a formula off to the right that will calculate the dollars associated with either adding time or deleting time.

For example;

1. Hour 00:00 has 2 employees, Hour 01:00 has 1 employee, Hour 02:00 has 3 employees
2. Hour 00:00 has 1,196 annual hours, Hour 01:00 has 832 annual hours, Hour 02:00 has 728 annual hours.

If the clock were to start at 00:00 and go to 01:00, the total dollar amount to the right should read, $23,920 (1,196 hours * $20/hr)
If the clock were to start at 00:00 and go to 02:00, the total dollar amount to the right should read, $40,560 (1,196+832 hours * 20/hr)

Any feedback is greatly appreciated.

I currently update this workbook weekly - it is a very manual,
laborious, and time consuming process. I am not very well versed in
VBA and I am curious as to whether or not automation is a viable
solution for this - if it is it would sure save me TONS of time.

I will explain this as best I can.

This workbook tracks Employee hours for each project number they worked
on, ORG number they worked under, and pay scale (GLC) they worked
under. More on this below.

There are 3 worksheets involved - Sheet A, Sheet 1, and Sheet 2. Data
is taken from Sheet A and pasted into Sheet 1. After ALL of the data
from Sheet A is put into Sheet 1, THEN data is taken from Sheet 1 and
pasted into Sheet 2. So, the worksheet relationships are:

Sheet A > Sheet 1
Sheet 1 > Sheet 2

I currently put Sheet A into it's own workbook and the other 2
worksheets into THEIR own workbook, but consolidating them all into one
workbook is definitely an option - I would just need to delete Sheet A
out of the workbook before sending the finished product to my
superiors.

"Sheet A" is dumped into Excel from a reporting program called
Impromptu and formatted by running various macros to delete columns,
fill in blank cells, center columns, correct font size, and delete
header rows. Sheet A contains the weekly data that I have to
incorporate into the other two worksheets. After formatting, Sheet A
is arranged as follows:

SHEET A
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A1.

Column A = Employee Name (Last, First)
Column B = Org Number (ex. 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:

- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 19 31 18 2
- Al, Roy 1.2. 101797 11 32 7 7
- Ma, Nil 1.6. 100421 20 21 8 1
- Po, Bob 1.5. 101897 21 38 7 4
- Po, Bob 1.2. 100427 10 31 8 1

As you can see, sometimes individuals have multiple rows in Sheet A -
this indicates that they worked on differing Org # / Project # / GLC
#'s.

Sheet A will ALWAYS have exactly 7 columns. There will always be
exactly 3 "hours" columns (which always represent hours for the current
pay period).

Next is Sheet 1:

SHEET 1
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A7 (rows 1 through 6 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Org Number (ex, 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns E F and G represent hours for one week - Columns H I and J
would represent hours for the NEXT week, and so forth (see 2nd visual
representation below).

VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:

- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 14 32 10 8
- Al, Roy 1.4. 101782 19 30 2 2
- Na, Bil 1.7. 100421 20 21 8 1
- Mo, Rob 1.8. 101897 21 15 7 4
- Mo, Rob 1.1. 100427 10 2 8 1

VISUAL REPRESENTATION of "hours" columns:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:

- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | ST Hours | TH Hours | DT Hours | ST Hours | TH Hours | DT Hours |
- 32 10 8 37 2 10
- 38 2 2 28 14 3

As you can see, each week another 3 "hours" columns are added to Sheet
1.

So the data from Sheet A has to be incorporated into Sheet 1 - several
conditions determine the exact way in which it needs to be pasted in.

* * Under every possible condition, only the VALUES from Sheet A should
be pasted into Sheet 1 as Sheet 1 contains formatting (coloring,
borders) that needs to remain intact.

AUTOMATION GOALS FOR SHEET A > SHEET 1:

CONDITIONS:

IF a row in Sheet A contains row(s) for an employee that does NOT
already have an entry in Sheet 1, then the entire row(s) would be
pasted into Sheet 1 alphabetically by Column A (Employee Name - Last
Name, First Name) - AND the 3 "hours" cells would need to be pasted
into the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 1 AND worked ONLY
under the EXACT SAME Org #, Project #, and GLC #, as before, the only
information that would need to be carried over from Sheet A would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week. Again,
this only applies if the Org #, Project #, and GLC # data ALL match
exactly.

IF a specific employee already has an entry in Sheet 1 AND worked under
a DIFFERENT Org #, Project #, OR GLC # than before, then the entire
row(s) would be pasted into Sheet 1 alphabetically by Column A
(Employee Name - Last Name, First Name) - AND the 3 "hours" cells would
need to be pasted into the CORRECT columns for THAT WEEK.

Now onto Sheet 2:

SHEET 2
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A6 (rows 1 through 5 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Straight Time Hours (ex. 32)
Column C = Time and a Half Hours (ex. 12)
Column D = Double Time Hours (ex. 11)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns B C and D represent hours for one week - Columns E F and G
represent hours for the NEXT week, and so forth (see visual
representation below).

VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:

- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | Name | ST Hrs | TH Hrs | DT Hrs | ST Hrs | TH Hrs | DT Hrs | TWTO*|
- Al, Roy 12 12 10 65 16 13 2
- Na, Bil 21 8 1 39 1 1 2
- Mo, Rob 17 15 5 35 12 18 2

* Total Weeks With Time Off

As you can see, Sheet 2 contains only the Employee Name, the "hours"
data, and a column called "Total Weeks With Time Off". The "TWTO"
column calculates how many 3 column "hours" entries each employee has
in THIS PARTICULAR SHEET (2). This column would move over 3 columns
every week as the weekly 3 "hours" columns are added.

AUTOMATION GOALS FOR SHEET 1 > SHEET 2:

CONDITIONS:

IF a row in Sheet 1 contains data for an employee that does NOT already
have an entry in Sheet 2, then the name cell and hours cells would be
pasted into Sheet 2 alphabetically by Column A (Employee Name - Last
Name, First Name) - the 3 "hours" cells would need to be pasted into
the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 2, the only
information that would need to be carried over from Sheet 1 would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week.

I apologize for the long-windedness.

Is this easily automatable?

The assistance that the contributors of this group provide is
invaluable. Any and all help is sincerely appreciated!
Thanks so much!

- Kobi

CAN SOMEONE PLEASE HELP ME . i have an assignment for class and i am totally lost . What if people split a dinner check using the principles of the
progressive income fax that is central to our tax code? Five
lifelong friends of various means meet once a week for dinner
and split the $100 check according to their ability to pay. Tom,
****, and Harry are of relatively modest means and pay $1.
$4, and $9, respectively. Ben and Ken are far more prosperous
and pay $18 and $68, respectively.
The friends were quite satisfied with the arrangement until the
owner offered a rebate. "You are excellent customers, and I will
reduce the cost of your meal by $15." The question became how
to divide the $15 windfall to give everyone his fair share? The
proprietor suggested that they allocate the savings according
to the amount each contributed to the original check. He made
a quick calculation, and then rounded each person's share to an
integer, using the Integer function. For example, Tom's new
bill should have been 85 cents, but it was decided he would eat
for free. In similar fashion, **** now owes $3, Harry $7, Ben
$15, and Ken $60. (Ken, the most prosperous individual, made
up the difference with respect to the cents that were dropped.)
The new total is $85, and everyone saves money.
Once outside the restaurant, the friends began to compare
their savings. Tom and **** each complained that they saved
only $1. Harry grumbled that he saved only $2. Ben thought it
unfair that Ken saved more than the other four friends combined.
Everyone continued to pick on Ken. The next week, Ken
felt so uncomfortable that he did not show up, so his former
friends ate without him. But when the bill came, they were $60
short.
Create the Worksheet
You will create the worksheet that is the basis for the charts.
The first sheet, which you will name Numerical Analysis,
contains the labels and data described below.
a. Enter a title in row I. In row 3, enter the following
labels: Person, % Paid, Amount, Projected Saving,
New Amount, Actual Saving, % Saving. Type Total
in cell A9 and type The original total in cell All
and Reduction in bill in cell A12.
b. Type the names, the percent paid, and the amounts
in cells A4:CS. This data is in the description of the
problem.
Calculations and Formalling
The analysis includes calculations and formatting necessary
for presentation. You will create the formulas and
select appropriate formatting options.
a. Calculate the projected savings for each individual
in column D, the new amount in column E, the
actual savings in column F, and the percent savings
in column G.
CHAPTER 3 I Charts
b. Calculate appropriate totals in cells B9:G9.
c. Enter the original total value in cell Cll and the
reduction in bill value in cell C12.
d. Format columns B through G as appropriate for the
values displayed.
e. Format the remainder of the worksheet with
appropriate colors, fonts, and font size.
Create the Charts
You will create the charts based on the worksheet values.
The charts provide information visually and help you to
analyze that information. You will create three charts: a
pie chart, a clustered column chart, and a combination
chart.
a. Create a pie chart on a separate sheet that shows
the percentage of the bill each individual pays
before the refund. Include descriptive titles and
labels.
b. Create a column chart on a separate sheet showing
the amount each individual saves. Include data
labels below for each data series.
c. Add a shape with text box describing the results
depicted on the chart. Include descriptive titles.
d. Create a clustered column chart on a separate sheet
showing the new amount of the bill and the actual
savings for each individual. Include data labels formatted
with Currency and zero decimal places and
a legend to the right of the chart.
e. Include a shape with a text box describing the data
depicted in the chart. Include descriptive titles and
labels.
Footers and Printing
Your instructor requires documentation for assignments.
You will print the data sheet and the three chart sheets
with your name, page numbers, and your instructor's
name.
a. Create a custom footer that includes your name,
sheet name, and the current date on all four
worksheets.
b. Print the worksheet and charts in landscape format
to ensure that all charts print on separate pages.
c. Save the workbook as chap33ap_dinner_
solution.

this is what I have so far !!

I am attempting to create a personalized budget spreadsheet but am stuck on one particular formula that I absolutely cannot figure out. Each bi-weekly period, I download all of my banking transactions from online & paste them into my own spreadsheet that I created. I have an added column where I have created a drop down list that houses all of my expense & income categories. For each transaction, I go through line by line & identify/select the related expense category & then this is where I am completely stuck.

I need a formula that will automatically calculate a running total of the dollar amount of EACH category in a table posted perhaps at the bottom of that particular tab.

I want each expense category to automatically update whenever a transaction is entered to align with it’s respective expense category. For example, if I enter a transaction that I then categorize as a ‘Car Maintenance’ expense, I want to car maintenance category to update with the dollar amount of that particular purchase.

HELP?*!!

Hi,

I am trying to create a spreadsheet which will generate an upload for Sage 50 Payroll.

The employee data is recorded by managers onto a spreadsheet, which I am trying to adapt for the purpose of uploading.

As the sheet needs to be used by around 12 people entering data from different departments, I need to make sure the reliability of the data is not compromised.

The payroll is split into 4 weeks, with a calculation for bonus at the end.

The upload has been offset from the main data to allow for rows to be inserted and deleted.

I am trying to formulate using VLOOKUP, but can't seem to get it to recognise the corect total for any hours over 40.

Pay element 7 in sage is for standard hours upto 40, and pay element 9 is for hours over 40 paid at a premium.

I hope this explination is enough for someone to be able to help. I have attached a sample sheet.

Many Thanks

Good morning,

This is my first post here, but I have been directed here by google in the past for many of my questions and always finding good information.

I have a spreadsheet that some of the guys in my office are using for our own "Biggest Loser". We weigh in once a week, and it keeps track of our weekly losses/gains by number and %. Kind of looks like this:

A1:Name
A2:Initial Weight
A3:Cumulative Loss
A4:Cumulative %
A5:
A6:Week 1 weight
A7:Weight Loss
A8:% Loss
A9:
A10:Week 2 weight
A11:Weight Loss
A12:% loss

etc., one column per person. After each weigh in we put the current weight in, and it calculates how much weight was lost or gained, and the % (we're going by % of total for keeping "score"). But at the top we wanted to show cumulative weight loss/gain.

So far, we have in B7: =B2-B6, and in B3 =sum(B7, B11, etc.) for every 4th cell. But right now, with just one weigh-in complete, we just see #VALUE!. Since the rest of the cells in the sum series don't have an answer, its not able to count them correctly.

I would like a cumulative running total in the cell, taking into account cells we havn't gotton to yet, so we dont have to keep expanding the SUM() range each time.

Hope I wasn't too confusing there. Thanks for your time!!


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