I created a spreadsheet to track my bowling scores by date by lane
Date Lane game 1 game 2 game 3 total
What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.
I am trying to make a function which will take a date as input and display the season for that date. i.e. the input 1-Jan-1992 will return "Summer" (southern hemisphere seasons)
View 10 Replies View RelatedI have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I am trying to create a list where the user will select which "Lanes" to assign to "Zones" -- for example... Zone 1 will be Lanes 1 through 10. Zone 2 will be Lanes 11 through 25. Etc, etc... I want to make it very easy for a user to change which lanes are assigned to which zone. I thought about using data valuation but I need Zone 2 to automatically bump up the *start* lane if you change the end of Zone 1.
Example: Zone 1 is set at Lanes 1-10, but I want to extend it to Lane 12. How can I make it so that Zone 2 automatically starts at Lane 13 after I set Zone 1? I would like to use drop-down menus for this because it seems like that would be the most user-friendly.
i have this formula in cell C14
="Today Is The "&U3-V7+1&"th Day Of "&IF(VLOOKUP(U3,AC10:AD17,2)="winter","Winter","")&IF(VLOOKUP(U3,AC10:AD17,2)="Fall","Fall","")&IF(VLOOKUP(U3,AC10:AD17,2)="Summer","Summer","")&IF(VLOOKUP(U3,AC10:AD17,2)="Spring","Spring","")
it returns " Today is the 92th Day Of Summer " *number and "Season" changes*
i want changing the 92th when the number changes.
so if it was 1 it would be "1st"
2 would be "2nd"
3 would be "3rd"
than 4-9 would be "4th,5th,6th..etc (as normal)
also counts for double digit number
21st 32nd 43rd
and also counting the days..today is the 92 day of summer but when it gets to the 1st day of fall it going to say 94th day of fall..when i want it to say 1st day of fall... than start counting the days of fall than so on for each season..
here are the dates for the seasons
3/20 is spring
6/21 is summer
9/22 is fall
12/21 is winter
3/20/2010 is spring
6/21/"" is summer
9/22/"" is fall
12/21"" is winter
I am trying to combine sub total if the formula matches a certain criteria.
I would like an average of the data I have previously used an average if formula =averageif(D:D,L4,J:J) but now I would like the answer when I filter the data.
=SUMPRODUCT(--($A$2:$A$12=$D$24),--($F$2:$F$12=$B$25),($H$2:$H$12))
This is the current formula I have, however I am working with averages. After the first two arguements are done I want the overall average of columun H.
I am trying to work out a formula to determine the maximum score a player has had in one season.
Spreadsheet has a row with say 45, 56, 23, 67*, 43 .... ( the * means Not Out ). Using the maximum formula disregards the record with the asterisk.
My other option is to store the * in a separate field next to the runs.
I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B.
In other words find the average price for the total of each item..
A B C
Code Qty $
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2
I am working on an inventory report that the owner would like broken out between in-season items and out of season items. Items with no season code are always in season. Items that have seasonality have a 2 digit season code. A as if Jan, B is Feb, etc. A season code might be CE or KB. CE would be in season from March until May. KB is in season from Nov through Feb and out of season the rest of the year.
We are currently in month G. Is there an easy way to use the two digit season code and determine if G falls in between those two months.
I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
View 1 Replies View RelatedOn a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.
if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.
[Code] .....
I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.
When i use the following f(x):
{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000
I am trying to get and average price for a total quantity of jacks
Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9
I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?
i have a list of parts that adds up and shows a total at the bottom (great, works perfectly)
in the next column i have a percentage (margin) which again works perfect
ie.
Col A Col B Col C Col D Col E
1 ENGINE 4500 5000 11%
2 SURCHARGE 1800 2000 22%
3 OIL 1 2 100%
Totals 6301 7202 ??%
what i would like to do (forgive me if i have missed something) is for the total value in colum D to add up but for the % in column E not to be included in the total %
Is it possible to have a situation that when the word"SURCHARGE" is in the row then the % will not be added to the total %
In other words i need the surcharge price to be included into the final price, but the surcharge would give me a false figure so that needs not to be included in the total %
I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.
Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)
Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.
View 5 Replies View RelatedI've attached a sample workbook. Data Table 2 on the Data Tables sheet is what I'm after. Basically I have a formula that sums up quantity 1 (Q1) and quantity 2 (Q2) for all widgets at a site name and month. So for example, go down through column site name and column month and add up all of Q1 for site1 in the month of Jun 2014 (all widgets). This works but it shows duplicate totals which makes sense as there are mutliple widget types at each site. I think the example workbook explains it better. Note the Pivot Table sheet is an example of what I'm showing currently and what I'd like to show. While I understand that depending on how I set up the Pivot Table I can get similar results using either Table 1 or Table 2 but the other thing is that my users like to do some filtering of the data sheet itself without regard to the Pivot Tables.
sum quantity and show total for each site.xlsx
formular to show a coinage total in a spread sheet im sure its to do with < > but new to this and dont have a clue any help apreciated. This is what i am trying to achieve.
excel formula If A1= 10 Then B2= 0
If A1 =7 Then B2 = -3
If A1=13 Then B2= 3
I'm fairly new to Excel (2007).
I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.
What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.
I am building a Pivot table and I need to show ONLY the Grand totals at the end of the table. In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns.
Column Labels
Pipeline
Best Case
Commit
Closed
[Code]...
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies View RelatedLet's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
I have a column for suppliers names and a total column The suppliers names differ. How can I create a separate column to show the total of spent for each supplier. I'm not up on excel code
View 4 Replies View RelatedI want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View 2 Replies View RelatedHow can I work out the following in hours an minutes: -
Cell A1 = 12th May 2007 20:00
Cell B1 - 14th May 2007 14:30
or should I put the dates and times in seperate cells? I would like the answer to show 42 hours 30 minutes
I have a very basic Excel Gradesheet that's designed to simply record letter grades, and show the grade average as the year progresses. I've hit a snag with problem in the formula which shows a grade of "F" - when the grade range can only be that shown on the table (A+ to E-). I've attached the Excel sheet so you can see what I mean.
View 8 Replies View RelatedFrequency
Formula to show the final/total a numeric values appears in an adjacent column.
I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).
I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.
Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}
However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R
NB: My list is sorted by column C to ensure all numeric values are in ascending order.
Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final