Need special formula for data entry

[raviprakash369]

Hi,
please find the below image and do the needful to create special formula.

Thanks in advance.

Regards,
Ravi

 

[IDS]

Assuming you want something more complicated than simple + and - formulas, it isn't clear to me what you want to do.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

I suspect something like ="+/-"&text(f1,2) is what he's angling at, but who knows?

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[raviprakash369]

I want to print the values before + or minus symbol and after + or - symbol in next 2 columns.

 

[IRstuff]

Is this homework? Student posting is prohibited.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[raviprakash369]

haha, if homework, why should I post, I would have asked my teacher.
If you know the answer, please help. No need to comment showing your highness.

 

[IRstuff]

"if homework, why should I post"

This site averages 2 student posts per week, because that's what some students would rather do.

Excel has string handling functions such as Left, Mid, Right for parsing strings. You can use the Find function to look for the ± character.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IFRs]

I often use custom formatting rather than concatenation - it keeps the cell contents to numeric values that can be used elsewhere.

 

[SkipVought]

You posted thread770-420991, very similar. No resolution there???

What have you tried in either of these two threads?

What works or does not work?

Often if you explain what you tried and what results you observed, it can be helpful.

BTW, images are great for admiring. But if you want help, a worksheet is required. I won't waste my time trying to transcribe your picture.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MintJulep]

I guess that you could do this with an ordinary formula. No need to use a special one.

 

[SkipVought]

Yes, just native Excel functions like, LEFT(), RIGHT(), FIND(), LEN()

For instance, just to get you started, Nominal could be...
[tt]
=--LEFT($A2,FIND("±",$A2)-1)
[/tt]
...where A2 is the source cell.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Thank you Skipvought, I got some formula for the same.
=LEFT(B13,FIND("±",B13)-1)


=SUBSTITUTE(B13,CONCATENATE(C13,"±"),"")

Thank you very much all for your support.

Regards,
Ravi

 

[SkipVought]

What is
=SUBSTITUTE(B13,CONCATENATE(C13,"±"),"")
about?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

To print value next to plus or minus sign.

 

[IRstuff]

> Isn't that what you're starting with?
> Your columns suggest that you need to handle the case where + and - tolerances are different, which means that you need to handle something like mm.mm + pp.pp - nn.nn

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

ravi, please read my comments in thread770-420991.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

But I feel sorry for you, so here's a solution using Named Ranges.
[pre]
Display Nominal Plus tol Minus tol max value min value
20±0.01 20 0.01 -0.01 20.01 19.99
[/pre]

[tt]
Nominal: =--LEFT(Display,FIND("±",Display)-1)
Plus tol: =--RIGHT(Display,LEN(Display)-FIND("±",Display))
Minus tol: =-RIGHT(Display,LEN(Display)-FIND("±",Display))
max value: =Nominal+Plus_tol
min value: =Nominal+Minus_tol
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

If the plus and minus tolerances need to be differentiated, then a couple of IF statements will be needed.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

He's just asking about THIS one thing.

The other stuff is multiple cells, and I have no idea how the requirement will be stated. He's all over the place.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Hi SkipVought,
As I am Design engineer and very new to excel, took the help of this forum.
Thanks for the support and I got solution.
I will continue the next plan converting these formulae to userform by help of colleagues here.
Once again thank you very much eng-tips and supporters.

Regards,
Ravi.