Formula needed

[Guest]

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

 

[Pete_UK]

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

 

[Niek Otten]

Hi Steve,

Format>Cells>Number tab>Custom, in the "Type" box, enter:

[=10]"X";General

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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

 

[Guest]

Thanks "pete_UK" ...works perfectly!!!

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

 

[Pete_UK]

Glad to hear that - thanks for feeding back.

Pete

 

[JeannieC]

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]

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

 

[JeannieC]

Thank you sooooo much for your help! It worked perfectly.

 

[Pete_UK]

You're welcome, Jeannie - thanks for feeding back.

Pete