Excel 2003 :: Sum Data Depending On Week Range

Jun 26, 2014

Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.

Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.

On the front sheet, in each cell I need information to be displayed I use the following formula;

"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"

As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.

View 1 Replies


Excel 2003 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:


(this is not the actual formula but simplified, that's not really important).

It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):

I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula

(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:

Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective

I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.

View 9 Replies View Related

Excel 2003 :: AutoFilter By Day Of Week When Day Is From Custom Format?

Jan 23, 2012

I'm working with three large data sets covering a month of data in 5 minute intervals. There is a single date and time column which I am using some custom formats to list Time in 12 hour format, AM or PM and Day of the week.

Based upon the various analysis scenarios, adding some Autofilter columns is meeting most of my needs. One issue I am having is that I can filter on a single day using the built in list that Autofilter provides. But I have been asked to look at weekend vs weekday.

Since Excel 2003 only offers two filter criteria, I thought I could use a custom filter with Saturday and Sunday as include or exclude criteria. But, what I am findings is that while the Autofilter shows each day in the dropdown, certain attempts to use them within the custom autofilter do not work.

For example, equals Saturday and equals Sunday correctly returns only weekend data. However, if I try changing both to Does not equals, the result is that no data is filtered.Some of my research indicates that the problem is that the date information being presented as days of the week would need to be text strings . This seems to shed some light on the issue as any attempts at using wildcards fail.

View 3 Replies View Related

Excel 2003 :: Get Sums Based On Codes In Dropdowns By Week?

Jan 24, 2014

I am using Excel 2003.I need two distinct sums:

I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013

$1,234,567,890 in total loans

I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013

$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min

I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.

View 1 Replies View Related

Excel 2003 :: Week To Date Running Hours Worked Totals - From Thursday Till Wednesday?

Jun 12, 2014

I have two columns with total hours worked at different places and a grand total for both together in a third column.

I'm trying to sum the total hours for the week so far from Thursday till the following Wednesday for each week.

I want the wookbook to look up todays date and tell me what the total hours are for the current week

I'm using Excel 2003 - see the file attached

View 14 Replies View Related

Excel 2003 :: Auto-populate Columns From Range 1 And Continue With Range 2

Jan 27, 2013

I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.

Here's what I have as start info and where I want to get to.



[Code] ......

Excel 2003

View 2 Replies View Related

Excel 2013 :: VBA Macro For Selecting Current Week Range

May 30, 2013

I currently have this beast of a code running in Excel 2013:

HTML Code:
Dim NextCol As Long

'Copy data
Workbooks.Open Filename:= _

[Code] ........

However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.

Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?

View 4 Replies View Related

(2003) Get Day Of The Week From A Date

Jul 31, 2009

I have a list of dates in Excel that I would like to pull the day of the week it was on: for example, cell A1 has '1/03/09' and I would like it to display in B1 what day of the week that date was.

View 3 Replies View Related

Excel 2003 :: Copying Range Of Cells From One XLS File To Another

Nov 5, 2012

I'm using Excel 2003. I've got two different .XLS files, each with multiple sheets.

I'm trying to create a macro which will copy a range of cells from one sheet on one .XLS file (which is closed) to a specific place on a specific sheet on the current .XLS file (which is open).

So for the sake of argument:

I've got two Excel files: C:ApplesOldFile.xls and C:OrangesNewFile.xls

OldFile.xls is closed -- NewFile.xls is open and in front of me.

I'm trying to copy the data in ranges B6:C41 and F6:F41 from Sheet2 in OldFile.xls to the same ranges on Sheet6 in NewFile.xls. There are no formulas in these cells -- just data (numbers).

I keep getting error messages, failures to copy to clipboard, etc.

View 3 Replies View Related

Excel 2003 :: How To Use Countif For Multiple Range And Criteria

Jan 1, 2014

How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

View 3 Replies View Related

Excel 2003 :: Adding Values If Column Next To It Is In Certain Range

Sep 9, 2013

I have this code working fine in excel 2007 but I need to get it working in excel 2003.


I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.

counting columns in Excel 2003.xls

View 3 Replies View Related

Excel 2003 :: How To Pass A Parameter In The Input Range

Mar 21, 2014

I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10

View 2 Replies View Related

Excel 2003 :: VBA To Add Worksheets Named For Items In Range?

Jan 17, 2012

I some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.

I'm using Excel 2003.

View 3 Replies View Related

Excel 2003 :: Dynamic Range For Pivot Table

Jan 21, 2013

How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?

Next step, I then want to use that Named Range as the source of the data for a PivotTable.

Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.

View 1 Replies View Related

Excel 2003 :: Count Where Value In One Cell Range But Blank In Another?

Nov 21, 2013

I am trying to perform a count against two ranges of data.Both ranges contain contain values (dates in my case).I am trying to perform a count where cells in column A have data but cells in column B do not.

Is there a way this can be done in Excel 2003? I have tried numerous COUNT and SUMPRODUCT queries but have not found a way to get this to work.

View 7 Replies View Related

Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box

Aug 13, 2012

Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.

Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names

if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown

The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.

View 3 Replies View Related

Excel 2003 :: Average Of Cells Which Are Not In Range Which Ignores Any Zero Values

Dec 4, 2013

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

Test Sheet.xls

View 3 Replies View Related

Excel 2003 :: Counting Occurrences In Two Columns Involving Range?

Jan 29, 2014

I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.

formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?

View 2 Replies View Related

Obtaining Certain Data From A Range Depending On Multiple Factors

Nov 15, 2013

I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.

In column A there could be multiple entries of the same ID number.

In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.

I need to export (to another sheet) any entries with the following conditions:

A = ID number with any instance in B showing both a 243 and 400 condition.


AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E

In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:

AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E

Now I've spent ages trying different things and can't get anything to work...

View 3 Replies View Related

Excel 2003 :: Include Text Criteria In Date Range Formula

Dec 20, 2012

Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"

View 5 Replies View Related

Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)



way to do the calculation using Excel 2003

View 9 Replies View Related

Excel 2003 :: Cell Result Based On Range Of Filled / Empty Cells

Apr 3, 2014

I'm stuck using Excel 2003 to auto-populate a cell.

I have a range of dates in five consecutive columns called:

Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5

I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.

Each phase is consecutive to the next so will always be filled in from 1 to 5.

I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.

I've tried nesting some ISBLANK functions without any luck.

View 4 Replies View Related

Excel 2003 :: Matching Row Data

Nov 28, 2011

I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.

Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1

View 3 Replies View Related

Changing Range Of Sumproduct Function Depending On Length Of Data Series

Feb 20, 2007

I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?

View 2 Replies View Related

Excel 2003 :: Autopopulate Data From 1 Database To Another?

Jul 1, 2014

I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......

I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....

Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...

1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').

2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..

3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed

4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.

5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)

6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....

To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red

View 14 Replies View Related

Excel 2003 :: Web Query - No Data Has Been Found

Sep 20, 2013

I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.

This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.

[URL] ...

View 1 Replies View Related

Excel 2003 :: Data Extraction From Cell

Apr 11, 2013

Data in cell A1 - 1) A Q
Data in cell A2 - 12) V
Data in cell A3 - 131) B H L

How do I throw away the #) and the following space in cells A1, A2, and A3 and save the rest?

Excel 2003. ie. no VBA.

View 4 Replies View Related

Excel 2003 :: How To Use Filter On Multiple Data

Nov 24, 2013

I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.

Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?

Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?

View 1 Replies View Related

Excel 2003 :: Formatting Chart Data

Jan 18, 2014

I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.

View 9 Replies View Related

Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter


and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved