Hello!
While with the function =SUM((VALUE(10*2));(VALUE(3*3));(VALUE(1*2))) I get the correct result (31), I could not find a way to calculate with text values of a range of other cells:
I’d need the sum of the results of a calulation entered by the user, something like =SUM((VALUE(A1:A3))) but even a simple =VALUE(A1) does not work, where A1 is formatted as ‘text’.
NB: Working with additional columns is an option I’d consider as very last resort.
Many thanks for every input!
A function like that is usually called “eval” or “evaluate” in other programming languages. Spreadsheets don’t have any such function. Your formula in A4 can be written simpler without VALUE as =SUM(10*2;3*3;1*2) because each of the terms returns a number. The only purpose of VALUE is to convert a text value into a number, for instance =VALUE("1234")
Hallo
Split »A1:A3« by →Data→Text to Columns: Delimiter * into two Columns, an use Formula:
=SUMPRODUCT(A1:A3;B1:B3)
Hallo karolus,
thanks for the suggestion: this would clearly improve my “worst case solution”. Nevertheless, as the spreadsheet has already far too many columns, and this construction would need to be implemented repeatedly, I hope to be able to solve this without adding additional columns: Just to minimize the risk of erroneous input by its users: Entering something else (like ´10*2´) in a long row of numbers could give some structure to the document, which surely would improve its readability and reduce input errors.
Hallo Villeroy,
thank you for the hint! Now I’d need a way to replace the single terms (like 10*2) in the formula with the cordinates of cell that contains these terms. Let it me draft it like this, which does not work:
=SUM(A1;A2;A3) or, as finally required, =SUM(A1:A3). As I am starting to understand, that this (EVAL) is not possible, I am now trying to go with the LEFT and RIGHT functions in a hidden column and figure out, how to use the multiplication-asterisk * as separator.
Hallo
try
=SUMPRODUCT(VALUE(REGEX(A1:A3;"^\d+"));VALUE(REGEX(A1:A3;"\d+$")))
Hallo karolus,
your REGEX approach is great! As I have to deal with empty cells / lines, I decided to use it with a hidden column (with =PRODUCT(VALUE(REGEX(A1;"^\d+"));VALUE(REGEX(A1;"\d+$"))) etc. in each line) and then a simple SUM over that hidden column at the bottom of column A.
Thanks you very much: The latest issue is solved!
better:
=PRODUCT(VALUE(REGEX(A1;"(\d+)\*(\d+)";{"$1"|"$2"})))
OR:
=IFERROR(PRODUCT(VALUE(REGEX(A1;"(\d+)\*(\d+)";{"$1"|"$2"})));"")
Hi karolus,
thanks for the update, I’ll keep it in mind for future projects, but stay with your first suggestion. It’s working fine now and the users can use both: x, as they are used to; and * for those who know the numpad. Also nice: The size of the returned kegs doesn’t need to be specified.
Here the relevant snippet of my current beta:
where the D3 function translates to
=IF(NOT(ISEMPTY(B3));(PRODUCT(VALUE(REGEX(B3;"^\d+"));VALUE(REGEX(B3;"\d+$"))));"")
Best regards,
Florian
