i am working on a look-up for a 2 dimensional matrix. A little bit of context for my problem:
I am trying to find a value based on two different (numerical) inputs: mileage of a car and operational time. I have already implemented the matrix and a junction table in the style of this base, which was posted on a quite simmiliar topic ([Probability matrix value driven by two "single-select" fields / nested-ifs / multi-variate lookup]) before:
My roll-up formula in my “opportunities” table is using a combination of MID(), LEN() and FIND() formulas, in order to return the assoicated percentage. However, i have encountered several problems:
- The fields which are to be looked have a range of different lengths (e.g. -0.01, 0.14), so that the MID() formula doesnt work as the different values do not have the same length
- The Rollup formula in my “Opportunities” table is not returning a percentage but a string. As i want to use the returned percentage for calculations, in need the lookup to return me a percentage and not a string
I’d be glad for any suggestions!
Thank you very much