Forums

We want to share an important update with our Betfair Community users.

Over the coming months, the Betfair Community platform will be winding down. The final active day for the platform will be December 21st. From December 22nd, users will no longer be able to access the platform.

Your discussions, insights, engagement, and, most importantly, feedback have helped shape the platform and guided innovation. Your feedback continues to be important to us, and we will continue to listen via our dedicated feedback channels, as well as through product testing and innovations.

To share your feedback and experience, visit: https://support.betfair.com/app/home

General Betting

There is currently 1 person viewing this thread.
Gin
18 Aug 11 16:05
Joined:
Date Joined: 02 Jun 03
| Topic/replies: 5,148 | Blogger: Gin's blog
I am trying to auto fill a column of formulas by using the fill handle. The first 2 cells contain the formulas:

=SUM(Daily!D215:D221)+SUM(Daily!E215:E221)
=SUM(Daily!D222:D228)+SUM(Daily!E222:E228)

I want each subsequent cell to contain the formula’s numbers +7 so that the following series look like:

=SUM(Daily!D229:D235)+SUM(Daily!E229:E235)
=SUM(Daily!D236:D242)+SUM(Daily!E236:E242)

And so on.

I looked up office help and thought that I had found the answer using Fill>Series>Linear and putting a 7 in the step value box but this didn’t work out.

Can somebody point me in the right direction please.

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
sort by:
Show
per page
Replies: 17
By:
Lori
When: 18 Aug 11 16:09
I'm pretty sure you can solve this with a combination of the INDIRECT function and the ROW function.

I can't remember exactly how to format it but should be able to google it from there.
By:
Lori
When: 18 Aug 11 16:10
CONCATENATE might be another useful function, it's a while since I've done this
By:
Lori
When: 18 Aug 11 16:24
To build a cell refence with Indirect you use INDIRECT("D"&215)
If you're using this in the A1 I think you could use INDIRECT("D"&(208+(ROW(A1)*7))) and copy paste that down the list.

(ROW(a1) would be 1
when dragged down, row(b1) would be 2 etc
)

Extend that to get sums etc ... I think.
By:
Gin
When: 18 Aug 11 18:44
Thanks for the replies Lori.

Sorry but I got dragged away from my PC after my initial post. On first look, what you have written went straight over my head but I will be able to sit down and look at it properly a bit later.
By:
Lori
When: 18 Aug 11 18:47
If you combine it with google and a blank sheet you'll probably do okay with it.

Indirect basically means that instead of using cell A1 you're using "A" and 1 ("A"&1) and you can add numbers to the one, so you can have cell A1+7 ("A"&(1+7)) and build a formula from there.
By:
Lori
When: 18 Aug 11 18:52
Check back in a few mins, I'm having a crack at this.
By:
Lori
When: 18 Aug 11 19:02
Okay, I think this works.

In A1 on a new sheet put:

=SUM(INDIRECT("Daily!D"&(208+(7*(ROW(A2))))):INDIRECT("Daily!D"&(214+(7*(ROW(A2))))))+SUM(INDIRECT("Daily!E"&(208+(7*(ROW(A2))))):INDIRECT("Daily!E"&(214+(7*(ROW(A2))))))

Copy that into A2, A3 etc and see if it has the desired effect, it's working on the one I knocked together to test on.
By:
Lori
When: 18 Aug 11 19:03
FFS

That actually has to go in A2, I copied the wrong line.

Put that lot in A2 and copy to A1, A3 etc
By:
Lori
When: 18 Aug 11 19:04
*Also, for some reason when copying from the forum it blocks out an extra cell. Hit the funny briefcase icon and use "match destination formatting"
By:
Compound Magic
When: 18 Aug 11 19:20
This may be easier to do ~

use two helper columns, in the first lets say cell L1 enter 215 and fill down step 7
in the second helper column say Cell M1 enter 222 and fill down step 7

Now in your first cell this formula and fill down ~
=SUM(INDEX(D:D,L1):INDEX(E:E,M1))
By:
Gin
When: 18 Aug 11 20:06
Jesus! I thought I was asking something relatively simple! [smiley:crazy]

Okay, I had a crack with Lori’s formula and it works! Good stuff, thanks for that.

The only problem is if I need to know which cell a particular figure is coming from – I can’t immediately tell by looking at the formula. But it wouldn’t be too hard to work out.

Out of interest, I tried CM’s formula too. I couldn’t get it to work at first but then realised that the formula didn’t reference the Daily! sheet, so it should have looked like this:

=SUM(INDEX(Daily!D:D,P4):INDEX(Daily!E:E,Q4))

The P4 and Q4 are obviously different because they are the cells I used for my helper columns).

This works a little better for me because it is easier to see which cells a figure is coming from (by looking across to the helper columns).

Anyway, thanks to both of you for helping me out with that. If I spend a little more time working out what is happening with the above, I can think of a few instances where I can use it to save me some more time.

I am glad I asked for help otherwise I could have sat here until Christmas without working it out!Laugh
By:
Lori
When: 18 Aug 11 20:12
This might help a bit.

Click the Formulas tab at the top.
With this forum page open (so you can see the full formula) click on "Evaluate formula" from the excel menu, near the right hand side.

A popup should appear with the formula in. If you click evaluate, it will go through what it's doing step by step, so you can get an idea where each number is coming/going/whatever.
Might need to go through it a few times but I think that'll help the easy way.
By:
Compound Magic
When: 18 Aug 11 21:40
Gin good to see you worked out the references.

looking back at my post I made an error with this line ~
in the second helper column say Cell M1 enter 222 and fill down step 7

Should have been ~
in the second helper column say Cell M1 enter 221 and fill down step 7

So if you used my formula you would have come up with the wrong answers.

Cheers.
By:
Gin
When: 19 Aug 11 09:45
Lori

Cheers, I will check that out.

Compound

Thanks - I had spotted that but thought it was churlish to point out! Silly

Thanks again both of you for the help.
By:
Gin
When: 24 Aug 11 11:49
Okay, I’m a bit confused again.

I’m trying to do a similar thing in an adjacent cell using the same helper rows.
The original formula was:

=(SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)*-0.03))/2

So I tried to change this by following the previous example but couldn’t work out how to add the extra instruction (*-.03) on the second part.

Whilst trying to break it all down to work it out I started by inputting just the SUM parts of the formula:

=SUM(INDEX(Daily!G:G,P6):INDEX(Daily!E:E,Q6))

This gave an unexpected answer so to double check it I returned to the original part of the formula:

=SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)

Which gave a different answer again! So now I am really confused as I thought they should be the same.

Can somebody (Compound magic, Lori, anybody!) show me what I am doing wrong. I’ve been trying to work it out for a couple of hours now and its driving me mad.
By:
Compound Magic
When: 24 Aug 11 12:54
Hi Gin,

Your very first formula ~
=SUM(Daily!D215:D221)+SUM(Daily!E215:E221)

Is the same as ~
=SUM(Daily!D215:E221)
Which my index did ~
=SUM(INDEX(Daily!D:D,P4):INDEX(Daily!E:E,Q4))

Your new formula ~
=SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)
Written as you have tried with sum index spans also column F, which you don't want.

So you would write it like this ~

=SUM(INDEX(Daily!E:E,P6):INDEX(Daily!E:E,Q6))+SUM(INDEX(Daily!G:G,P6):INDEX(Daily!G:G,Q6))

That adds the rows values in column E signified by the row numbers in P6 to Q6, then adds to that the same row values for column G.

Cheers.
By:
Gin
When: 24 Aug 11 13:12
Ah! thats worked. I was misunderstanding how the original formula you gave me was working.

With a bit of tweaking my original formula (=(SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)*-0.03))/2) has become:

=((SUM(INDEX(Daily!E:E,P6):INDEX(Daily!E:E,Q6))*-0.03)+SUM(INDEX(Daily!G:G,P6):INDEX(Daily!G:G,Q6)))/2

Which works fine.

Many thanks CM - much appreciated. I can eat my lunch in peace now.Laugh
sort by:
Show
per page

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
‹ back to topics
www.betfair.com