Excel 365 Back Calculate Cleaned Data to Raw Data

Question

My Problem:

I am using the Microsoft 365 Excel app for desktop.

My dataset comes from a programmed tool that collected raw data and automatically converted it to the final value using an equation I programmed into the tool.

I now realize that I want that raw data, "x", rather than the converted data, and I know that I will need to iteratively back calculate the equation for each converted value in the dataset to get that raw "x" value, but don't know how.

The equation is the following:

(4.824E-10)(x^3) - (2.278E-6)(x^2) + (3.898E-3)*(x) - 2.154

The converted values are each in their own cell in a single column in excel.

What I have tried:

I have tried solving the equation by hand and then plugging in the solved equation to excel, but my understanding is that I would have to do that individually for each data value in the data set.

I also tried the Solver Add-in, but as you can see in the images provided, I am getting an error saying that a formula needs to be provided in the "Set Objective" cell, even though I have provided that, so I am not sure if I am referencing the right sells to make Solver work correctly.

($F$5 contains the answer to the equation that I am trying to back step to find "x", which is cell $AE$5)

First image is a screenshot of how I filled in the Solver window.

Second image is the error I got when I hit "solve" in the Solver window.

I would like to make this an automated process for the entire dataset, if possible.

Thank you for any insight that you can provide.

Answer 1

A third-degree equation can be solved in several ways: with the help of the Solver, with the help of the Goal Seek, by the use of cyclic calculations, and finally there are analytical formulas that can be used and later the calculations can be performed using a Data Table. Here's an example in the screenshot.