Calculating percent error of each element of a list given a list of ratios

Question

On Excel/Google Sheets, I have a set of measurements (U to Z for this example) that need to match a set of target ratios (for this example, U to Z must follow a ratio of 2:1:14:7:1:1, so ideally W must be twice as large as X and 14 times as large as any of V, Y, & Z)

I wish to know the correct way to normalize the measurements and accurately calculate how far off a value is from its ratios to the other values. So far my actual measurements are normalized against the first value, but would produce disastrous results if the first measurement fails and produces a 0.

I could normalize and calculate the errors for each element in the set as shown in the bottom half of the image, but I don't know where to proceed from here to produce a single definitive error value for each measurement. Ideally I'd also like one-liner formula using only the measurements and ratios as the set size could increase, but if additional cells are still required to get the correct percent error, then so be it.

Thanks!

Answer 1

Probably not the answer you want, but just for the record I'm wondering if it isn't just a heavily disguised least squares fit like this:

If you really needed to consider percentage errors, then you would have to delve into the literature. It would be perfectly possible to implement equations (2) and (3) from this paper in Excel if you so wished.

Answer 2

Using @Tom Sharpe's suggestion of treating this as a regression problem, I was able to determine the slope of my regression plot using my measurements and ratios, then treat the corresponding y-values as the "true" values to calculate the percent error.

Attached below is an image of my one-liner formula on Google Sheets with sample data.