Assigning values to a symbol

[Stephen Doyle]

Hi there,
Is it possible to assign a symbol (in this case a ˆ ) to a value i.e. 1 and
then have them added at the end of the row?
And if so how?

TIA

-- Cheers

Stephen
(e-mail address removed)

 

[Ragdyer]

Any symbol with a 1 assigned to it can be "counted", and then used in
calculations.

=COUNTIF(A1:Z1,"^")
OR
=SUM(B1,COUNTIF(A1:Z1,"^"))

 

[kletcho]

to get the ^ symbol use:

=char(94)

Are you trying to count all instances that have a ^? Does this
describe what you are trying to accomplish?

21^
123
4A^
^RT
3EA

Answer: 3

If this is what you are trying to do then you would use the search
function in an array formula (for some reason the find function does
not recognize ^ if it the first character.) Here is the formula where
B2:B6 is the range you would replace with your own range:

=SUM(IF(ISERROR(SEARCH(CHAR(94),$B$2:$B$6,1)>0),0,IF(SEARCH(CHAR(94),$B$2:$B$6,1)>0,1,0)))

This is an array formula, so you will need to use ctrl+shift+enter to
make it work.

 

[kletcho]

Just thought of something much simpler than my previous windy, pompous
formula. Try this:

=COUNTIF(J12:J19,"*^*")

Should count ^ no matter where it is found in a cell.

 

[Stephen Doyle]

Just thought of something much simpler than my previous windy, pompous
formula. Try this:

=COUNTIF(J12:J19,"*^*")

Should count ^ no matter where it is found in a cell.

Works brilliantly, many thanks.

-- Cheers

Stephen
(e-mail address removed)