Pop
Member
Registered: 8th May 03
Location: Reading
User status: Offline
|
Hello,
I've been googling this one to death but can't quite find the answer I need. I'm after help with a formula and hopefully there are some clever Excel people on here that might know.
This is to be used for football.
I want to use cell A1 for the total. In the rest of row 1 I will be putting a figure each game for the total goals. I will start in cell B1 then C1 and so on... What I am trying to figure out is a formula that will total the most recent 8 figures.
It would start totalling cells B1 to I1. The next week I would update cell J1 with the goals total and in cell A1 it would then total cells C1 to J1.
Is there any way this can be done so that it always reads 8 cells back from the right, starting at the first cell that does not have a 0 in it?
Hopefully that all makes sense. All help is very much appreciated.
[Edited on 02-11-2011 by Pop]
|
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
|
no
|
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
|
at least not in that format
|
Pop
Member
Registered: 8th May 03
Location: Reading
User status: Offline
|
Can you suggest a format that would work?
|
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
|
Not right now mate but will have a play tomorrow to see if i can come up with anything. most likely would need a series of macros and im no VBA expert. DOm will undoubetdly be able to help though
|
Pop
Member
Registered: 8th May 03
Location: Reading
User status: Offline
|
Cheers for your input. Anything you can think of is great.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Piece of cake in a database, aggregate query and a limit of 8 on the group, ordered by the primary key.
[Edited on 02-11-2011 by Ian]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
In fact, Access can't do that. Sub-select to find the lowest permissible ID (highest ID minus 8) and group that.
Table called goals
Two fields, ID which is primary, goalcount which is the numerical number of goals.
Create a query in SQL view -
SELECT SUM(goalcount) AS Sum FROM goals WHERE id >(SELECT MAX(ID)-8 FROM goals);
[Edited on 02-11-2011 by Ian]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I would think that is something I could do. I'll have a little think about it later 
|
Sam
Moderator
Premium Member
Registered: 24th Dec 99
Location: West Midlands
User status: Offline
|
http://www.xl-central.com/sum-last-3-values.html
|
Pop
Member
Registered: 8th May 03
Location: Reading
User status: Offline
|
quote:
Originally posted by Sam
http://www.xl-central.com/sum-last-3-values.html
Fantastic! That works a treat 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
What's with the bonkers massive exponent in the first one?
|
