Help understanding what a formula does and why it's 'faulting'

N

neil40

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
 
T

T. Valko

That formula is flawed for a couple of reasons. It assumes there will be
enough data to satisfy the result of the MATCH function. Also, even if there
is enough data the result of the MATCH could easily return the incorrect
result because there will probably be many duplicates of the lookup_value
and when used with a match_type argument of 0, will always match the first
instance of the lookup_value.

That formula is basically looking for the last TEXT entry in the column and
then returning the value that is offset from that location by -6 rows, -5
rows, -4 rows, etc.

Try this formula:

I'm assuming that AN1 is your column header and your actual data starts in
AN2 on down.

=IF(COUNTA(AN$2:AN$100)<7-ROWS($1:1),"-",INDEX(AN2:AN100,LARGE(INDEX((AN$2:AN$100<>"")*ROW(AN$2:AN$100),,1),7-ROWS($1:1))-1))

Copy down to a total of 6 cells. Also, use a large enough range size to
allow for newly added data. Or, you could use a dynamic range that will
automatically adjust as you add new data.

How to create a dynamic range:

http://contextures.com/xlNames01.html#Dynamic
 
T

T. Valko

Hmmm...

I forgot to make one of the range references absolute.
...INDEX(AN2:AN100,LARGE...
Click to expand...

The correct formula should be:

=IF(COUNTA(AN$2:AN$100)<7-ROWS($1:1),"-",INDEX(AN$2:AN$100,LARGE(INDEX((AN$2:AN$100<>"")*ROW(AN$2:AN$100),,1),7-ROWS($1:1))-1))
 
D

daddylonglegs

Hello Neil,

making the same assumptions as Biff, i.e. that you have a header in AN1 and
data starts at AN2 try this formula copied down 5 more cells


=IF(COUNTA(AN:AN)>7-ROWS($1:1),INDEX(AN:AN,MATCH(REPT("z",255),AN:AN)-6+ROWS($1:1)),"-")
 
T

T. Valko

=IF(COUNTA(AN:AN)>7-ROWS($1:1)...

Need a slight tweak:

=IF(COUNTA(AN:AN)>=7-ROWS($1:1)...

If there was a single entry you were not picking it up.
 
T

T. Valko

Disregard. I didn't have a column header when I tried that formula. If you
don't have a column header then you'd need to adjust for that.
 
T

T. Valko

The difference between our 2 formulas is that mine will account for empty
cells within the range. If there will not be any empty cells use DL's
formula.
 
N

neil40

The difference between our 2 formulas is that mine will account for empty
cells within the range. If there will not be any empty cells use DL's
formula.

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Many thanks to you both.

Just one question with DL's method.
Which part of your formula do I change if data starts in row AE4?

Cheers
Neil
 
T

T. Valko

After some more tweaking I was able to shave a few more keystrokes off the
formula.

Assumptions:

no empty cells within the range
there will be no row insertions before row 1

=IF(COUNTA(AN:AN)<ROWS(1:$7),"-",INDEX(AN:AN,COUNTA(AN:AN)-ROWS(1:$6)+1))
 
D

daddylonglegs

Hello Neil

"Just one question with DL's method.
Which part of your formula do I change if data starts in row AE4?"

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100)-ROWS(1:$6)+1))
 
N

neil40

Hello Neil

"Just one question with DL's method.
Which part of your formula do I change if data starts in row AE4?"

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100­)-ROWS(1:$6)+1))






- Show quoted text -
Click to expand...

Hmm. The formulae don't seem to work.
I will have data that will progressively fill AE4 to AE49 (there are
46 games per season), so I changed the formula to =IF(COUNTA(AE$4:AE
$49)<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
and copied down 6 times.
but i get a blank results
I'll explain what happens in the Cells AE4 to 49 and perhaps this
affects the results?
Formula in Cell AE4 is
=IF(AD4="","",IF(AA4>AB4,"W",IF(AA4<AB4,"L",IF(AA4=AB4,IF(AA4=0,"d","D"))))),
copied down to AE49
So, as results are filled in week by week, AE4 to 49 will hold the
result W, D, d or L

Cells AE2 and AE3 are blank, whilst AE1 is part of a merged cell
across X1->AW1
If I progressively evaulate the formula I get
IF (46<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-
ROWS(1:$6)+1))
IF (47<7,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,46-6+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,40+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,41))
IF (FALSE,#N/A,$AE$44)

Baffled!
Neil
 
T

T. Valko

Hello Neil

"Just one question with DL's method.
Which part of your formula do I change if data starts in row AE4?"

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100­)-ROWS(1:$6)+1))






- Show quoted text -
Click to expand...

Hmm. The formulae don't seem to work.
I will have data that will progressively fill AE4 to AE49 (there are
46 games per season), so I changed the formula to =IF(COUNTA(AE$4:AE
$49)<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
and copied down 6 times.
but i get a blank results
I'll explain what happens in the Cells AE4 to 49 and perhaps this
affects the results?
Formula in Cell AE4 is
=IF(AD4="","",IF(AA4>AB4,"W",IF(AA4<AB4,"L",IF(AA4=AB4,IF(AA4=0,"d","D"))))),
copied down to AE49
So, as results are filled in week by week, AE4 to 49 will hold the
result W, D, d or L

Cells AE2 and AE3 are blank, whilst AE1 is part of a merged cell
across X1->AW1
If I progressively evaulate the formula I get
IF (46<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-
ROWS(1:$6)+1))
IF (47<7,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,46-6+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,40+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,41))
IF (FALSE,#N/A,$AE$44)

Baffled!
Neil

Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!
 
N

neil40

Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK­(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Many thanks Biff. It's now perfect.

Thanks for persevering with me!

Neil
In soggy central England.
 
T

T. Valko

[email protected] said:
Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the
COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK­(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Many thanks Biff. It's now perfect.

Thanks for persevering with me!

Neil
In soggy central England.

You're welcome. Thanks for the feedback!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return last 6 results formula 2
Function or Sub to help with text string 5
Index Match Help 6
Help with colour formula 3
Need help with ISNA, INDEX, MATCH 2
Conditional Formatting on Index,Match formula 3
Repost - Functions on filtered data 0
vlookup and match first value greater than 4

Top