Excel Last X Entries

[excelhasey]

Happy new year to you all I hope you had a great Christmas.
I am looking for some Excel help please, ideally using excel formulas rather than Macros etc thanks :-

In Column A I have a list of numbers which can be between say 1 and 100 but they will change daily so I don't know how long this list will be at the end of each day

So in column B i can find out how many times say one particular number occurred in the run (Column A) by using "Countif", but :-

In Column C1 i have a number (call it X), which can be amended at any time by me and in Column D i want to count the number of occurrences a particular number has been entered in Column A but only during the last X number entries

i.e. its basically the maximum number of entries in column A (the length of this column changes daily) minus the X value in $C$1 - So say X = 25 then I want to count the number of occurrences each number (from range 1 to 100) in Column A has occurred in the last 25 entries

Does this make sense ??
Can it be done ??
Any ideas ??

[osknows]

Hello,

It can be done. Assuming...

Column A contains a list of numbers (no blanks)
C1 = Last X figures to include in match
D1 = The number to match

then the following formula will count the occurrences

=COUNTIF(OFFSET($A$1,COUNTA($A:$A)-$C$1,0,$C$1,1),$D$1)

[excelhasey]

Cheers Os but I think I have messed it up or I don't understand it - In the formula you have -$C$1,0,$C$1,1 is that not just looking to put a 0 or a 1 if the match exists rather than counting it ?? - Also should it not read D1 at the end rather than $D$1 ??
Anyway I have re-jigged the spreadsheet thus (ah it appears I cant copy in screen dumps)
Right A1 contains the number of todays recorded events (for this example I have set it at 20)
A2 is what I call "X" i.e. the last number of events I wish to evaluate (for this example I have set it at 5)
A4= text "Event No.", B4= text "Possibles (i.e. 1 to 10), C4= text "Actual Result", D4=text "No of times the possible actual result (Col C) = possible in Col B (i.e 1 to 10)", E4=text "No of times the possible actual result (Col C) = possible in Col B (i.e 1 to 10) in the last 5 ("X") events"
Col A5 to A24 is now 1 to 20 inclusive (i.e. no. of all todays events)
Col B5 to B14 reads 1 to 10 inclusive (i.e. all the possibilities)
Col C5 to C24 are the actual manually inputted results (for this example they are 7,7,5,3,9,8,5,10,9,4,7,6,1,9,1,7,6,9,3,6
So Col D5 to D14 now read 2,0,2,1,2,3,4,1,4,1 (using Countif function)
Col E5 to E14 should be telling me the number of times 1 through to 10 has appeared in the last 5 ("X") events which should be 0,0,1,0,0,2,1,0,1,0
but all I get is zeros
I hope you can follow and understand that, I appreciate its a bit messy via text message !!!
[/quote]

[osknows]

Based on your description, enter the following in cell [E5] and copy down

=COUNTIF(OFFSET($C$5,$A$1-$A$2,0,$A$2,1),$B5)

essentially this breaks down to

Count occurrences of value in $B5 in the range OFFSET($C$5,$A$1-$A$2,0,$A$2,1)

and the range OFFSET($C$5,$A$1-$A$2,0,$A$2,1) means OFFSET(reference, rows, cols, [height], [width])

or OFFSET from cell $C$5, 15 rows down (20-5), 0 cells across, with row height 5, column width 1

(ie the last 5 rows)

[excelhasey]

Os your a legend - it works a treat

Thank you very much mate cheers