LinkBack URL
About LinkBacks
Can I sum a hyphenated range? For example:
Column A - Quantity
10 - 15
Column B - Rate
$1.00
Column C - Total (i.e. contains formula A * B = C)
$10 - $15
Can I sum a hyphenated range? For example:
Column A - Quantity
10 - 15
Column B - Rate
$1.00
Column C - Total (i.e. contains formula A * B = C)
$10 - $15
Not in any way that is useful other than for display purposes - you would not be able to use the result in a SUM formula, for example.
A simple way to do what you want: use columns A, B, and C with
10
'-
15
and in D use $1
then in E F and G use
A*D
'-
C*D
Bernie Deitrick
Excel MVP 2000-2010
Thanks for the reply. That idea did occur to me, but was hoping there was another way.
You could use something like this:
=B1*LEFT(A1,SEARCH("-",A1)-1)&" - "&B1*MID(A1,SEARCH("-",A1)+1,255)
But the result, like the entry in A1 will be TEXT, not a number.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
That's very clever! Do you know if there's a way to tweak to the formula so that the final number always has two numerals after the decimal (i.e. 1.50 vs. 1.5) and dollar symbols in it? I.e. column C displays as $20.00 - $30.00
Use the TEXT function, and make sure that you use TRIM and VALUE as well
=TEXT(B1*VALUE(TRIM(LEFT(A1,SEARCH("-",A1)-1))),"$0.00")&" - "&TEXT(B1*VALUE(TRIM(MID(A1,SEARCH("-",A1)+1,LEN(A1)))),"$0.00")
Maybe you want...
=TEXT(B1*LEFT(A1,SEARCH("-",A1)-1),"$0.00")&" - "&TEXT(B1*MID(A1,SEARCH("-",A1)+1,255),"$0.00")
HTH
Regards, Jeff
Yep.
Use this:
="$ "&TEXT(B1*LEFT(A1,SEARCH("-",A1)-1),"0.00")&" - "&"$ "&TEXT(B1*MID(A1,SEARCH("-",A1)+1,255),"0.00")
Thank you!
You are very welcome. We are happy to help.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks