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
