LinkBack URL
About LinkBacks
If I have a range in cell A1 (e.g. 3.76-4.32) and want to put the midpoint of the range (4.04) in B1, what formula should I put in B1? What number format (general, number, text) should A1 be?
If I have a range in cell A1 (e.g. 3.76-4.32) and want to put the midpoint of the range (4.04) in B1, what formula should I put in B1? What number format (general, number, text) should A1 be?
Give this formula a try...
=AVERAGE(LEFT(A1,FIND("-",A1)-1),MID(A1,FIND("-",A1)+1,99))
Rick, as always, has brilliant formulas.
But I just want to point out that any time you have numbers/values in a cell, that also include any text (like - for instance, or 2 .'s), that number becomes text. Then fancy formulas are needed to convert that text back to values so that you can run formulas and clacs like you want.
Ir is almost always better (and simpler) to keep each number in it's own cell, then this would have been a simple average() function
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That worked. Thanks. The number formatting for A1 was "general". Also, for the range, you must use "-" Hyphen-Minus (character code: 002D), not "—" Em Dash (2014), or "–" En Dash (2013).Originally Posted by Rick Rothstein
Last edited by tukae; 08-30-2019 at 12:33 AM.
Formatting doesnt really work of=n text cells. And formatting only changes the appearance of a cell, not it's contents.
Do you need the flexibility to use Em and En dashes as well as normal dashes? If so, give this formula a try...
=AVERAGE(LEFT(A1,FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"—","-"),"–","-"))-1),MID(A1,FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"—","-"),"–","-"))+1,99))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks