Free Microsoft Excel 2013 Quick Reference

- Need formula when Cell A=X then change Cell B=0
- Auto fill cells depending on Drop down List selection
- Summing across worksheets from first to current sheet
- Figure Payroll...
- Percentages problem
- Calendar Formula Problem
- Issue with dates?
- Need formula help
- Rota formula
- capacity planning: 5-day rolling basis
- If value shows up more than once, then pro-rate according to other values
- Solver and Time based functions
- Cross checking paycheck with personal records- multiple jobs, gratuities, etc.
- How to use autofilter and average together? or something similar.
- Inserting an Animated Clock in Excel Worksheet
- Can This Be Automated?
- Need:help
- Budget Analysis
- Sage Payroll Upload Using VLOOKUP
- Summation on alternating cells when number is present

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

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.

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?

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

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

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.

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

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

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!

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.

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

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

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

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

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

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.