aggregate calculations

I have two columns of data:

Date      Value
1/1/05    9
1/1/05    10
1/1/05    12
1/9/05    16
1/9/05    4

There may be a variable number of dates, and does not contain a
complete list of consecutive dates (i.e. some dates have no associated
data and are not in the list)

I want to produce a calculation the that subtracts the MIN for each
date from the MAX of each date.  I do not necessarily have to display
the max and min values - I am really after the result

Date    (max - min)
1/1/05  3 <12-9=3>
1/9/05  12 <16-4>

What is the best way to do this?
I have experimented with pivot tables, and have easily gotten the min
and max by date.  I was looking at the calculated field option to
subtract the min from the max, but it doesn't work as I expected.  Can
the Pivot Table calculated field do this?

I have also looked at subtotals, advanced filter, and dmin / dmax
calculations.  None of these seem to easily deliver what I am after.

I am not against writing the VBA to "scan" my data - but I was just
wondering if there is an eaiser way -


i_makino (4)
5/26/2005 3:32:42 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 0


Similar Artilces:

Calculating Expiration Dates
I am tracking seminars staff attend. I need to track when certain training expires (First Aide Training needs to be done every 3 years). Some training is good for 1 year and others are good for 3 years. These are my fields: Type:(Dropdown field with CPR, First Aide) Date: Date of Seminar Today-Date: Date()-[Date] 1Yr: IIf([Today-Date]>365,"Yes","No") 3Yr: IIf([Today-Date]>1095,"Yes","No") FirstAide Expired: IIf([Type]="First Aide",[3Yr]) CPR Expired: IIf([Type]="CPR",[1Yr]) I get correct information returned on my 1YR and 3...

HELP! Calculate HQ Item Cost as weighted average
So here's a fun little thing my partner apparently overlooked way back when and I never noticed until this morning... The HQ Manager Configuration setting "Calculate HQ Item Cost as weighted average" was left at FALSE instead of being set to TRUE. We've been running this way for nearly two years, and I'm guessing that every inventory valuation report we've ever run from HQ was horribly inaccurate. I'm going with the ostritch mode on this one--I am simply not going to try to figure out how much this may have cost us at tax time last year... To correct the...

Calculation problem where a fixed charge is involved
Forgive me for asking guidance on something which is probably quite simple. I've set up a worksheet to calculate all the hidden costs of on-line auctions and it is the PayPal aspect that is giving me grief. PayPal charge me 3.4% + 20p per transaction. If I use: =SUM(B2*0.034+0.2)and C2 and D2 and so on... obviously gives me the right answer but, if I just enter the formula at the beginning of posting sales and before any purchase or bid has been made, when I want a nil balance to show, this method shows 20p. How do I amend the formula for that aspect so that the 20p set c...

Calculating absence periods in a rolling 12 months in excel
Help! Tring to put a spreadsheet together that calculates how many absence periods fall in a rolling 12 months. Have created a listing of absences for an individual for example, with columns for first date of absence and last date of absence and number of working days lost. Now wish to somehow calculate the total number of absence periods (not days) that fall in the last 12 months in one cell. 12 months prior to todays date (=now()). ! Any help would be appreciated. Many thanks Mark Hi if the starting date is in column A and the ending date in column B try the following formulas C1: =NOW...

I am trying to calculate percentages in a query based off a total number. There are two fields Country (Text Field) Individuals (Number Field) This is what it curerntly looks like. Country Individuals Belgium 2 Ireland 1 Scotland 1 For a total of 4 people. This would be an example that I want to achieve in the query. The percentage field would tell me the following based on the above numbers. Belgium 50% Irland 25% Scotland 25% Any ideas would be greatly appreciated. -- Rose "Ofer Cohen" wrote: Click to show or hide original message or reply text. -- Rose ...

Calculating days and dates
I need to calculate the number of working days (ie Monday to Friday) from the date entered in cell D4 (22/07/2005). This needs to update each day in cell E4. I can do this not based on working days, by entering =DATEDIF(D4,TODAY(),"d") but where in the formula do I reference it to working days or network days? HELP! -- HDV ------------------------------------------------------------------------ HDV's Profile: View this thread: You can use the ...

How do I calculate ratios between 2 cells?
Hi, I need to work out a ratio between two figures (eg. the number of replies to sales made etc). Both cells are in the same worksheet. Sounds easy but I can't get anywhere with HELP. If anyone can help I'd very much appreciate it. Kind regards How about = A1/B1? A1 has replies and B1 has sales You could even format it % to get percent value. best wishes -- Bernard V Liengme remove caps from email "Excel Novice" <> wrote in message >...

Multi-field query calculations
Hi all, Admittedly, I have not programmed anything in years and Access 2007 seems to have some differences, but I am embarrased to say I can figure out how to create a multi-field expression in a query that will provide the following results in a report: Here is what I want to achieve: Reorder Alert: If the Physical Inventory is equal to or less than the Reorder Point then show the Reorder Amount in the report. This is what I did based on what I read, but it doesn't work. What am I doing wrong? ReorderAlert: IIf ([PhysInventory] = or < [ReorderPoint]), [ReorderAmoun...

How do I calculate my GPA in Excel? please help me!
I'm doing this project for school, I have collums for: Units, Grade Recieved, Grade Points, how do i calculate my gpa, using excel to do the calculations -- Message posted from Hi! Problem: I don't know what a GPA is: I don't know the rules fo calculating it. How do you calculate it not using Excel? If it is the average of som numbers, are you familiar with the way(s) to calculate averages? As the song says: tell me more... Al -- Message posted from Take a look at

=9/10 does not calculate --- did I change an option? #2
I am accustomed to calculating in a cell by typing "=" and then a formula, such as 9/10 or 2+8, and having the result appear in the cell. Now, the calculation does not happen, and it just puts the text "=9/10" in the cell. I must have changed an option, but am not able to find one that gets the old behavior back. Any help on how I might restore calculation would be greatly appreciated, many thanks in advance. Jim Did you read the earlier replies? Format the cell as General (or number) and open the cell for editing then close it best wishes -- Bernard V Liengme Microso...

Date and Time calculation
I am looking to calculate the difference between two dates and times bu am unable to get it to compute more than 24 hours. A1=10/18/2004 8:00 B1=10/18/2004 8:00 TEXT(B1-A1,"h:mm:ss") This gives 0 hours. What is needed to convert the date as well into th equation. This is time sensitive and look forward to your reply. Thanks -- sacravi ----------------------------------------------------------------------- sacravia's Profile: View this thread: Hi =B...

Calculations within a report
I have a database where I keep track of invoices. Some of the fields are: Vendor, Invoice #, Invoice Date, Service Period, Service Period End, and Amount. The Service Period is entered as 1/02/2008 (for example). I have created a report which includes all of this information. The report is ran off of a query in which the criteria is set to show all of the invoices for the previous quarter. When using the Report Wizard, I told it to group by Vendor, then by Service Period per Month. (This created a new field called “Service Period per Month.”) When the report is ran, any dates that s...

Calculate numbers between 2 dates
Hi I was trying to figure out the following. If i need to calculate sales between 2 dates which are given C1 C2 C3 C4 C5 C6 C7 C8 C9 ID FrstVis VAr1 19991230 20010228 20030531 Flag Cumlsales #Mnth 1 19980430 20040131 431 991 756 1 ? ? 2 19970630 20021130 811 555 245 0 ? ? 3 19990931 20031231 823 475 386 1 ? ? I need to ca...

How to calculate pass/fail percentages entered on a spreadsheet?
I am trying to set up a spreadsheet with driving test results including one column to say pass one to say fail and calculate the percentage of each on a monthly basis. I can enter the info but don't know how to calculate the percentages Hi I've just assumed the pass and fails are in Col A. I then put this formula in B1 to Count the number of passes and divid by the number of Pass or Fails =SUMPRODUCT(--($A$1:$A$1000="*pass*"))/COUNTA(A:A) Same formula to find Fails =SUMPRODUCT(--($A$1:$A$1000="*fail*"))/COUNTA(A:A) Then Format cells as % VBA Noo -- V...

Calculated Fields and criteria
Good afternoon.I am a newbie to Access but very familiar with Excel. Here is the setup: 3 tables linked to Oracle database 1 query using the 3 tables. Relationships all to one record (Product_No) I have created a query to give me all the info I need except one field required a calculated field which works correctly when I run the query.The problem I am having is that when I enter ">0" in the criteria under the calculated field, I get no records to show and I know there are 314 records before I enter the criteria and 182 are >0.(I counted) One of the records is a sum.I ne...

how to quick calculate sum of colume A1 to A115?
How to quick calculate sum of colume A1 to A115? Or, do I always have to set up formular =sum(A1:A15) to get the sum? =SUM(A2:OFFSET(A2,MATCH(999999,A:A,1),1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony B" <> wrote in message news:O6%23S2HZTIHA.5016@TK2MSFTNGP06.phx.gbl... > How to quick calculate sum of colume A1 to A115? > > Or, do I always have to set up formular =sum(A1:A15) to get the sum? On Wed, 2 Jan 2008 17:36:23 -0600, "Don Guillett" <> wrote: >...

Calculating Total Number of Minutes in a Clock Setting
Hi, I am using Excel 2000 and have a cell that contains the format of time (hh:mm) and would like to see the calculation, to put into another cell, that would produce the exact number of MINUTES in the day that the time represents ie, 23:00 would equal 13,800 minutes. I certainly would appreciate guidance on this subject -- Thanks in advance for your help... Larry H. Hi, If A1 contains the time as hh:mm, use the following formula in some other cell. =HOUR(A1)*60+MINUTE(A1) Regards, B. R. Ramachandran "Larry H" wrote: > Hi, > > I am using Excel 2000 and have ...

Calculate Time Span Between Points
Hello All: In the code below I have a list of Points, I want to calculate the timespan in seconds between the points, What is the best way to do this? Thanks Stuart public class TrackPoint { public virtual int Id { get; set; } public virtual DateTime? Time { set; get; } public virtual decimal? TimeSpanSeconds { get; set; } } List<TrackPoint> trackPoints = new List<TrackPoint>(); trackPoints.Add(new TrackPoint { Id = 1, Time = DateTime.Parse("1/2/2010 4:00:00 PM"), }); trac...

More than calculations in time
I have set up a time sheet and want the minimum time taken at lunch to show no less than 30 minutes. At present there are start and finish times for both am and pm. The lunchtime column is a simple calculation taking the second start time from the first stop time thus producing a time figure ie 00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins has been taken irrespective of what has actually been taken. Can anyone help please? Thanks Peter Peter, A formula along the lines of =MAX(A2-A1,TIMEVALUE("00:30:00")) where A2 is the end of lunch, and A1...

Report Writer Question
Hello. In GP8 , is it possible to create a calculated field in report writer w/ several levels? Something like this: IF Field A = 1 THEN "Red" ELSE IF Field A = 2 THEN "Blue" ELSE IF Field A = 3 THEN "Green" ELSE "" If I cannot do this, I am thinking I will have to create 3 fields (one for each condition) and stack them one on top of the other on the form and 'hide if empty' .. not very pretty .. Any suggestions? Thank you, Maria. You will have to use multiple fields, but you do not have to stack them one another Calc1...

Auto Calculate Keeps Resetting to Manual
Cannot get this to stay at automatic. Any thoughts why? New workbook, nothing complicated (b2/b3). I save the document, reopen and set to recalculate. The next day when I open, it's back to manual. I hate when things weird out. The recalculation setting comes from the first workbook you open. Do you have another workbook that has its setting to manual? Regards, Fred "Shirley" <> wrote in message > Cannot get this to stay at automatic. Any thoughts w...

How do I calculate the angle between two lines on a chart
I'm trying to calculate the angle between a Target Line and a Trend Line (both are straight lines) on a chart. Any ideas as to how I would do this? Thanks SLOPE is a measure of the angle a line makes to the x-axis So slope2-slope1 is proportional to the angle between two lines Now do your homework best wishes -- Bernard V Liengme remove caps from email "R.Swipe" <> wrote in message > I'm trying to calculate the angle between a Target Line and a...

Calculating a person's age
I have a database where I enter the person's date of birth. The users of the database want to be able to see the age of the person as of the day they are viewing. I thought this would be simple and entered the following into a query to provide an age field for the form: DateDiff("yyyy",[PatientDOB],Now()) However, it appears that this is only accurate some of the time and with the new year I have noticed that this is more prominent. Is there a better formula for working this out? It doesn't necessarily have to be in the query as it can be calculated in a fie...

i can't figure out how to calculate percentages in excel
hI I can't seem to get excel to show my the right numbers in percent format i'm dividing a cell of 3000 by a cell of 342 and i get 8.771929825. if i try to switch this to percentages i get 877%. what am i doing wrong? Nothing. 8.77 *IS* 877%, just as 1.00 is the same as 100%. What are you expecting? In article <>, Laserbeak43 <> wrote: > hI > I can't seem to get excel to show my the right numbers in percent format > i'm dividing a cell of 3000 by a cell of 342 a...

Calculator #2
It seems there should be a calculator function in Excel. I'm often needing to add a few numbers together before inserting them. Anyone know of such a thing? There's no popup calculator built into Excel. People often just use a cell, typing for example, =1+2+3 and Enter. If you don't want a formula in the cell press F9 before the Enter. -- Jim Rech Excel MVP Excel IS a calculator, so of course no one needs one ;-) .... but anyway, Orlando has one at and John has one at -- HTH. Best wishes Haral...