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



--
kinsey

PS....

I wrote:
"kinsey" wrote:
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" wrote:
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" wrote in message
...
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



--
kinsey

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


"JoeU2004" wrote:

PS....

I wrote:
"kinsey" wrote:
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" wrote:
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" wrote in message
...
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



--
kinsey