View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] neil40@btinternet.com is offline
external usenet poster
 
Posts: 26
Default Help understanding what a formula does and why it's 'faulting'

Hi

With some help I got a formula that works, but only after a fashion

Here was my original question:
Return last 6 results formula
I have a column in my sheet that fills in progressively over a season
as team results are entered.

This particual column will show d for a goal-less draw (0-0), D for a
score draw (1-1, 2-2 etc) and W or L for Win/Loss

I'd like to show the last 6 game 'form' in this format: W,L,W,W,d,D
using that column.
If less than 6 games I want it to put in dashes (-,-,W,L,d,D)

With kind help, this was the result:
=IF(INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN), AN:AN,
0)-6)=0,"-",INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN),AN :AN,0)-6))

Next cell down has -5, next cell -4 etc etc
(A final cell concatenates to give the result I wanted)
What doesn't work is if there are less than 6 results I get #VALUE as
the result

Can someone kindly
a) Possibly explain why
b) Explain what the formula actually does!

Thanks
Neil