Series question in a formula

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
Hi there XL Family,

Hoping someone out there with more XL know how will know the answer to this.

In A1 of I have a basic sum formula.
=ATP!D11+ATP!D21+ATP!D31+ATP!D41+ATP!D51+ATP!D61+ATP!D71+ATP!D81+ATP!D91+ATP!D101+ATP!D111+ATP!D121+ATP!D131+ATP!D141+ATP!D151+ATP!D161+ATP!D171+ATP!D181+ATP!D191+ATP!D201+ATP!D211+ATP!D221+ATP!D231+ATP!D241+ATP!D251+ATP!D261+ATP!D271+ATP!D281+ATP!D291+ATP!D301+ATP!D311

In A2
=ATP!D321+ATP!D331+ATP!D341+ATP!D351+ATP!D361+ATP!D371+ATP!D381+ATP!D391+ATP!D401+ATP!D411+ATP!D421+ATP!D431+ATP!D441+ATP!D451+ATP!D461+ATP!D471+ATP!D481+ATP!D491+ATP!D501+ATP!D511+ATP!D521+ATP!D531+ATP!D541+ATP!D551+ATP!D561+ATP!D571+ATP!D581+ATP!D591

In A3
=ATP!D601+ATP!D611+ATP!D621+ATP!D631+ATP!D641+ATP!D651+ATP!D661+ATP!D671+ATP!D681+ATP!D691+ATP!D701+ATP!D711+ATP!D721+ATP!D731+ATP!D741+ATP!D751+ATP!D761+ATP!D771+ATP!D781+ATP!D791+ATP!D801+ATP!D811+ATP!D821+ATP!D831+ATP!D841+ATP!D851+ATP!D861+ATP!D871+ATP!D881+ATP!D891+ATP!D901

Etc Etc Etc

As you can see it does follow a series by adding 10 to each part of the sum.

Just wondering if there is a way to do a Edit-Fill-Series for this instead of having to type it out.

I have quite a lot to do for this large worksheet.

Hope someone can help.

Im sure it is easy if you know how.

Thanks heaps

Joshua
"An Aussie Novice"
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Joshua

THis is an array formula equivalent to the one you have in A1

=SUM(N(OFFSET(ATP!$D$11,10*(-1+ROW($1:$31)),0)))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Is this what you want?
PGC
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You have less values summed in A2 than in A1 and A3 - is that correct?

Another way to replace A1 would be with

=SUMPRODUCT(--(MOD(ROW(ATP!D11:D311)-ROW(ATP!D11),10)=0),ATP!D11:D311)
 

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
the reason for that is A1 is January,
A2 is Feburary
A3 is march

What Im trying to do is is tally up the total for the month for each

This is the worksheet the total goes into.


mainpic.jpg


The folling is the ATP worksheet where im trying get the total sum for.
The cells for the formula are only in the yellow.

Hope you understand. This is way I could just say between D2 and D311.


mypic1.jpg


Hope having the pics helps out with this one.

Thankyou again.
 

Forum statistics

Threads
1,141,841
Messages
5,708,911
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top