Forcing a formula to ignore the tilde 1

[cwash]

Is it possible to have a formula ignore the tilde and still give me a result from the formula?

ex:
Cell A1 contains the value 2
Cell B1 contains the ~4
Cell C1 contains the formula =A1+B1

I am using the ~4 to indicate that it is 'an estimate' or an 'assumption' of what I think the number should be.

someone told me that the ~ can be used for this purpose, but of course, I cannot get the formula to give me a result- all I get is #Value.

Any help is greatly appreciated.

 

[SkipVought]

What a bummer!

Try this...
[tt]
=IF(LEFT(A1,1)="~",RIGHT(A1,LEN(A1)-1),A1)
[/tt]
Verify references.

copy down

The COPY column and Edit > Paste Special - VALUES over the ORIGINAL column.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cwash]

thanks for the reply, but it's not giving me the sum of A1+B1 in C1. i am only getting the value of the cell containing ~(and the number).

what i would like to get is the sum of a1+b1 into c1, ("4" in the example below).

a1=1
b1=~3
c1=4

 

[SkipVought]

"... but it's not giving me the sum of A1+B1 in C1. .."

The procedure I gave you does not give you ANY sum.

It makes it POSSIBLE to eliminate the tilda, in order that you CAN sum.

BTW, it DOES work!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cwash]

yes- your formula did work, but without the sum.

correct me if i am wrong, are you saying that i would need to perform a three step process:

1) perform your procedure,
2) remove the tilde; and then
3) perform the sum function

 

[SkipVought]

1) Performing the procedure DOES remove the tilda
2) Makes it possible to Sum

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cwash]

Okay, i think i got it: (but correct me if i am wrong)

1) i put your formula in D1 and copied it to the other rows (D2, D3, etc)-
2) i then took the values that were produced from your formula and copied/paste (values) into B1, B2 (etc)
3) wallah...C1, C2 (etc) gave me the sum of A1:B1, etc.

i'm slow, but not slow enough to give thanks when warranted!

Thank you for your patience!

 

[SkipVought]

glad you got it! thanx!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Gavona]

Glad you got it sorted. It is a much better idea to have 'pure' numbers and maybe a separate column to indicate estimates.
Don't fall into the trap of using colours to identify estimated values if you will ever need to quickly find or ignore the estimates. You could use a modified version of Skip's formula in column E to extract the tilde if you don't want to lose it.

That said, given the data you have, this formula in C1 would also do what you want without the need to copy and paste.
=VALUE(SUBSTITUTE(B1,"~",""))+A1

Regards,

Gavin