1

I have a large table where in the first column for some row rather than just a single value there is a range e.g.

enter image description here

I want to apply a calculation on all the data elements, but since in the first column there isn't always just a number but sometimes a range written as you see in the above sample, I don't know how to do this properly.

The result that I want in a result column is the value of each row multiplied for a factor e.g. 0.3. The result column should show another range as result if the input value in Param 1 column is a range or just a single result if the corresponding Param 1 value is just 1 value e.g. enter image description here

Improve this question
5
  • Could you please let us know which calculations do you want to do on this table, and what else does the data mean?
    – Emily
    Commented May 24, 2022 at 7:08
  • You could transform the range into a list of values, with whatever granularity is appropriate, and apply your formula to each iteration. Depending on your specific needs, there may be simpler methods.
    – Ron Rosenfeld
    Commented May 24, 2022 at 11:20
  • Similarly to Emily's question, if it is a range do you want to use the middle, min, max? as well. Item1. The start of the range, can it be higher than the end, or is that more a typo in your example?
    – BradR
    Commented May 24, 2022 at 11:54
  • Please show the desired result. For example, what should the result of Param 1 be, and how is it calculated? If this is too complex to describe in the question, then give a simplified example instead.
    – Jim K
    Commented May 24, 2022 at 15:23
  • I have added more details to clarify.
    – AndreaF
    Commented May 29, 2022 at 16:47

1 Answer 1

Reset to default
1

The displayed sample data could be considered to be in A1:E4, with row and column titles (headers) in row 1 and column A, and so on. In that case, the formula for cell E4 (last row in the example) would be:

=IFERROR(VALUE(B4)*0.3,
         VALUE(LEFT(B4,FIND("-",B4)-1))*0.3&"-"&VALUE(RIGHT(B4,LEN(B4)-FIND("-",B4)))*0.3)

First calculation in the IFERROR() uses VALUE() to indicate whether a single value exists in the target cell, or not. In the case of failure, the second calculation breaks apart the string forming the range into the piece to the left of the "-" and the piece to the right of it. It uses VALUE(), this time to convert each string into a number and then multiplies each such by the ".3" and finally forms a string of the resulting left valuefactor, a new "-", and the resulting right valuefactor.

It does depend upon the consistent use of the "-" for separating the two components in "range" cells, though one could, with lots of effort, handle several possibilities. An alternate entry form might have "space-dash-space" or similar mess-ups but these would be better handled by inserting a TRIM() function around the string pieces inside the VALUE() wrappers to take off the leading or trailing spaces. Other variations might be found and one could probably find various clevernesses to deal with them.

It is very straightforward though, separating, operating upon, and remaking the strings. So your only real trouble will be dealing with the errant data entries people make (and there will be plenty). Not a problem though, if the data comes from some data source that is written by a program, not data entry!

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .