Formula needs to adapt to newest data in column

[JVD1990]

Hi all,

I want to make a formula in which a part of that formula refers to the lowest value in a column. The formula is: 88,362 + (13,397 x weight in kg) + (4,799 x length cm) – (5,677 x age in years). In my excel sheet I have a column for weight in which I have a row for each week of the year. I want my formula to adapt to my current weight, which means that if I add a new row with data to my column I want my formula to automatically refer to that value without having to change the formula.

So if my current weight is in C34 and I add my new weight in C35, the formula automatically has to refer to the data in that cell. If I add data in C36, the formula has to adapt to the weight posted in that cell. And so on, and so on. I've tried different things and did some thorough searching, but either I haven't found a solution to this problem yet or I didn't understand potential solutions.

I hope I can find some help here

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[JVD1990]

Hi,

I hope this example worksheet will do!

[Glenn Kennedy]

I'll give it a go and see....

[Glenn Kennedy]

In C2:
=IF(A2="","",88.362+(13.397*A2)+(4.799*185)-(5.677*35))

copied down... (optional extra!!)

and in H2:
=LOOKUP(300^10,C:C)

[JVD1990]

Thank you very much for your answer, it helped me solve my problem! I've combined your solution in H2 within the formula to use it in my own worksheet to get:

=88,362+(13,397*(LOOKUP(300^10;C:C))+(4,799*185)-(5,677*35))

This way I can just use column A in the worksheet to get the wanted solution.

Thanks again!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.