problem combining 2 formulas

K

kinsey

Hello, I have two rates of exhange and I want to find the mid point and
deduct 3% all in one formula.

The bank buy rate is 1.6706 (cell a1)
The bank sell rate is 1.6085 (cell a2)

so =sum(a1-a2)/2 gives me the difference beween the two rates and I divide
by 2 to find the basis for the mid point which is 0.03105 (cell a3)

Then I create another formula (a3 + a2)*0.97 to give me the mid point -3%
(or 97% of it the same either way)

How can I combine the two formulas? I always seem to have a problem with
circular refererences.

thanks

kinsey
 
J

JoeU2004

kinsey said:
How can I combine the two formulas?

=(1 - 3%) * (a1 + a2) / 2

so =sum(a1-a2)/2 gives me the difference beween the two
rates and I divide by 2 to find the basis for the mid point
which is 0.03105 (cell a3)
Then I create another formula (a3 + a2)

The easier way to compute the midpoint is:

=(a1 + a2) / 2

FYI, your first formula, which computes have the difference, could be
written more simply as:

=(a1 - a2) / 2

No need and no point to use the SUM function.


----- original message -----
 
J

JoeU2004

PS....

I wrote:
kinsey said:
so =sum(a1-a2)/2 gives me the difference beween the two rates and I
divide by 2 to find the basis for the mid point which is 0.03105 (cell
a3)
Then I create another formula (a3 + a2)*0.97 to give me the mid
point -3%
[....]
How can I combine the two formulas?

=(1 - 3%) * (a1 + a2) / 2

I dutifully copied the computation in your original article. Note that:

(a3 + a2) = ((a1 - a2) / 2 + a2) = (a1 - a2 + 2*a2) / 2 = (a1 + a2) / 2

But now I wonder if your original formula is computing what you intended.

Do you want to reduce the midpoint value (1.63955) by 3%, as you are doing?

Or do you really want to reduce the difference between the low end and the
midpoint (0.03105 = 1.63955 - 1.6085) by 3%?

If the latter, you might want the formula:

=a2 + (1 - 3%) * (a1 - a2) / 2

If you are not sure, ask yourself the following question: what result would
you expect if you "reduced the midpoint by 100%"?

If your answer is: you expect the low-end number (1.6085), then you want
the second formula.

If your answer is: you expect zero, then you want the first (original)
formula.


----- original message -----

kinsey said:
How can I combine the two formulas?

=(1 - 3%) * (a1 + a2) / 2

so =sum(a1-a2)/2 gives me the difference beween the two
rates and I divide by 2 to find the basis for the mid point
which is 0.03105 (cell a3)
Then I create another formula (a3 + a2)

The easier way to compute the midpoint is:

=(a1 + a2) / 2

FYI, your first formula, which computes have the difference, could be
written more simply as:

=(a1 - a2) / 2

No need and no point to use the SUM function.


----- original message -----
 
K

kinsey

Joe, many thanks. This works well. I always learn a lot from forum
--
kinsey


JoeU2004 said:
PS....

I wrote:
kinsey said:
so =sum(a1-a2)/2 gives me the difference beween the two rates and I
divide by 2 to find the basis for the mid point which is 0.03105 (cell
a3)
Then I create another formula (a3 + a2)*0.97 to give me the mid
point -3%
[....]
How can I combine the two formulas?

=(1 - 3%) * (a1 + a2) / 2

I dutifully copied the computation in your original article. Note that:

(a3 + a2) = ((a1 - a2) / 2 + a2) = (a1 - a2 + 2*a2) / 2 = (a1 + a2) / 2

But now I wonder if your original formula is computing what you intended.

Do you want to reduce the midpoint value (1.63955) by 3%, as you are doing?

Or do you really want to reduce the difference between the low end and the
midpoint (0.03105 = 1.63955 - 1.6085) by 3%?

If the latter, you might want the formula:

=a2 + (1 - 3%) * (a1 - a2) / 2

If you are not sure, ask yourself the following question: what result would
you expect if you "reduced the midpoint by 100%"?

If your answer is: you expect the low-end number (1.6085), then you want
the second formula.

If your answer is: you expect zero, then you want the first (original)
formula.


----- original message -----

kinsey said:
How can I combine the two formulas?

=(1 - 3%) * (a1 + a2) / 2

so =sum(a1-a2)/2 gives me the difference beween the two
rates and I divide by 2 to find the basis for the mid point
which is 0.03105 (cell a3)
Then I create another formula (a3 + a2)

The easier way to compute the midpoint is:

=(a1 + a2) / 2

FYI, your first formula, which computes have the difference, could be
written more simply as:

=(a1 - a2) / 2

No need and no point to use the SUM function.


----- original message -----

kinsey said:
Hello, I have two rates of exhange and I want to find the mid point and
deduct 3% all in one formula.

The bank buy rate is 1.6706 (cell a1)
The bank sell rate is 1.6085 (cell a2)

so =sum(a1-a2)/2 gives me the difference beween the two rates and I divide
by 2 to find the basis for the mid point which is 0.03105 (cell a3)

Then I create another formula (a3 + a2)*0.97 to give me the mid point -3%
(or 97% of it the same either way)

How can I combine the two formulas? I always seem to have a problem with
circular refererences.

thanks

kinsey
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top