Search code examples
regexgoogle-sheetsgoogle-sheets-formulare2

A cell containing a range of values and making calculations with that range

Is it possible to have a range of values in a cell so that Sheets understands it when calculating something?

Here's an example of the desired output:

     A      B         C
1    Value  Share     Total sum
2    100.00 90-110%   90-110

Here, Total sum (C2) = A2 * B2 (so 100 * 90-110%), giving a range of 90-110.

However, I don't know how to insert this range of values into a cell without Sheets saying #VALUE!.

Solution

  • you will need to do it like this:

    =REGEXREPLACE((A2*REGEXEXTRACT(B2, "\d+")%)&"-"&
               A2*REGEXEXTRACT(B2, "-(\d+%)"), "\.$", )

    0

    for decimals:

    =REGEXREPLACE((A40*REGEXEXTRACT(B40, "\d+.\d+|\d+")%)&"-"&
               A40*REGEXEXTRACT(B40, "-(\d+.\d+%)|-(\d+%)"), "\.$", )

    0