Hey guys need help on this formula as I keep getting a #REF! Error that won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal number
in that area it works correctly.

Much appreciated!

AM15 as defined works OK for me : what do you mean "if I use a normal number"
.... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal number
in that area it works correctly.

Much appreciated!

Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula and
just add numbers the #value error is removed and shows the person who the top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but comes up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps


"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal number
in that area it works correctly.

Much appreciated!

The formula in AM15 returns a TEXT string.

When the other formula tries to calculare this expression:

AM$15:AM$47-ROW(AM$15:AM$47)/10^10

That's what causes the error.

Biff

"Killer" wrote in message
...
Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on
cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula and
just add numbers the #value error is removed and shows the person who the
top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but comes
up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps


"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal
number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that
won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"?")+COUNTIF(D15:AK15,"P")*0.5&" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal
number
in that area it works correctly.

Much appreciated!

How would chnage it so this formula would work.
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5



"T. Valko" wrote:

The formula in AM15 returns a TEXT string.

When the other formula tries to calculare this expression:



That's what causes the error.

Biff

"Killer" wrote in message
...
Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on
cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula and
just add numbers the #value error is removed and shows the person who the
top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but comes
up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps


"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal
number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that
won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"?")+COUNTIF(D15:AK15,"P")*0.5&" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal
number
in that area it works correctly.

Much appreciated!

You can't. You'd either have to split that formula into 2 cells or you'd
have to change the other formula but then you'd be getting into overly
complex monster formula territory. I told you that your file is really
"busy". Well, this is what I meant: You're making things overly complex
because of the design of your layout. You can't put the names in order on
the weekly sheets becuase it results in a circular reference. Look at how
big your file is already and you don't even have any data in it yet. How big
do you think it will be by the time you reach week 17? It may be unusable!

To be perfectly honest, I think you should start over and keep it simple.
Get rid of all those merged cells. Keep any formatting to a minimum and
don't format until you have all the formulas in place and the layout
finished. Keep the data in contiguous ranges.

Biff

"Killer" wrote in message
...
How would chnage it so this formula would work.
=COUNTIF(D15:AK15,"?")+COUNTIF(D15:AK15,"P")*0.5&" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5



"T. Valko" wrote:

The formula in AM15 returns a TEXT string.

When the other formula tries to calculare this expression:



That's what causes the error.

Biff

"Killer" wrote in message
...
Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on
cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula
and
just add numbers the #value error is removed and shows the person who
the
top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but
comes
up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps


"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal
number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error
that
won't
list what is required from cell AN15. The formulas below are as
follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"?")+COUNTIF(D15:AK15,"P")*0.5&" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal
number
in that area it works correctly.

Much appreciated!

Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula and
just add numbers the #value error is removed and shows the person who the top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but comes up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps



"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal number
in that area it works correctly.

Much appreciated!

You are you using LARGE on a non-numeric field e.g. 1.5 - 1.5 so you get a
#VALUE error.

"Killer" wrote:

Sorry it's AM15 not AN15 nothing to do with AN cell at all.

In Cell C7 it should list the person name with the top record based on cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I remove the formula and
just add numbers the #value error is removed and shows the person who the top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5 as a test but comes up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps



"Toppers" wrote:

AM15 as defined works OK for me : what do you mean "if I use a normal number"
... instead of what?

And AN15 isn't mentioned anywhere .. what's in it?


"Killer" wrote:

Hey guys need help on this formula as I keep getting a #REF! Error that won't
list what is required from cell AN15. The formulas below are as follow.

Cell C7
=IF(ROWS($1:1)<=COUNTA(B$15:B$47),INDEX(B$15:B$47, MATCH(LARGE(AM$15:AM$47-ROW(AM$15:AM$47)/10^10,ROWS($1:1)),AM$15:AM$47-ROW(AM$15:AM$47)/10^10,0)),"")

Cell E7
=IF(C7="","",INDEX(AM$15:AM$47,MATCH(C7,B$15:B$47, 0)))

Cell AM15
=COUNTIF(D15:AK15,"ˆš")+COUNTIF(D15:AK15,"P")*0.5 &" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5

The error is in the AM15 cell formula because if I use just a normal number
in that area it works correctly.

Much appreciated!