| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with Adding Decimals
I want to count the number of times a specific string occurs in a row. Then
I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Adding Decimals
If S.5 is in A1, =MID(A1,2,2) will yield .5, to which you can apply any
mathematical operation. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Elaine" wrote: I want to count the number of times a specific string occurs in a row. Then I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Adding Decimals
I don't understand. Can you tell me what to put in which cell? I want the
total to appear in cell AK8. -- Thanks for your help! Elaine "Dave F" wrote: If S.5 is in A1, =MID(A1,2,2) will yield .5, to which you can apply any mathematical operation. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Elaine" wrote: I want to count the number of times a specific string occurs in a row. Then I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Adding Decimals
I think I misinterpreted your question, sorry.
Why don't you change the S.5 part to: (COUNTIF(B8:AF8,"S.5")/2, since you're assigning it the value of .5 (i.e., 1/2). So you get: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")/2) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Elaine" wrote: I don't understand. Can you tell me what to put in which cell? I want the total to appear in cell AK8. -- Thanks for your help! Elaine "Dave F" wrote: If S.5 is in A1, =MID(A1,2,2) will yield .5, to which you can apply any mathematical operation. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Elaine" wrote: I want to count the number of times a specific string occurs in a row. Then I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Adding Decimals
I think it's doing what I need. Such a simple addition to what I find to be
a complicated string. I'm new at Excel so I REALLY appreciate your help!!! Many thanks! -- Elaine "Elaine" wrote: I want to count the number of times a specific string occurs in a row. Then I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Adding Decimals
Simplify version
=SUM(COUNTIF(B8:AF8,{"S","DF","C","W"))+COUNTIF(B8 :AF8,"S.5)/2 "Elaine" wrote: I want to count the number of times a specific string occurs in a row. Then I want to add whole numbers and decimals. Ex: Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for a total of 4. Cell B13 contains the string "S.5". I want to this item to count as a half or .5. The Sum of B1:B13 = 4.5 Problem: I can't get "S.5" to count as .5. I'm using the following formula to count each occurrence and then give me a total number of all cells: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5")) Is there a way to include an automatic .5 without actually subtracting it from the end of the string? EX: =COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNT IF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8: AF8,"S.5"))-.5 -- Thanks for your help! Elaine |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Displaying a number with different decimals depending on a condition | Excel Worksheet Functions | |||
| Desire Sum to add visible figures rather than underlying decimals | Excel Discussion (Misc queries) | |||
| Adding Decimals | Excel Discussion (Misc queries) | |||
| Rounding Error when adding or subtracting two cells | Excel Worksheet Functions | |||
| How do I calculate without decimals in excel? | Excel Worksheet Functions | |||
Linear Mode
