# Summing Series Of Maximums In Larger Series

Aug 22, 2014

I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:

I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)

How do I write that kind of formula?

## Summing Numbers In A Series

Nov 15, 2008

The series 1,3,6,10,15,21,28,36 ("A" series) is the ongoing sum of 1+2+3+4+5+6+7+8 ("integers") and naturally goes on for as long as on chooses. I am only interested in the numbers up to 100.

Is there an Excel function that will allow me choose any integer as a starting point and any higher integer as an ending point and provide the sum of the "A" series associated with the two choices.

For example: If I begin at 6 and end on 12 it would give me 308 =28+36+45+55+66+78

00
11
23
36
410
515
621
728
836
945
1055
1166
1278
1391

## Summing Results Of Formula On Series Of Data

Oct 24, 2011

Is there a way to SUM the result of the followig for B4:J4 and B16:J16, the other cell references remain static:

=(B28*(P28^2-IF(ISNUMBER(B16),ABS(B4-B16)^2,B31)))

I a currently using the following which is rather lengthy and not efficient to maintain/edit:

=(B28*(P28^2-ABS(B4-B16)^2))+(B28*(P28^2-ABS(C4-C16)^2))+
(B28*(P28^2-ABS(D4-D16)^2))+(B28*(P28^2-ABS(E4-E16)^2))+
(B28*(P28^2-ABS(F4-F16)^2))+(B28*(P28^2-ABS(G4-G16)^2))+
(B28*(P28^2-ABS(H4-H16)^2))+(B28*(P28^2-ABS(I4-I16)^2))+
(B28*(P28^2-ABS(J4-J16)^2))

## Sine Of An Angle X By Summing An Infinite Series In VBA

Jul 18, 2006

i was set some work related to maths to be carried out in VBA. the problem for me is im finding it very hard to understand either and so i cannot seem to work out how to complete the assignment given.

i can attach the assignment to save space and time reading on here however if you would like me to post up the assignement i can do that too....

## 200 Series But Get (The Maximum Number Of Series Per Chart Is 255) Error Message

Aug 4, 2014

I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.

If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)

But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.

After the data is plotted it is the code below that gets the error:

[Code] .....

## Line Chart - Remove Series Code But What If No Series Exists?

Dec 22, 2011

I have the following code:

Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub

However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.

## Show Chart Data Series Labels On All Series. 2007

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?

## Spurious XY Scatter Chart Series Adding Series

Aug 21, 2006

The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.

Sub Chart2()

Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer

Worksheets("Hemisphere").ChartObjects(2).Activate

CurrentSeries = 1 ............

## Color Pivot Chart Series Dependant On Series Name

Jun 24, 2008

I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.

Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................

## Chart - Second Series Starts Where First Series Ends?

Sep 21, 2011

I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.

The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.

Excel version: Excel 2010
OS: Windows 7

## Color Bar Chart Series Based On Series Name

May 15, 2008

I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....

## Max And Min Value Of Series

Feb 8, 2010

Happy Morning to all I am new here, i need your help how can I get the Max and Min value of this ref#

GHACCCSBF00044711
GHACCCSBF00044712
GHACCCSBF00044713
GHACCCSBF00044714 ....

## First Value In Series To / Below Zero?

Oct 19, 2012

B25:AK25 represent months 1-36 B26:AK26 is declining dollar value tied to other formulas Need formula which indicates in which month the associated dollar amount in row 26 first declines to or below zero.

## Sum Of Arithmetric Series

Nov 19, 2009

I need to make a formula in a Excel (I have Excel 2003), that takes value X and adds Z to it, then increases X by 1 and again adds Z to it, then again increases X by 1 and adds Z to it, until it reaches value Y plus Z. Or, repeating the (X+1)+5 process N times.

For example, if X=3, Y=6 and Z=5:

(3+5)+(4+5)+(5+5)+(6+5)

X (and Y) will have to use values from 1 to up to tens of thousand. X, Y and Z (or X, Z and N) will be cell values from an existing table.

## Filling Down A Series.

Aug 3, 2009

I m looking to fill down a column where I am assigning a number that maps to a certain week of the year. In essence after every 7th value the number should increment by 1.

Date Day ConsumptionWeek number

1/01/2009Thu 827 1
2/01/2009Fri 907 1
3/01/2009Sat 1,013 1
4/01/2009Sun 1,051
5/01/2009Mon 1,097
6/01/2009Tue 1,155
7/01/2009Wed 1,044
8/01/2009Thu 976 2
9/01/2009Fri 1,034 2
10/01/2009Sat 1,107
11/01/2009Sun 1,073
12/01/2009Mon 1,161
13/01/2009Tue 1,395
14/01/2009Wed 1,172
15/01/2009Thu 1,060
16/01/2009Fri 1,008

## Multiple The Series

Jun 6, 2007

I need to create a chart where there is one horizontal series and one vertical series for each data point on the Y axis. Example: results for 5 people (series along the X axis) in trials vs. results for the same 5 people in the actual competition (second X series). The trials vs. actual is the Y series for a certain competition on date MMDDYY. The Y axis would represent the various competitions throughout the year.

## Split Series

Mar 16, 2009

I want to split a column series as per its left three 3 digits into different columns as example given below....

## VOG Compare Series

Apr 19, 2009

Vog I am trying to use your code from following post

Actual when I use to compare 2 series in same sheet I use following formula in column C (All Series "Number Stored as Text")

How should I use mentioned post code as per my formula?

## Series Collection Vba

Jan 6, 2010

I trying to code a SeriesCollection in vba, below is the code I have:

' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).Name = "='Chart1'!\$C\$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!\$D\$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!\$E\$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!\$F\$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!\$G\$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!\$H\$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!\$I\$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!\$J\$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!\$K\$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!\$L\$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!\$M\$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!\$N\$1"

However it it giving the error "Object denfined or Appication denfined"

## Add New Series To Charts

Nov 13, 2008

i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.

Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub

## How To Add Series Without The Graph Line

Apr 14, 2014

Is there any possible to add a series to a graph table without having the line in the Graph. i just want to show the difference for information and not in the graph?

i have tried some different things (like having the difference in white so it is not visible, formatting the Axis etc)

See attached picture. Excel 2.JPG

## Dynamic Series For A Graph

Jun 30, 2014

I have a graph that auto updates but sometimes there is only 1 series and sometimes there is 2,3,4, or 5 series. Is there a way to have the graph update accordingly? and only have have the series that are valid be in the key on the graph. Currently the graph keeps all the series in the key even when there isn't any data for it.

Please see attachment. Use sheet 2 for the graph. and use the drop down menu to select catagory. this ia very quick mock up of the concept.

Graphseries.xlsx

## Same Colors To Same Data Series

Nov 27, 2013

I have a big set of data and I want to create different graphs in different tabs with it.

So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe
Tab 2 would be the same, but for product b
Tab 3 the same, but for product c

So with dynamic tables in each tab I can easily do my graph

The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.

How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...

## VLOOKUP For Series Of Data

May 8, 2014

There is data which comprises of Part (#), Discount (%), Start Date & End Date. In this data Part (#) can repeat but Start Date & End Date periods will not overlap.

I want to write a formula which looks up into this whole data set and give me result as TRUE or FALSE and get Respective Discount (%) in another cell. True if for a specific Part (#) Discount (%) is applicable on Current Date.

Attaching sample excel file for example of data set.

## Subtracting Until Zero From A Series Of Numbers

Jul 3, 2014

I'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.

Staff attendance WIP.xlsx

On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.

The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.

The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.

I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.

## How To Calculate Series Of Row Totals

Aug 7, 2014

I have a list of stores and the costs for various items.

Each store has (usually) more than one row.

Each store's items are totaled at the end of each line. Simple

What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.

Please see the attached sheet : How to Calculate Store Total.xlsx

## Time Series Function

Mar 26, 2008

I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value?

example:

8:00 .005
8:01 .006
8:02 .0075
8:03 .0077
8:04 .008

time now is 8:03

How do I get the .0077 in excel?

## Fill Series Down A Column

Jul 23, 2008

Bet this is simple to someone...

How can a continue this fill series down a column?

=\$BC\$3
=\$BC\$4
=\$BC\$5
=\$BC\$6

I would copy, paste and edit only there are 2500 rows!

## Change A Series Of Rows.

Feb 12, 2009

I want to change the values arranged in the row wise to the format I have attached in the excel sheet. Manually it would be very tiresome.

## Fill Series Of Cells

Feb 5, 2014

I want to run a series of cells but how to do it. E.g.

Cell A1 to contain the data 2A
the next cell (A2) to read 2B
the next 2C etc etc

I've tried just dragging the cells down but it just duplicates 2A, 2B, 2C whereas I want it to fill the next cells as 2D, 2E etc

And I've tried using the fill series option but neither seems to be working (presumably due to the letter).