Formula to add latest data input

[Marvo]

If you have a simple formula in one cell to add the latest amounts in a column, for example the last 6 amounts imputed, can a formula be constructed so that when you add a further amount it updates?

Basically last 10 weeks 5+6+3+4+3+4+5+3+1+9=43

Formula adds last 6 weeks = 25

Then another amount added, in this case 7

Last 10 weeks 6+3+4+3+4+5+3+1+9+7=44

Formula to add last 6 weeks = 29

Is that possible?

[samba_ravi]

yes it is possible,
please attach sample excel file

[Marvo]

Sorry samba-ravi, haven't got one yet, wanted to know if it was possible. I'm also trying to incorporate a value to a letter, simply W=3, D=1. I'll make a mock-up sheet and post it.

[Marvo]

Okay. You're looking at Column J. What I'd like if possible is a total of the last 6 matches counting a W = 3 and D =1.

A further improvement would be the 6 could be in a separate cell so it could be changed to say 8 or 10 if necessary that would be even better. Results are added each week.

If you want to aim for a perfect outcome, having a further cell counting the last 6 games in League games only (Column I) would be the icing on the cake but I dont want to push my luck.

Many thanks for your interest.

[Marvo]

Right, stage 1 completed. This is the formula I've come up with to add the letters together

=SUM(COUNTIF(J5365:J5370,"W")*3+COUNTIF(J5365:J5370,"D")*1)

So stage 2, when a result is entered into J5371, how do I get this formula to automatically change to

=SUM(COUNTIF(J5366:J5371,"W")*3+COUNTIF(J5366:J5371,"D")*1)

to represent the latest last 6 results? I'm guessing there has got to be a 6 somewhere?

Maybe something to do with the entire row (J) but a number to indicate how far you go back?

[Marvo]

Right I've moved on to stage 3 because I haven't got a scooby how to do stage 2. I've tried this

=COUNTIFS(ALL!I:I="League",SUM(COUNTIF(ALL!J:J,"W")*3+COUNTIF(ALL!J:J,"D")*1)

but it doesn't work, says I'm missing an opening or closing parenthesis. I put an extra closing one on the end of the above but that didn't work. Anybody?

[Glenn Kennedy]

Once again, where am I supposed to be looking for this stuff?

[Marvo]

Post with attachment at 12:02.

Started answering myself at 3:53.

I'm not very good at this am I?

[Glenn Kennedy]

The answer to 1 and 2 combined:

=SUMPRODUCT(LOOKUP(INDEX($J:J,AGGREGATE(14,6,ROW($J$1:$J$10000)/($J$1:$J$10000={"W","D"}),{1,2,3,4,5,6})),{"D","W"},{1,3}))

[Glenn Kennedy]

Last 6 League matches ONLY:

=SUMPRODUCT(LOOKUP(INDEX($J:J,AGGREGATE(14,6,ROW($J$1:$J$10000)/(($I$1:$I$10000="League")*($J$1:$J$10000={"W","D"})),{1,2,3,4,5,6})),{"D","W"},{1,3}))

[Marvo]

Thanks Glenn,

I've put that formula into a cell and its returning the value of 3. With 3 wins and 2 draws the last 6 games it should be 11?

Looking at your formula, is the 1,2,3,4,5,6 the instruction to look back 6 rows??

I'm trying to understand it.

[Marvo]

The league only formula also returns 3, should be 7.

[Glenn Kennedy]

I think I have misunderstood you. You want the last 6 GAMES?? Please confirm. I was calculating the last 6 WINS or DRAWS.

[Marvo]

Yes Glenn, its for a current form. What I was aiming for was two cells, one with a formula to look back over an amount of games, the other cell with the amount of games. In that way I could put whatever figure I liked in the one cell and the formula would look back that amount of games to give me a points total.

My Australian friend then will do a code where I can enter that points total and immediately discover the previous time that sequence was either better or worse.

So, when asked the question, when was the last time we had such a good or poor run of results I can immediately say, Feb-April 1978 (for example). To actually find that out manually is nigh on impossible, there are not enough hours in the day.

[Glenn Kennedy]

Take a look here.

I am confused by your expected answer for last 6 league games. Shouldn't it be 8???

[Marvo]

Yes Glenn, I did it in my head. Sorry.

[Marvo]

That's great Glenn. Couple of questions. Is it where you write 1,2,3,4,5,6 in the formula where it is looking back at that amount of games? Also, do we need a value for "L" if it's zero?

[Glenn Kennedy]

Yes... and Yes.

[Marvo]

Okay, thanks. I'll just have to change the formula when I need to or have a few, maybe 6 games, 10 games, 12, games, 15 games etc.

That might not be as easy as I say. I just tried to copy the formula over to my master workbook and it immediately returned zero and even pressing the back button wouldn't return it to 8. I'll try writing the formula over manually.

Thanks Glenn, you've been a great help and it's much appreciated.

[Glenn Kennedy]

Dont. There is an alternative that'll be easier to maintain. I will look at it tomorrow.

[Glenn Kennedy]

I had some "issues" with your sample sheet. Try copy paste without the = and add it manually. Also check cell alignment. It caused me lots of issues on your sample sheet. I called you all sorts of ungentlemanly names...

[Marvo]

Okay Glenn, I probably deserve it.

I've attached the actual workbook, your formulas have been transferred to K5418 & K5418 where you'll see they are giving an incorrect result.

They will eventually be added to the Runs & form page. That page is under construction.

Many thanks.

[bebo021999]

With J5418 stores counting number, i.e, 6 or 7 or 10

Try this:

Please Login or Register  to view this content.

How it works: assuming count number =6

Get the last row index (5370), then minus 6, to get the starting row index (5365)
LOOKUP(2,1/($J$2:$J$5400<>""),ROW($J$4:$J$5400))-$J$5418-1

Sumup all rows with:

1) row index>=5365
2) column K> column L, count 1 then multifly by 3
3) column K< column L, count 1

The SUMPRODUCT(1*(2+3)) to add up every row with it own result (3 or 1)

I hope this will help you in further requirement in your file.

[Marvo]

With J5418 stores counting number, i.e, 6 or 7 or 10

Try this:

Please Login or Register  to view this content.

How it works: assuming count number =6

Get the last row index (5370), then minus 6, to get the starting row index (5365)
LOOKUP(2,1/($J$2:$J$5400<>""),ROW($J$4:$J$5400))-$J$5418-1

Sumup all rows with:

1) row index>=5365
2) column K> column L, count 1 then multifly by 3
3) column K< column L, count 1

The SUMPRODUCT(1*(2+3)) to add up every row with it own result (3 or 1)

I hope this will help you in further requirement in your file.

Thanks Bobo. Sadly I've got up this morning and I've encountered the dreaded blue screen so can't get into my computer. Looks like it will need to go for repair or I may have to buy a new one, either way I can't get at my documents for now. I will come back to this as soon as I'm able but that may not be for a couple of days.
Thanks for the assistance.

[Marvo]

Morning all. This problem has now been solved by somebody who has constructed some code to do the job. Thanks for all the help.

[Glenn Kennedy]

The reason that they did not work is because you (for some reason) decided to enter them as array formulae, with CTRL-SHIFT-ENTER. Just use enter and they work fine.

[Marvo]

The "some reason" Glenn is most of the time I don't know what I'm doing. I saw the { and thought that indicated an array formula. I'm sitting here now nervously waiting for the computer repair shop to open to see if I can salvage all my work, at least that what has been updated in the last month or so since my last back-up. Fingers crossed.

Note: Thankfully I've downloaded two of my workbooks back off of the excel forum so all is not lost. Once again, thanks for your help.

[Glenn Kennedy]

Did the formula on your file with {} round it? I've recently upgraded to O365 and array entry is a thing of the past....

Good luck with the PC! I back everything up to the cloud continuously. I got caught once. Once you make that mistake, you never make it again.

[Marvo]

I'm sure I tried the formula with and without array but I did think it was an array. You're lucky if you no longer have to deal with it, if I have to as I suspect buy a new computer I'll try and make sure I get the very latest excel package. I don't use the cloud, back up to a hard drive but keep forgetting to do it. I last backed up possibly a couple of months ago so a lot of work will be lost. I'll have to look at how to back up my stuff daily. Maybe dropbox to my laptop?

Ten minutes until the repair shop opens. I'm dreading the prognosis. It's not the cost, it's the lost time.

[Glenn Kennedy]

It's off topic, but... I have about 350Gb of music that is continuously backed up to Microsoft One Drive. It's quick to start when i boot up either the Desktop or Laptop. I have 150Gb of work-related files on Google Drive. It is slow to sync on boot-up. Maybe 15 mins. Ther PC is perfectly useable in that time. If both PC and Desktop are on, files sync instantaneously and I can pick up where I left off on either machine. I tend not to turn them off at night, but put them into sleep mode, to avoid the lengthy wait while Google Drive does its job.

To prove my paranoia... I also back the desktop up to two separate external HDDs every week or so.

As you may have gathered, when I got burned, I got burned badly. Never again!! Hope you don't have such an unpleasant lesson to learn!!

[Marvo]

Hard drive kaput, lost almost everything, trying to piece things together. Most annoying thing. Installed the Excel 2016 pro which I had on my previous hard drive, Microsoft wont accept the key which I still have, even though it was paid for! So I have it for just a month. Might trawl the internet see if I can find a working key, they have some bloody nerve.