How to create a function of 2 mathematical formulas?

[killwin98]

Hello,
I'm new to excel, and I'm trying to create a formula, but I don't know how.

I have created a table, and the data is in LogMAR units.
I am comparing baseline and final results, and I am able to create such a graph, but instead of that I would like to create a graph that shows the mean change from the baseline of the LETTERS. Each letter equals 0.02 LogMAR.



Here is some data. So in this example, the baseline number (A77) should be zero, and the rest of the numbers should be in relation to the initial number, so in B77 there is a decrease of 0.08 (1.17-1.09). That is a good thing - the patient can read 4 more letters (0.02 x 4 letters = 0.08), so the mean change is +4.
C77 should be again in relation to A77: 1.17-1.04=0.13. 0.13/0.02=6.5, so here the value of the mean change should be +6.5.

(row 1 here is the months)

I hope this was clear enough. I would really appreciate your help!

[oeldere]

Welkom to our forum,

Probably I can't help you with your problem, but since your on an excel forum it would be great if you add an excel example, without confidentional information.

Please also add the desired (expected) result.

In a pic we can't work to test our solutions, in an excel file we can.

[killwin98]

Here is a link, and this is the example:

The baseline number (A2) should be zero (because we measure the change from here), and the rest of the numbers should be in relation to the initial number, so in B2 there is a decrease of 0.08 (1.17-1.09). That is a good thing - the patient can read 4 more letters (0.02 x 4 letters = 0.08), so the mean change is +4.
C2 should be again in relation to A2: 1.17-1.04=0.13. 0.13/0.02=6.5, so here the value of the mean change should be +6.5.
(Row 1 is the months)

[oeldere]

To Attach a File:

A lot of members don't like to open a file on an external site.

You can add the file as an attachment in your question.

But I don't see any formula in your file, and also don't see the expected result.

How to attache a file.

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[killwin98]

I have no problem uploading files here, I just thought it would be easier to post a link

I don't have any formula in my file because I don't know how to create one. That's why I posted this thread. I gave an example of what I wish to do with the data in the post above.

Do you think you could help me to create such formula?

Thank you!

[oeldere]

If you can tells us how you should do it manualy; then we can make a formula for that.

But than you have to give us also the desired (expected) result.

If you don't have that (can't make that) you don't have an excel problem, but another problem.

[killwin98]

I will explain again.
Cell A2 should be the initial (baseline) number, so its value should be zero. All the other cells need to relate to that baseline number (not to the zero value, but to the actual number).

In the file I attached, A2 equals 1.17, the its value should be zero "0".
B2 e1.09. The difference between A2 and B2 = A2-B2. In this case, it equals 0.08. Now here should be another formula, because for each consecutive value after calculating the difference between A2 and the other cells, should come another formula, which is dividing the results by 0.02. In this case, the result is 4.
So now, what we have so far is A2=0 and B2=4.

If we move to the next cell, C2, we calculate again the difference, using the original value of A2 (1.17). Here we should subtract A2-C2 = 1.17-1.04 = 0.13.
Like the previous example, we should always divide the result by 0.02, so in this case C2 = 6.5

If we look so far at the results, we have A2=0, B2=4, C2=6.5

Could you help me to create such formula?

Thank you in advance!

[oeldere]

See the green cells.

(removed file, for moderators request in #8)

[killwin98]

See the green cells.

(removed file, for moderators request in #8)

I changed the title of my thread and PMed already arlu1201. Could you please send again the file? Thank you!

[oeldere]

Hi Kevin,

Sure I can.

See the attached file.

[killwin98]

Thank you! If I drag the formula to other cells on the right (and putting more info in row 2), I get the results I need.
The problem is that I cannot continue the formula without formulating row 4 before. Is there a way to avoid that?
And why do I need anything below row 5? The only number I need for this formula is 0.02 which is in A5.

I appreciate your quick help!

[oeldere]

Q The problem is that I cannot continue the formula without formulating row 4 before. Is there a way to avoid that?

A not sure what you mean.

if the problem is that you want to use that row, you can made your formula in another row.


Q And why do I need anything below row 5? The only number I need for this formula is 0.02 which is in A5.

A you don't need them, they were just there to show you how it's made.

[shg]

killwin, perhaps if you could explain exactly how this all gets used, someone could draw a straighter line toward a solution.

[killwin98]

Oeldere,
I don't need row 4 at all. It's just the middle of the calculation. Could I have a straightforward formula that contains both these calculations?

Shg,
I would like to plot a curve graph where the x axis is months, and y axis is mean average change of my data, related to the baseline mean average data (first cell in a row). I already have the mean average for each month, but I would like to plot the mean change from baseline.

[killwin98]

Oeldere,
I don't need row 4 at all. It's just the middle of the calculation. Could I have a straightforward formula that contains both these calculations?

Shg,
I would like to plot a curve graph where the x axis is months, and y axis is mean average change of my data, related to the baseline mean average data (first cell in a row). I already have the mean average for each month, but I would like to plot the mean change from baseline.

[shg]

The change in visual acuity?

[killwin98]

The change in visual acuity?

yes, exactly

[shg]

So post a workbook with an example of how your data is disposed.

[killwin98]

What oeldere did with my dummy file is great, it's just that I don't need the 4th row, and I wonder if there is a way to create a formula that would include both calculations.

[shg]

OK, I'll let you two carry on.

[MrShorty]

If I understand it correctly, it looks like a fairly basic "composite" function, if you remember composite functions from algebra.

f(x)=a-x (this is what is in row 4)
g(y)=y/b (this is what is in row 5)

You are trying to find g(f(x))=g(a-x)=(a-x)/b

In essence, it appears to me that, if you substitute ($A2-B2) for B4 into row 5, you will get what you want =($A2-B2)/$A5.

[oeldere]

See the attachment.

Please reply if this is what your up to.

[oeldere]

See the attachment. (removed attachment) => duplicated post.

[killwin98]

See the attachment.

Please reply if this is what your up to.

Oeldere, this is exactly what I wanted! Thank you!