| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula
I currently have a formula:
=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
(I received an error on my initial post, which is why there are 2.) Anyway,
I tried your formula and it comes up with 1, not 54. Any other thoughts? TIA -- Krista "Bob Phillips" wrote: =IF(C26="","",IF(C26="-All Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
What do you have in C26?
Biff "Krista" wrote in message ... (I received an error on my initial post, which is why there are 2.) Anyway, I tried your formula and it comes up with 1, not 54. Any other thoughts? TIA -- Krista "Bob Phillips" wrote: =IF(C26="","",IF(C26="-All Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
This field will be populated from an outside source and will contain a series
of names seperated with ";" or "-All Clubs". I need C27 to then count the number of ";"+1 for the first type of data or 54 for "-All Clubs". Hope this helps to clarify. -- Krista "Biff" wrote: What do you have in C26? Biff "Krista" wrote in message ... (I received an error on my initial post, which is why there are 2.) Anyway, I tried your formula and it comes up with 1, not 54. Any other thoughts? TIA -- Krista "Bob Phillips" wrote: =IF(C26="","",IF(C26="-All Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
That was NG wrap-around, you needed to patch it. Try this version
=IF(C26="","", IF(C26="-All Clubs",54, LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... (I received an error on my initial post, which is why there are 2.) Anyway, I tried your formula and it comes up with 1, not 54. Any other thoughts? TIA -- Krista "Bob Phillips" wrote: =IF(C26="","",IF(C26="-All Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Formula
Perfect -- THANK YOU sooo much!
-- Krista "Bob Phillips" wrote: That was NG wrap-around, you needed to patch it. Try this version =IF(C26="","", IF(C26="-All Clubs",54, LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... (I received an error on my initial post, which is why there are 2.) Anyway, I tried your formula and it comes up with 1, not 54. Any other thoughts? TIA -- Krista "Bob Phillips" wrote: =IF(C26="","",IF(C26="-All Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Krista" wrote in message ... I currently have a formula: =IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1) But I need to add another function to it somehow and I'm striking out. I need to include that if C26="-All Clubs",54. I need to place the number 54 into the formula field if the data in C26 is equal to "-All Clubs" yet still have it count the ";" for all other data that will be input. Any suggestions? -- THANKS! -- Krista |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
