#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help Needed FordFiestaST150 Excel Worksheet Functions 1 November 3rd 06 09:41 AM
Formula help needed ! thegooner Excel Discussion (Misc queries) 1 July 26th 06 11:47 AM
Formula needed Connie Martin Excel Worksheet Functions 10 May 4th 06 01:19 AM
Formula Needed! Roman Excel Discussion (Misc queries) 2 June 19th 05 09:29 PM
Formula needed Viewpoint Excel Worksheet Functions 1 January 30th 05 03:06 AM


All times are GMT +1. The time now is 10:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"