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
ADVERTISEMENT
Aug 12, 2014
I have drop drown menus that allow you to select 1 of 3 choices and based on those a second set which lists the options for the first three (subset). Now depending on the second value I need to pull a set of x y coordinates that can be of varying length. '
Example
Drop down menu 1 1560, 1669, 1554. Drop down menu 2 1560-1,2,3,4 1669-a,b,c,d. This has been done now lets say I select 1560-1. I need the following to be pulled from a list, the data is a block.
1.00 1.543
1.23 -1.25
0.54 -2.75
This can be of any length.
View 6 Replies
View Related
Oct 5, 2006
i have a chart on a worksheet, it contains multiple series (3) in which i wish to change the values/ range of each series of the chart through vba. Thus far i have struggled due to the lack of intellisense, in which i tried
Worksheets(1).ChartObjects(2).Chart.Series("Fails").Values = Range("b12:b14")
with "Fails" being the name of that particular series.
View 3 Replies
View Related
Sep 4, 2013
I have a table showing data for 10 teams. There are however 10 different measures which are pulled from a seperate sheet using a dropdown box to select the measure.
One of the measures is example 390823 which needs to be format 390,823 so that thousands are shown, and other formats are, example, 89.9 which need to be 89.9%.
Ive gone to my other sheet and all the raw data is showing correctly with % and ,s. But my dashboard table can only have one format. How do i make it so that each measure pulls in the correct format when selected, without dragging the whole area and changing each time?
View 1 Replies
View Related
Oct 12, 2007
I'm trying to change the default option for the data series when graphing in Excel 2003 .... I am trying to create a line graph
Excel, by default, is putting static pressure on the x axis and the air flow on the y axis .... I need it to be the other way around
I have found out that:
By default, Excel plots whatever you have fewer of—rows or columns—as the data series
apparently it is possible to change this Excel default option, but I can't figure that out
in step 2 of the Chart Wizard, I have tried many different things, but can't get Excel to chart the Air Flow CFM on the x axis, and have static pressure values on the y axis
View 9 Replies
View Related
Oct 2, 2012
solve this task with a formula or a macro?
I have this strange number data stored by columns. Each cell contains one number series of different length from 1 to 13.
Needed results 1, Needed results 2
7409998765 7409998766 , 9998766567876, 7409998765 9998766 9998766 7409998766 9998766 9998766567876 9998766 9998766
I need to look-up and bring in one cell for each row only the 10 length numbers separated by space and in another cell the 13 length numbers space separated also.
The number of columns containing data is different from row to row ( from 2 to 60)....and the rows..are thousands..
View 7 Replies
View Related
Sep 3, 2007
When a number is entered in cell S3, it triggers formulas throughout the worksheet to populate the it with information from another sheet. If the length of the text in B6 is greater than 80, the font size for B6 only should be 8; if the length of the text in B6 is less than or equal to 80, the font size for B6 should be 10. Regardless of the length of the text in B6, the font size for the rest of the sheet should not be changed.
I tried the following
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$6" And Target.Count > 1 Then Exit Sub
Dim cnt As Long
cnt = Len(Cells(6, 2).Value)
If cnt > 80 Then Cells(6, 2).Font.Size = 8
ElseIf cnt < 80 Then
Cells(6, 2).Font.Size = 10
End If
End Sub
View 9 Replies
View Related
May 6, 2009
I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.
View 2 Replies
View Related
Nov 19, 2009
I'm trying to write some code to make an existing chart display the correct series of data. However, on the ".SeriesCollection..." lines, it gives me the error "Object does not support this property or method." I'm using a Bar-Line graph, if it makes any difference.
View 5 Replies
View Related
May 31, 2006
I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?
View 4 Replies
View Related
Apr 12, 2012
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
View 3 Replies
View Related
Aug 10, 2009
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab. BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders). I need the totals to half the job and add it to the salesmans total accordingly.
View 4 Replies
View Related
May 23, 2008
I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...
View 3 Replies
View Related
Aug 5, 2008
I understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
View 8 Replies
View Related
Dec 1, 2007
it is possible to change the colour of a cell text and / or cell background depending on a value.
For example I have currently 2 cells with numbers in
G3 = 4.38
K3 = 8.68
What I wolud like to happen is for the cell with the lowest value to change to Red.
The Column letters will always remain the same but I do plan to add a lot more rows.
View 10 Replies
View Related
Jan 13, 2010
Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?
This is what I've written so far:
Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer
[Code]....
As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.
View 4 Replies
View Related
Nov 30, 2006
I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).
Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:
=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)
When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.
View 2 Replies
View Related
Jan 21, 2010
I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.
I have tried running the following, but I get an error, basically saying the series hasn't been selected.
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)
View 9 Replies
View Related
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.
E.G
A B C D E
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:
A B C D E
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
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
View Related
Jun 18, 2014
I'm trying to figure a function wherein if I change the category, the results will automatically changes. see attached sheet.
View 6 Replies
View Related
Jan 16, 2007
I'm putting together a spreadsheet to do with football betting, that involves calculating various stakes and odds and probabilities. I've done pretty well, but I want to take it to the next level.
At the moment, I have 4 sheets which are completely self contained:
sheet one = betting on 2 matches
sheet two = betting on 3 matches
etc
This was the easiest way to do things when I was learning.
Now I want to look at incorporating more flexibility into my spreadsheet. I want to investigate the possibilities of having a small form to be filled in, such as the following:
Number of matches to bet on... a drop down menu for the user to choose from
and then for the visible area of the sheet to change to show the appropriate layout for their selection. So they'd choose the number of matches they want to bet on, and the spreadsheet would display what they need automatically.
I've looked at putting forms in and think I could do that quite easily, but how can achieve the effect of changing the layout/visible area depending on the chosen option?
View 11 Replies
View Related
Feb 23, 2009
I have bunch of sheets lets call them
Germany, France, Belgium, Poland, Russia, Data
In the data sheet I have in F1 "Germany", F2 "France", H1 "Belgium", H2 "Poland", H3 "Russia"
In each of the sheets (appart from data) I have a validation list in cell D6 listing the data sheet values F1,F2,H1,H2,H3.
Now basicaly what I need is when I select Germany from the list it would take me to Germany sheet, when I select Belgium from the list it would take me to Belgium sheet etc.
Basically the sheets have always same name as the name in the list. I know i can write the code in individually but because I have a lot of these I was hoping there might be an easier way?
View 9 Replies
View Related
Mar 7, 2014
I have a set of data listed by country, I need to add a new country to the list and I want to keep everything in alphabetical order. My problem is that adding a new series to the range anywhere but the end throws of the series formatting for my chart. The plotted points on my chart are the countries flag, so when everything shifts down a row the flags no longer represent the proper country. Is there an easy way to add the new country and keep my formatting lined up or will I need to redo the formatting for all the alphabetically lower countries?
View 5 Replies
View Related
Jul 11, 2014
I'm trying to change 115 charts so that the Xvalue axes (Y-axes) will start on the lowest value of my data series. This is what I've come up with so far:
[Code] .....
What is the syntax to set instead of X so that all Xvalue axes is set to the lowest Data series value in individual chart?
View 6 Replies
View Related
May 31, 2008
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
View 4 Replies
View Related
Nov 28, 2012
I have been having problems with a function trying to count data within 2 date ranges.
When inserting the 2 dates required into collum B2 (week beginning) + B3 (week ending) it does not
View 8 Replies
View Related
Jan 15, 2014
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
View 3 Replies
View Related
Jan 26, 2007
I'm having a difficult time figuring out out to coordinate two cells together.
If a "1" or "2" is entered into cell A1, I would like cell A2 to have a drop down menu show a "0". If a "3" is entered into A1, I would like B1 to have a drop down menu show "1" or "2". If a "4" is entered into A1, I would like B1 to have a drop down menu show "3" or "4". Lastly, if a "5" is entered into A1, I would like B1 to show "5" or "6".
I don't know if this is possible without using VBA.
View 9 Replies
View Related
Apr 24, 2014
I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies
View Related
