Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Hey Buff
This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being €œbusy€ LOL what is the average file size considered suitable? As it stands right now its at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
This isn't putting the names in order for each week this is placing names
in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
"T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so its best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"ˆš",IF('W1'!C$47= "push","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
The only formula that I need fixed is the following
=IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Hey Biff
I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example€¦. http://www.nghl.ca/Football_Pool.zip Take Care! Rob http://www.nghl.ca/Football_Pool.zip "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Hey Biff
I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example€¦. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Oh, OK I see what you mean. I thought those picks would all be for the same
game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
WOW I see what you mean now by merged cells and useless formulas.
I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Enter this array formula on the Killer sheet in cell E15:
=IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X"))) Copy down as needed. Biff "Killer" wrote in message ... WOW I see what you mean now by merged cells and useless formulas. I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Hey Biff
I gave what you have suggested a try and its not showing the correct result. Example: DAL, IND, NE, CAR should be all €œP€ And CIN, NO should be all €œX€ Right now they are showing as #N/A with this formula. I have uploaded an example again. Thanks! "T. Valko" wrote: Enter this array formula on the Killer sheet in cell E15: =IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X"))) Copy down as needed. Biff "Killer" wrote in message ... WOW I see what you mean now by merged cells and useless formulas. I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Did you enter it as an array?
CTRL,SHIFT,ENTER (not just ENTER) screencap: http://img157.imageshack.us/img157/9123/poolzy0.jpg Biff "Killer" wrote in message ... Hey Biff I gave what you have suggested a try and it's not showing the correct result. Example: DAL, IND, NE, CAR should be all "P" And CIN, NO should be all "X" Right now they are showing as #N/A with this formula. I have uploaded an example again. Thanks! "T. Valko" wrote: Enter this array formula on the Killer sheet in cell E15: =IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X"))) Copy down as needed. Biff "Killer" wrote in message ... WOW I see what you mean now by merged cells and useless formulas. I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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! |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
Stupid me! works awesome!
Thanks a million...... "T. Valko" wrote: Did you enter it as an array? CTRL,SHIFT,ENTER (not just ENTER) screencap: http://img157.imageshack.us/img157/9123/poolzy0.jpg Biff "Killer" wrote in message ... Hey Biff I gave what you have suggested a try and it's not showing the correct result. Example: DAL, IND, NE, CAR should be all "P" And CIN, NO should be all "X" Right now they are showing as #N/A with this formula. I have uploaded an example again. Thanks! "T. Valko" wrote: Enter this array formula on the Killer sheet in cell E15: =IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X"))) Copy down as needed. Biff "Killer" wrote in message ... WOW I see what you mean now by merged cells and useless formulas. I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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 |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formlua Error!
I hope "we're" done with this one! <g
Good luck! Biff "Killer" wrote in message ... Stupid me! works awesome! Thanks a million...... "T. Valko" wrote: Did you enter it as an array? CTRL,SHIFT,ENTER (not just ENTER) screencap: http://img157.imageshack.us/img157/9123/poolzy0.jpg Biff "Killer" wrote in message ... Hey Biff I gave what you have suggested a try and it's not showing the correct result. Example: DAL, IND, NE, CAR should be all "P" And CIN, NO should be all "X" Right now they are showing as #N/A with this formula. I have uploaded an example again. Thanks! "T. Valko" wrote: Enter this array formula on the Killer sheet in cell E15: =IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X"))) Copy down as needed. Biff "Killer" wrote in message ... WOW I see what you mean now by merged cells and useless formulas. I have removed the formula in cell E, E44:E47 as it now required it was a bad copy and paste on my behalf, my bad. I have also removed the merged cells as well in week one and will do the same for the rest of the weeks. I have uploaded the fix to the link below. http://www.nghl.ca/Football_Pool.zip Now you should have no problems fixing the formula to show the correct result. Again thank you for your help Biff you are the best. "T. Valko" wrote: Oh, OK I see what you mean. I thought those picks would all be for the same game but I see they're for *any* game. There's a problem however. All those merged cells are messing up the ability to create a formula for this. I don't understand what this is for: on sheet W1 column E, E44:E47 are merged cells and have this formula in it: =COUNTIF(D45,"?*") D45 will only contain the score for the underdog. So what's this formula for? Why are those cells merged? Biff "Killer" wrote in message ... Hey Biff I think there still a problem whatever the result is in the first column in Cell C47 it puts the result for every cell no matter what team has been chosen. I have up load the file so you can see the example.. http://www.nghl.ca/Football_Pool.zip Take Care! Rob "T. Valko" wrote: The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X"))) It works OK for me. If on W1 I make Ind the fav, the spread is 14 and the score is Ind 14 Dal 0, then the above formula resurns "P" as it should. Biff "Killer" wrote in message ... "T. Valko" wrote: This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. My reply about putting the names in order on the weekly sheets was in response to your email. Oh ok so it's best I manually enter the names to save the file space got it..... right now it's at 1.19 MB and almost completed. Yeah, but you don't have any data in it yet! And, the last I've seen of it, you only have formulas up to week1 and its size at that point was .98 mb. There's still 16 other sheets that need those same formulas. All formulas have been entered throughout the 17 weeks and that's the reason the file has reached 1.19 MB The only formula that I need fixed is the following =IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X"))) it shows a Push as a X. and not as a P then I'm done. Thank You for your help! Biff "Killer" wrote in message ... Hey Buff This isn't putting the names in order for each week this is placing names in a leader board under the Killer picks based on the records same as the master sheet has been done. I didn't understand the comment of being "busy" LOL what is the average file size considered suitable? As it stands right now it's at 1.19 MB and almost completed. Thanks Rob "T. Valko" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Help with this Formlua | Excel Discussion (Misc queries) | |||
Validate Cell contents in formlua | Excel Worksheet Functions | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |