Returning a range from an inner function

[Nathan Sokalski]

I have a range of data, which has had the DOLLAR() function performed on it.
I would like to get the total of this range using the SUM() function. doing
SUM(F1:F4) will not work, so I need to convert the data into values that
will work. The VALUE() function is perfect for converting the data into
values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not
work because VALUE() will not accept a range as a parameter (and also does
not return a range). I need to perform the VALUE() function on each cell
before submitting it to SUM(). There must be some way to do this,
considering how much people display totals of dollar values, and considering
that SUM() is probably the most used function in Excel, people must need to
convert data from it's natural form sometimes, right? Any help would be
appreciated. Thanks.

 

[Max]

Try, array-entered*:
=SUM(--F1:F4)

*Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing
ENTER

The "--" will coerce the text values to numbers

 

[Peo Sjoblom]

Also the OP's original formula =SUM(VALUE(F1:F4)) would work array entered

 

[T. Valko]

Another one:

Normally entered:

=SUMPRODUCT(F1:F4+0)

 

[Max]

Also the OP's original formula =SUM(VALUE(F1:F4)) would work array entered

granted .. but only at the cost of another 3 keystrokes <g>

---

 

[Nathan Sokalski]

Thanks, that works great for this situation, and I appreciate finding a
solution. But is there a general way to perform a series of functions on the
value of each cell before submitting those values as a range to the
outermost function? I would like to note that even though your solution
works great for this specific scenario, there may be cases where the
operation is much more complex, and will probably involve other functions.
For example, you may want to round up each value in a range before
submitting it, or perform an IF function to use 0 in some cases and the
original value in others, or who knows what else someone might want. Is it
really impossible to perform an operation on each cell value before
performing the outermost function? Thanks.

 

[T. Valko]

Your follow-up is impossible to answer! But I'll say this, you should be
able to manipulate those values almost any way you want *after* you coerce
them to numeric numbers. That's what this is doing: F1:F4+0. This has to
done as an array. You can round them or use them if IF functions but you'd
need to site specific needs.