Using functions for a cell with a number-range

[Zakhary]

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

 

[Duke Carey]

You can enter

$65,000 - $100,000

in a cell ONLY as text. Therefore, it wouldn't be included in any math
functions you might use.

The only way to accomplish what you want is to use 2 columns: devote one
column to the low end of the range and another to the high end of the range.
For items that have a single cost, i.e., not a range, use the same value in
each column.

 

[Zakhary]

If I were to do the recommendation - one HIGH Column and one LOW Column, will
I be able to have a result where the Grand Total reads in a range (e.g.
"$192,162.32 - $262,162.32")?

-Zakhary

 

[Duke Carey]

You'll have to sum the two columns separately. However, you can create a
formula that combines text and values to get what you want. Assume the sum
of the low end of the range is in cell B25 while the sum of the high end is
in C25. You could use a formula along the lines of

="Grand Total: "&text(B25,"$#,##0.00")& " - "&text(C25,"$#,##0.00")

That would display somethng like:

Grand Total: $25,000.00 - $50,000.00