Re: Nested IF Mutiple Logical Tests
Thank you!
Yes - I thought of vlookup - that was plan B
But wanted to try the nested formula since I usually have issues with parenthesis
I appreciate you taking the time
Cheers
Leslie
Re: Nested IF Mutiple Logical Tests
Thank you!
Yes - I thought of vlookup - that was plan B
But wanted to try the nested formula since I usually have issues with parenthesis
I appreciate you taking the time
Cheers
Leslie
I have read through a few threads to match some IF Formulas to my challenge and thought I came pretty close
I'm hoping it has to do with a , or ) misplaced since the error message is "too many arguments" and I am using 2013.
Basically - based on cell A43 where employees calculated score is they are :
[TABLE="width: 419"]
[TD="colspan: 6"]Highly Effective: 3.5 - 4.0
Effective: 2.5 - 3.4
Needs Improvement: 1.5 - 2.4
Ineffective: less than 1.5[/TD]
[/TABLE]
My Formula try
=IF(AND(A43>=3.5,A43<=4.0),"Highly Effective",IF(AND(A43>=2.5,A43<=3.4),"Effective"),IF(AND(A43>=1.5,A43<=2.4),"Needs Improvement"),IF( (A43<1.5,”INEFFECTIVE” ,"N/A")))))
What am I doing wrong?
Thank you
Re: Parse URL
Thank you so much
Have a wonderful day
Re: Parse URL
Thank you so much
Worked perfectly
Quick follow-up question
what does the ~ do in the first formula?
Thank you
Leslie
I have a list of very long URLs which vary in length
I need to parse the URL to 2 other columns breaking up the URL to its Path and (last occurrence of /)File name = 3 column URL/Path/FileName
I have been playing with Right/Mid/Len Find but seem to miss the right combination
I have attached a sample
Thank you
Leslie
Re: Pivot table date counts
Many thanks -
Makes perfect sense - I must have been over complicating it
Got it
Appreciate your time
-
Quote from iwrk4dedpr;708385Your file you attached does not have the source data for the pivot table.
Add a new column to the data table .... and add it to the pivot table
Re: Pivot table date counts
The names might change every day depending on the date of the filter
can you give me an example of the countif formula I use it all the time but here I don't want a specific student if it is changing every day and the cell location might also change depending on the number of students and dates associated with each student
thank you
Working with a pivot table displaying a list of students and their tardy dates from the beginning of the semester
each date is displayed and a total of tardies for each student
Administration sometimes wants to filter only on a specific date so obviously the count changes
Principal wants to display for each student their totals for the semester as well as filter on a specific date - not losing the totals column
I know I can create a formula but if student list changes not sure if that is a solution
Any thoughts
Thank you
Re: Pivot table updating to linked workbooks
Afraid of that
Thank you for your time
I am familiar with PowerPivot and use it
However these inventory files are in a SharePoint Library and our organization has not moved to supporting PowerPivot for SharePoint servers yet
Re: Pivot table updating to linked workbooks
Ok I will try these files
Bottom line - Pivots don't update on refresh when source data range is correct and the items are viewed in the row but do not display in pivot for correct calculation
Thank you
Re: Pivot table updating to linked workbooks
Not sure if files can help-too big to send and smaller versions breaks all the links
Master inventory -links to all school files (Thousands of rows some blank to add new items - not entirely blank as to break the source data range)
Monthly inventory - is a Base school linking to multiple schools that report to it
(These sheets are thousands of rows)
Pivot tables are reporting out the multiple school inventory totals
Problem
source data does not change -- range is correct for pivot tables
contents of the added inventory items display in the monthly sheet correctly
Excel simply does not refresh the contents of these cells for calculations in the pivot table
I understand the issue with pivots and adding/deleting rows/columns
but if the pivot table is pointing to the right cells why not see the correct contents for calculation on refresh? or edit links update values?
I tried to send files but links broke or file is too big
Thank you
I have created a manual inventory system (Dept won't buy real inventory system) in excel.
I have monthly sheets linked to the inventory items workbook. When new items are added (always) the monthly sheets display the new items.
the pivot tables have been setup for each month.
I would think that refresh updates all the links. I have even gone to Data > edit links and updated values.
Problem -- the links are updated on the monthly sheets for all new items/values Perfect
but the pivot table does not update the items/values. on refresh or edit links to these new items
I have to re-create the pivot table for the items/values to be current.
Since I have over 127 sites - the idea of re-creating the pivot tables each month for each site is impossible
Any ideas?
Thank you
I am building a summary report from files on a SharePoint Library.
SUMIF does not work unless the linking files are open which I can't do.
I searched and found out that SUMProduct is the function to use.
I am having trouble converting my SUMIF formula to fit the SUMPRODUCT argument
This is the model example I found online:
=SUMIF('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)
To
=SUMPRODUCT(('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332="M&R Parts D")+0,'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)
This is my formula:
=SUMIF('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$S$7:$S$638,"Frozen/Refrigerator",'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$R$8:$R$638)
My SUMPRODUCT attempt:
=SUMPRODUCT(('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$S$7:$S$638="Canned and Dry Goods")+0,'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$R$8:$R$638)
Any Thoughts. I have attached a sample of my report each school has a separate file in sharepoint on our network - I would be linking to. Thank you
Re: Date fill series
I thought as much
I did a similar formula but yours is much better!
Many thanks for your time
Cheers
Leslie
Greetings-
I am well versed in the auto fill for dates and standard fill series such as weekdays etc
But what I am trying to do seems like an easy request but I cant get it to work
I am trying to fill a series of dates for only tuesdays and thurdays
I can do tuesdays only by a series step of 7 or thursday with a series step
but not both
Such as:
[TABLE="width: 195"]
2/05/13
[/td][td]Tue
[/td][/tr][tr][td]2/07/13
[/td][td]Thu
[/td][/tr][tr][td]2/12/13
[/td][td]Tues
[/td][/tr][tr][td]2/14/13
[/td][td]Thu
[/td][/tr]
[/TABLE]
I thought if I selected the cells that had the date sequence it would follow it so only give me dates for tue / thursday/tuesday, thurday
but it doesent and any combination of series steps and stop values does not seem to work
what amI missing?
Im sure it can be done in VB but this is for a user not me
Many Thanks
Re: Word 2010 Mail Merge multiple Rows to 1 Label
Quote from arno;633218Display MoreHello,
im having the same problem.
One thing is for sure... its not easy.I can select all the records by doing
{NEXT}{MERGEFIELD fieldname \*MERGEFORMAT}
for like 20 times the problem is then that you get the values multiple times.
There is a funtion to skip a record {SKIP IF.........}, but i don't know how to compare the old value with the new value.
Also the number of simular records is variable, so it would be super if you can use a loop for this.Conclusion, i'm still looking for the right solution
______________
many Thanks - Im sure others have a similar need
My workaround I'm thinking Access or Crystal reports
Re: Hours Worked Sum Pivot
Quote from Herbds7;629866Subtotal(9/109,xxx) includes/ignores hidden values.
Never knew that thank you!
Leslie
Re: Hours Worked Sum Pivot
Quick Question in the Subtotal formulas--
Why 109?
=SUBTOTAL(109,[Pay per Day])
=SUBTOTAL(109,[Hours Worked])
Re: Hours Worked Sum Pivot
Thanks so much - got it
The right custom format makes all the difference
Much appreciated
Cheers
Leslie
Quote from Herbds7;629764Excel 2010 Table, PivotTable
Custom Format
http://8936e6d9f85be098ae08-f26298a42cfbd9c5a8f09838ce8d1b1e.r90.cf1.rackcdn.com/09_24_12.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
Trying to help timekeeper to tally total hours worked by employee
When doing a pivot for sum total hours worked for FY13 it does not calculate correctly
I understand they formatted that column/custom h:mm
but when I change the 8:00 hours to a number I come up with 0.33
I am attaching a sample file
I just want them to get a running total of hours worked/pay
What am I doing wrong?
Thank you