Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
I need to be able to put a letter, X into a cell which would then, when the
row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Here's an array* formula that should do it:
=SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, X into a cell which would then, when the row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Hi Steve,
FormatCellsNumber tabCustom, in the "Type" box, enter: [=10]"X";General -- Kind regards, Niek Otten Microsoft MVP - Excel "Sprinter" wrote in message ... |I need to be able to put a letter, X into a cell which would then, when the | row was added, the X would count as 10. The X needs to remain in the cell, | and not just simply change to 10. | However I still need to be able to return a value in to the same cell, such | as 1, 2, 3, etc if I dont want to use the letter X. | | Hope some one can help and thanks in advance! | | Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Thanks "pete_UK" ...works perfectly!!!
"Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, X into a cell which would then, when the row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Glad to hear that - thanks for feeding back.
Pete On Nov 14, 8:59 pm, Sprinter wrote: Thanks "pete_UK" ...works perfectly!!! "Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, X into a cell which would then, when the row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
I have the same problem, I have a series of data that requires addition,
however, some cells contain the letter "C" which must equal to 60. The formula Pete_UK provided in this thread worked wonderful. However, now that I have the columns added, I have to divide the total by 60 to reach the total number of hours instead of minutes. How do I add that division calculation to that formula? Can anyone help me? Thank you "Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, X into a cell which would then, when the row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Well, I'm glad to hear it worked for you, and that you are using the
archives to look for solutions. If I understand you correctly, all you need to do is divide that formula by 60, so you will have something like this: =SUM(IF(A1:A20="C",60,A1:A20))/60 Don't forget to use CTRL-SHIFT-ENTER to commit the formula. Hope this helps. Pete On Dec 10, 5:59*pm, JeannieC wrote: I have the same problem, I have a series of data that requires addition, however, some cells contain the letter "C" which must equal to 60. *The formula Pete_UK provided in this thread worked wonderful. However, now that I have the columns added, I have to divide the total by 60 to reach the total number of hours instead of minutes. *How do I add that division calculation to that formula? Can anyone help me? Thank you "Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, *X into a cell which would then, *when the row was added, *the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
Thank you sooooo much for your help! It worked perfectly.
"Pete_UK" wrote: Well, I'm glad to hear it worked for you, and that you are using the archives to look for solutions. If I understand you correctly, all you need to do is divide that formula by 60, so you will have something like this: =SUM(IF(A1:A20="C",60,A1:A20))/60 Don't forget to use CTRL-SHIFT-ENTER to commit the formula. Hope this helps. Pete On Dec 10, 5:59 pm, JeannieC wrote: I have the same problem, I have a series of data that requires addition, however, some cells contain the letter "C" which must equal to 60. The formula Pete_UK provided in this thread worked wonderful. However, now that I have the columns added, I have to divide the total by 60 to reach the total number of hours instead of minutes. How do I add that division calculation to that formula? Can anyone help me? Thank you "Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, X into a cell which would then, when the row was added, the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
You're welcome, Jeannie - thanks for feeding back.
Pete On Dec 11, 4:56*pm, JeannieC wrote: Thank you sooooo much for your help! It worked perfectly. "Pete_UK" wrote: Well, I'm glad to hear it worked for you, and that you are using the archives to look for solutions. If I understand you correctly, all you need to do is divide that formula by 60, so you will have something like this: =SUM(IF(A1:A20="C",60,A1:A20))/60 Don't forget to use CTRL-SHIFT-ENTER to commit the formula. Hope this helps. Pete On Dec 10, 5:59 pm, JeannieC wrote: I have the same problem, I have a series of data that requires addition, however, some cells contain the letter "C" which must equal to 60. *The formula Pete_UK provided in this thread worked wonderful. However, now that I have the columns added, I have to divide the total by 60 to reach the total number of hours instead of minutes. *How do I add that division calculation to that formula? Can anyone help me? Thank you "Pete_UK" wrote: Here's an array* formula that should do it: =SUM(IF(A1:A20="X",10,A1:A20)) Adjust the range to suit - I have assumed numbers in A1:A20. * As this is an array formula, then once you have typed it in (or subsequently amend it) you need to commit it with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will put curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Upper or lower case X will be treated as having a value of 10. Hope this helps. Pete On Nov 14, 7:01 pm, Sprinter wrote: I need to be able to put a letter, *X into a cell which would then, *when the row was added, *the X would count as 10. The X needs to remain in the cell, and not just simply change to 10. However I still need to be able to return a value in to the same cell, such as 1, 2, 3, etc if I dont want to use the letter X. Hope some one can help and thanks in advance! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help Needed | Excel Worksheet Functions | |||
Formula help needed ! | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions | |||
Formula Needed! | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions |