YouTubers Love Excel 12: Pivot Table AVERAGEIF SUMIF/COUNTIF

Hey all,

This is my current woe .... i've got 2 columns of data.
One o them showing the market penetration of hotels (%).
The second column showing (also in %)their Growth Over Last Year's market penetration....GOLY.

Also, all hotels have a fair mkt pen. which is 100%. The ones that do actuals better than their fair shares have figures above 100%.

Here's what i have to do (manually.... and its killing me). I need to rank the ones that did over 100% in the first column keeping in mind their growth over the last year...... once i'm done ranking the ones over a 100%... i need to do the same for the ones below 100%....that is 97.6% or whatever the figure is.
Well, the thing is ... Although the data here is significantly lesser than what i have to work with....Its irritating that excel doesn't understand conditional ranking....oh..and before i forget.....i operate on Office 97/2003 sometimes....So if there are any solutions, that anyone would like to suggest, they also would need to work on it...not jus on Office 2007...

Cheers to all who wanna try breakin this.....(Also to those who'll give up and meet me at the local bar after work ...

P.S- Do lemme know if you need a screen shot or somethin to help figure it out better.

Warm regards
DJ

.. . . not!

Just fixed 5 formula errors in a workbook I maintain. (Yes, I do make
errors in spreadsheet formulas.) The original and changed formulas were

From
=IF(ISNUMBER(C11),MIN(C11,Limit-C11),"")
=N(Summary!C9)+N(Summary!C9)
=N(Summary!C10)+N(Summary!C10)
=N(Summary!C11)+N(Summary!C11)
=N(Summary!C12)+N(Summary!C12)

To
=IF(ISNUMBER(E12),MIN(E12,Limit-E12),"")
=N(Summary!C9)+N(Summary!E9)
=N(Summary!C10)+N(Summary!E10)
=N(Summary!C11)+N(Summary!E11)
=N(Summary!C12)+N(Summary!E12)

That's it! Old file size 410KB, new file size 383KB. While I shouldn't
complain about a fluke REDUCTION in file size, Excel is just too
bizarre with regard to file size changes.

Hi,

I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.

Thanks!!

Hi,

I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.

Thanks!!

Hi,

I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.

Thanks!!

I am creating a time sheet for hourly employees that will be the basis of for a payroll entry that wil be entered by an accountant. I'd like to consolidate the process so there is no room for error.
Payroll is done bimonthly so there are 14 days to a pay period. The time sheets reflects one employees breakout of fourteen days. Let's say there are 10 employees and there is a different time sheet as a sheet (whose name is the employee's name) for each. There are three fields populated on each sheet:

1.) Column E-J (merged) is the Job
2.) Columns L-N (merged) is the Category
3.) Column P is the Percent of the day spent at that job doing the specified category (i.e. painting, etc).

Project Category % of day 5111kmbk_s - Sitework and Exterior 153708 Misc Concrete 10% 5111kmbk_a - Apartments and Interiors 250201 Finish Carpentry Labor 65% 5111kmbk_a - Apartments and Interiors 380202 Paint Interior Labor 25% 0% 11/2/2009 100%

Exmaple show above. For each day, there are 4 rows and an employee can submit 1-4 entries for each of the fields so 1-3 rows could be blank. As demonstrated, the employee can enter the same job twice with a different category. The four rows in Column P must add up to 100%. Right now there is a conditional formatting that bolds the items in red if they are 100% because I don't know how to use data validation in thsi unique scenario where they are given the chance to enter 1-4 rows. If someone could help with this that'd be great but this is not he main goal.

The primary goal is to have a formula,macros, etc tally all the unique Job/Category combinations and pull them into a different spreadsheet so there are no duplicate entries and every combination is listed. From there I will average the percentages and distribute the payroll number generated through a different system based on the employees clocked hours to each unique job and catgegory based on the percentage attained.

PLEASE HELP! I'm a complete rookie at excel so the more detail the better. Thank you SO MUCH for your help and happy thanksgiving!

Hello, I have (what I think) is a pretty simple task, but I'm not sure the best way to go about it, hopefully someone can steer me in the right direction.

Here's what I need:

I'm working on a guide to my city, and need to organize everything into an easy to read spreadsheet.

The guide is split into several different categories; hotels, places to eat, hiking trails, etc, etc. With each category then having a list of places to visit.

Can anyone suggest the easiest way to organize this into excel?

I hope I'm making sense, but if you need more clarification please ask, thank you in advance.

Hi guys,

Newbie to the forums and also fairly new to lookup tables.

I have a sheet of data related to an audit - it is related to meds and weight loss, but there are a lot of other columns of related data. There are a number of drugs, and the weight loss is either a positive or negative number depending if it was lost or gained. I would like to average the positive and negative numbers seperately so I can see what the average loss was if people lost, or the average gain if gained. I was able to do this on the original sheet fairly straightforwardly with the AVERAGEIF function with the criteria ">0" for positive values and "<0" for negative values.

I am more interested in seeing the average weight loss/gain for each of the drugs. For other parameters a straightforward pivot table worked but I cannot see a way to use a pivot table to average only the positive or negative values.

Is there any way of incorporating the AVERAGEIF, or a similar function, into a pivot table, or is there some other way of working around this?

I hope that this is clear and I hope that someone can help - trawling through hundreds of entries manually would not be pleasant!

Thanks,
Justin

P.S. can't upload original file but can make a dummy one if it will help.

Hello everyone, There are a ton of books available but I am looking for an Excel 2007 pivot table and Excel 2007 pivot chart 'power-user' book.

Does anyone have a suggestion?

Thank you.

Ok, I figure sombody must have come across this before...

Problem 1) So I have a pivot table with a linked pivot chart(graph)
I want to be able to automatically print an idividual chart for each of the selections for the pivot table. (instead of the go to drop down, make selection, click to chart, ctrl-P, etc..for each chart.)

Problem 1A) So what I REALLY want is to create a PDF of each chart...the problem here is that adobe is not part of excel, so the macro commands dont work there, so I cant enter separate titles for each PDF or even click OK in the pdf window as part of the excel macro.

Theoretically, I should be able to copy and paste the title of each chart as the name of the associated PDF...but I can't seem to make this a reality...

Josh.