SUGGESTION - Enable using "number-ranges" in cells and with functi

[Zakhary]

The below is a question that I asked on the Excel Discussion Group Board:
<-------------->
How can I add, subtracts, etc. (all functions) with a cell that has a
number-range. How do I input a number range in currency mode in the first
place? To provide you the example at hand...

I am doing cost-analysis for a proposal. My Excel Sheet is set-up with
column A showing the products, service, or other expenses. Column B shows
the cost of each expense. Column C shows how many of the given product,
services, or otherwise are needed. Column D multiplies the Column B cells
with the Column C cells. In the last row of Column D, all of the dollar
amounts in Column D are added (e.g. "SUM=D630").

Some of the costs are not solid costs and instead have a range, such as
$65,000 - $100,000.

First, how can I place "$65,000 - $100,000" in Currency Mode? After doing
such, how do insert in another cell that multiplies that amount by another
number (the number of products, services, etc. needed)?

In the end, I want the spreadsheet to show this:
A25: "Computer Vocational Education Instructor/Local Account and Filtering
Software Manager"
B25: "$65,000 - $100,000"
C25: "2"
D25: "=B25*C25" --> "$130,000 - $200,000"

Then, I want that ("$130,000 - $200,000") to be included in the Grand Total
sum and create a range. For example, if without that range the Grand Total
amount is $62,162.32, I would want it to be changed to read "$192,162.32 -
$262,162.32".

Can ths be done with Excel?

-Zakhary

<------------------>

I was informed by a Silver Level Contributor that this can not happen in
Excel. Analyzed, the suiggestion I have is to make this capable of happening
in the next edition of Excel. It can be very beneficial for various purposes.

-Zakhary

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...95cfc39&dg=microsoft.public.excel.programming

 

[Trevor Shuttleworth]

Well, one way, but you'll need to do a bit of work on the formatting ...

=VALUE(MID(B25,2,FIND(" - ",B25)-1))*C25 & " - " & VALUE((MID(B25,FIND(" -
",B25)+4,LEN(B25)-FIND(" - ",B25)+4)))*C25

That's how you could present it, anyway

To do the sums, use the separate elements to extract the bottom and top of
the range into "helper" columns. Sum the bottom and top values. Combine
the range.

Assumption is:

1 - 5 => 1 5
2 => 2 2
4 - 7 => 4 7
would sum to
7 - 14 => 7 14

Regards

Trevor

 

[Zakhary]

You wrote:
=VALUE(MID(B25,2,FIND(" - ",B25)-1))*C25 & " - " & VALUE((MID(B25,FIND(" -
",B25)+4,LEN(B25)-FIND(" - ",B25)+4)))*C25

Where exactlty does that formula go?

Here is the precise thing that I want to do...

1) In my Excel sheet, I am wanting cell B25 to read "$85,000 - $100,000".
Notice that I am picky and want it in both currency and comma mode as well
as, with a space on each side of the dash!

2) In cell C25, I will have "2" inserted, which denotes the number of the
Cell B25 products that I want. It will act as a multiplier for Cell D25.

3) In Cell D25, I want to multiply Cell B25 by Cell C25. This would
usually be as simple as "=B25*C25", but it obveously won't be this time. I
am looking for this cell (D25) to result in reading "$170,000 - $200,000".
Notice again that I am picky and wanting it in both currency and comma mode,
as well as with a space on both sides of the dash.

4) In Cell D31, I am looking to add the sum of cell range D828. I want a
"number range" to be included in that sum; a "number range" that is based on
the range produced in cell D25. Without Cell D25 considered, the sum is
$67,254.06. With it included, I will want it to read "$237,254.06 -
$267,254.06". Notice once again - I am picky and want it in both currency
and comma mode as well as, with a space on each side of the dash!

Can this be done? From the confidence in your recent posting, I am thinking
that it can.

-Zakhary

 

[Trevor Shuttleworth]

Confidence huh ?

OK, give this a try:

in cell E25 put the following formula:

=IF(ISERROR(FIND(" -
",B25)),VALUE(RIGHT(B25,LEN(B25)-1))*C25,VALUE(MID(B25,2,FIND(" -
",B25)-1))*C25)

in cell F25, put the following formula:

=IF(ISERROR(FIND(" -
",B25)),VALUE(RIGHT(B25,LEN(B25)-1))*C25,VALUE((MID(B25,FIND(" -
",B25)+4,LEN(B25)-FIND(" - ",B25)+4)))*C25)

In cell D25, put the following formula:

=IF(ISERROR(FIND(" - ",B25)),TEXT(E25,"$#,##0.00"),TEXT(E25,"$#,##0.00") &
" - " & TEXT(F25,"$#,##0.00"))

So far, that gives you a number in cell E25 (bottom) and a number in cell
F25 (top). If there is only a number and not a range, you'll have the same
entry in both cells. D25 is the combined and formatted range or value
multiplied by the contents of C25

Assuming you have, say, four rows of data, in cell E30 put =SUM(E25:E28)
and, in cell F30 put =SUM(F25:F28)

In cell D30, put the following formula:

=TEXT(E30,"$#,##0.00")&" - "&TEXT(F30,"$#,##0.00")

Note that this will only work if all the cells in column B are treated as
text ... which they will be for the range values but might not be if you
only have a single value. It also works better for me because my currency
is GBP not the USD so a $ in the cell means it *will* be treated as text.
You might need to work around that.

To explain: $65,000 - $100,000 will always be treated as text because of the
spaces and minus sign. However, $65,000 on its own would be treated as a
number (currency value) in your part of the world but not in mine ... I'd
need it to be £65,000

Hope that all makes sense.

Regards

Trevor

 

[Zakhary]

It doesn't seem like this did the trick. I did the step-by-step
recommendations, but there ended up being VALUE errors.

I went as far as deleting the content I had in Cells E25 and F25.

-Zakhary

 

[SP]

Zakhary

Instead of setting up your formula in one single cell (B25) to show your
range of numbers ($85,000 - $100,000), it's best that you spread your range
of numbers into 3 individual cells; that is, $85,000 in one cell; the dash
"-", in another cell; and $100,000, in another cell. Your result can then
take up 3 other cells (F25, G25, & H25).

That way, you can calculate (multiply or add) however you need by treating
your data as numbers (and not as text). You just need to move your
multiplier (C25) to a different cell (like E25) and redesign your
spreadsheet to something like this...

Item 3) Multiply
B C D E F
G H
row25 $85,000 - $100,000 2 =B25*E25 -
=D25*E25

Item 4) Sum
D E
F
row31 =Sum(D828)+F25 - =Sum(D828)+H25

This is spreadsheet...spread them out. Try!
Steve

 

[SP]

Zakhary

Instead of setting up your formula in one single cell (B25) to show your
range of numbers ($85,000 - $100,000), it's best that you spread your range
of numbers into 3 individual cells; that is, $85,000 in one cell; the dash
"-", in another cell; and $100,000, in another cell. Your result can then
take up 3 other cells (F25, G25, & H25).

That way, you can calculate (multiply or add) however you need by treating
your data as numbers (and not as text). You just need to move your
multiplier (C25) to a different cell (like E25) and redesign your
spreadsheet to something like this...

Item 3) Multiply
B C D E F G H
row25 $85,000 - $100,000 2 =B25*E25 - =D25*E25

Item 4) Sum
D E F
row31 =Sum(D828)+F25 - =Sum(D828)+H25

This is spreadsheet...spread them out. Try!
Steve

 

[Zakhary]

Those cells have other information in them and adding additional cells would
amke this spreadsheet look dubious, since this is the only instance where a
range is needed.

This information is for the cost of hiring someone for a specific position
depending on their experience. What I have resorted to is placing the
mid-point in the cell and making a note at the bottom of the Excel sheet that
92,500 is a mid-point and that the costs can be anywhere between 85,000 and
100,000.

I still suggest that Microsoft make an easier way to do this.

-Zakhary

 

[SP]

If you can't afford to add cells, then try out Trevor's formula. You'd type
that formula into cell D25. What the formula does is separating your number
ranges in B25, multiplying them with C25, and assembling the result back to
D25 - all in one single cell. While this may help with your multiplying
need, additional formulas will be necessary for your summation need.

I think you are looking for a general/simple solution to a highly customized
setup.

Steve