Hi everyone,
Please find attached the spreadsheet I am working on.
In here I am trying to input a number in column H that makes the value in column F match with the value in column D. However, at the moment I am doing this manually as you can see in H8/H9.
Column I contains a range of numbers between 0.10 and 160. The number in column H will be in this range and therefore I require a formula that selects a number in this range to match column F with the column D.
Thank you
Welcome to the forum.
There is no 2107 version of Excel (that's just a release number). Please update your profile to MS365. Thanks.
Explain in detail the sample calculations you have made manually - how did you arrive at those figures?
Last edited by AliGW; 08-29-2021 at 04:18 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you
All done, thanks again!
The manual calculations I have done are simply guesses to make the value in column F match with column D. However, if I was to manually guess each value it would take forever with the amount of data I have.
I therefore need a function that finds a value in column I and inputs it into column H that makes column F match with D.
Does this answer your question?
No. I don't even know how to start guessing. What did you take into account when you guessed? I really cannot see the connection between the 'guessed' number and anything else, since none of your formulae seem to use that 'guessed' number.
I'm at a loss - sorry.
Sorry, it's really hard to explain via message
From the data I am attempting to get a value in column H that matches column F with column D (column F is calculated acceleration data and column D is acceleration data derived from 2D cameras).
The value in column H will be between 0 and 160 so I added this range in column I.
Now I need a function in cells H8-H17 that identifies a number from column I to make column F match with D.
If you highlight the formula in column F you will see that I am guessing the number in column H that makes the number in column K fit the column F equation to make column F match with D.
This equation in column F is acceleration = force/mass.
If you change the value in H10 manually it may also help explain what I mean?
Apologies again if this doesn't make sense, via message on here it is really difficult!
Thanks - hopefully someone will tune into your explanation. Seems it's more of a maths problem than anything, and I'm tempted to suggest that you might want to look at the SOLVER add-in.
Fingers crossed!
I will take a look at this now, thanks again.
I think i get your point but i believe putting in a formula would create circular references. what i understood is that you need to put down a formula to a cell, on which not one, but many cells are depended upon.
for eg,
(A2)+ (A3) = 4 (Cell A4)
*assuming both A3 and A2 has 2 as value*
here, 4 is calculated on the basis of values entered in A2 & A3
If you enter a formula in A3, ie =A4-A2, it would create a circular reference.
correct me if i am wrong, but what i understood from your request is that you want A2 or A3 to be automatically filled with 2 through some formula.
I dont know if there is any solution for this issue, but keenly looking forward to see if there is any.
Last edited by AliGW; 08-29-2021 at 07:26 AM. Reason: PLEASE don't quote unnecessarily!
Give a sec to give rep to all who tried to help
Yes this is along the same lines as what I am trying to achieve.
Essentially I need excel to automatically manipulate cells numbers to achieve a given value that matches another.
Hopefully a solution is available.
how did you calculate the values in cells H8 and H9?
did you put in random numbers until you got a match, or did you use some mathematical approach?
@janmorris
from the post i understood that he is entering the data manually, possibly random numbers until you get a match. i did some calculations myself and i ended up having his desired figure, but..
1) i am not sure how to put it in excel using formulas
2) even if i figure that out, there would be circular references
Yes, I am entering random numbers until I get a match.
Helpless in this through formulas. if you are to use Solvers, you may use this formula, put your desired acceleration in value of, selecting H10 (since the formula i used is for H10) in Changing Variable Cells.
=(((H10*9.81)+$E$2)-$B10)/(((H10*9.81)+$E$2)/9.81)
But you will have to go to each cell and do the solver one by one.
In cell H10 use formula =($E$2*9.81-$B$8*9.81-D10*$E$2)/(D10*9.81-9.81^2)
Last edited by larsgaard; 08-29-2021 at 10:34 AM.
Thank you everyone for your help, larsgaard this equation works great!
Another possibility is to run GoalSeek in a loop using a macro (could be done by solver also).
where i is the last row in column A with a value i.e. row 17 so the value of i is set dynamically every time the macro is run.
Please Login or Register to view this content.
Alf
Hi Abith
Not really as the number of lines in the uploaded file is quite small so setting a reference to solver in VBA (VBA -> Tools -> References and tick box Solver) a macro like this will give you all results in one go. The possible problem is that the excel solver is limited to a maximum of 200 constraints so number of rows for solver to work on must be less than 200. This setup is only defined by the cells to change and the constraint that F values = E values, and objective cell value is not needed.But you will have to go to each cell and do the solver one by one.
AlfPlease Login or Register to view this content.
Last edited by Alf; 08-29-2021 at 01:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks