problem combining 2 formulas

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
0
kinsey (18)
8/30/2009 7:51:01 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
351 Views

Similar Articles

[PageSpeed] 10

"kinsey" <kinsey@discussions.microsoft.com> 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" <kinsey@discussions.microsoft.com> wrote in message 
news:F14A0801-F59C-446C-84F9-D523746321FF@microsoft.com...
> 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 

0
joeu2004 (766)
8/30/2009 8:00:09 AM
PS....

I wrote:
"kinsey" <kinsey@discussions.microsoft.com> 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" <kinsey@discussions.microsoft.com> 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" <kinsey@discussions.microsoft.com> wrote in message
news:F14A0801-F59C-446C-84F9-D523746321FF@microsoft.com...
> 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

0
joeu2004 (766)
8/30/2009 8:41:30 AM
Joe, many thanks.  This works well.  I always learn a lot from forum
-- 
kinsey


"JoeU2004" wrote:

> PS....
> 
> I wrote:
> "kinsey" <kinsey@discussions.microsoft.com> 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" <kinsey@discussions.microsoft.com> 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" <kinsey@discussions.microsoft.com> wrote in message
> news:F14A0801-F59C-446C-84F9-D523746321FF@microsoft.com...
> > 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
> 
> 
0
kinsey (18)
8/30/2009 9:08:01 AM
Reply:

Similar Artilces:

2 Questions on Pivot Tables
Any assistance appriciated. (Excel 2003) 1) I have a simple Pivot table showing 6 groups with their totals and a subtotal eg: America 10 UK 15 Oz 22 etc. Grand Total 125 I want to Pivot Graph this data, no problems. I want however an extra line to show the total on the Secondary Axis. I have almost got it by placing the column in a second time as a Running Total and then as a Line on my chart, hiding all the values until the last point. However I would like if possible the Total as a horizontal line across the chart? 2) I h...

Any word on CRM 1.2 release date?
Has anyone heard a firm release date yet for CRM version 1.2? And will CRM 1.2 work with SBS2003 that was just released? -kw "Jim" <jim@nospam.com> wrote in message news:ehxGoPvnDHA.644@TK2MSFTNGP11.phx.gbl... > Has anyone heard a firm release date yet for CRM version 1.2? > > This depends on where you are based. I've just got home from an MS course, and we told that in Australia it will release to the partners at the conference late next month, and will be released to customers on 12/1/04. It was also confirmed that it will run on SBS2003 - it will ru...

Page set up #2
I want to use the paper size of A3, but cannot find it in the paper options. Anyone know where I can find it? Check your printer properties and preferences. If the printers you have installed won't handle A3 you won't have it. And you will need to "install" something that will. Even a "PDF" file printer will do. Alan Badgery wrote: > I want to use the paper size of A3, but cannot find it in the paper > options. Anyone know where I can find it? I've just a home use computer, but the intention was to use the work book between and home and work, w...

Monitoring Exchange 2003 #2
Hello, We are planning to implement MOM 2005. However, we are also concerned about implementing auditing for changes made within ESM. Will there be a tool that will allow management to know a change was completed by a specific UserID? When a mailbox store is dismounted - who dismounted it/date/time? When a change is made to the RUS - who made the change? Thank you, Exchange2003 MOM does not have the ability to monitor this in the shipped Management Pack. You can enable AD auditing on the domain controllers: http://support.microsoft.com/default.aspx?scid=kb;en-us;314955&sd=tech. ...

smtp addresses #2
Exchange 2000. I have 2 smtp addresses under recipient policy. smtp: username@domain.com (set as default) smtp: username@local.domain.com These were created automatically when we migrated from exchange 5.5 to 2000. local.domain.com is our internal domain. domain.com is our Internet address. I unchecked local.domain.com address, and "Apply this policy now", but it did not remove them from each users AD account. So I deleted the local.domain.com address, and "Apply this policy now", but it still did not remove them from each users AD account. We are getting NDR's b...

date problem #10
Thanks a lot, Dave This piece of code works great. M P Redd -- mpredd ----------------------------------------------------------------------- mpreddy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1327 View this thread: http://www.excelforum.com/showthread.php?threadid=26358 ...

Exporting emails from Outlook 2003 #2
I have a folder that contains over 500 threads on technical matters on a subject. I have tried to use Export to Exel but this does not transfere all the information over including not giving all the text in the body of the email. A similar problem is saving an email to a folder outside outlook it doesn't show the date so you may have a huge list of items all titled but not dated in the title Please can anybody help? Hi stevtb why do you want to export the emails. What is the target/reson to export the emails? -- Oliver Vukovics Share Outlook without Exchange (Vista Ready): Publi...

Problem changing parent Business unit
WE have re-arranged some business units into a more appropriate hierarchy, however, we have noticed that a user in a role with deep privileges (own business unit and children I think) to read users can only see users in his own business unit and only one of the child business units (there are 2 child business units). We cannot enable him to see all the users in both child business units. We tried reassigning the parent business unit to the one child that is not appearing, but it doesn't seem to make any difference. WE also tried adjusting the privileges up and down (i.e. Global and local...

Problem
Ever since I added a Microsoft Exchange Server Account and all it6s associated folders and its in-box to my pre-existing Personal Outlook 2003 POP3 folders, I have had nothing but confusion. The Exchange Server account from my workplace downloads its messages into the in-Box for my private ISP POP3 account and vice versa. When I try to configure a rule for my personal POP3 account so that any messages addressed to my workplace account are loaded into the exchange server in-box on my client PC, the software tells me I have to go on-line to Exchange Server - even to create rules for th...

create new users STRANGE problem
Hi, I'm developing a web application (using standard asp and not asp.net) to let certain remote users administer exchange 2003. I have installed win2003 standard edition, exchange 2003 enterprise edition,owa and my web application on the same server. My web app, let users to create mailboxes, delete mailboxes, change user password. All works fine, but for example, if I reboot the server, a web application user can create 50 mailboxes. But when any of these users log on to owa, something goes wrong and the admin user cannot mailbox enable other user accounts through the web app, even if he ...

Problem w/ Outlook
I'm running Office XP and all of a sudden I can't move messages to other folders not delete messages from my Inbox. It doesn't give me a specific error code, just suggests that I restart Outlook. I've tried that with no success. I went to ControlPanel and repaired my Office installation. Any ideas? Eudora is looking beter all the time. ...

training #2
Is there a class offered on Publisher and if so will it ever come to Ohio? Thanks Lots of good training articles available on the Office site. http://office.microsoft.com/en-us/training/CR061832741033.aspx Where in Ohio? -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Cindy Taylor" <anonymous@discussions.microsoft.com> wrote in message news:0ed301c4f293$82579a30$a501280a@phx.gbl... > Is there a class offered on Publisher and if so will it > ever come to Ohio? Thanks On Tue, 4 Jan 2005 19:06:21 -0500, "...

ProbleM: when I restore a mailbox using Exmerge with a pst file, nothing is transferred.
Hi, I am practising Exmerge for a big remote site migration in a couple of weeks. One thing I dont understand is that I can backup one test mailbox fine using Exmerge (I know this works, as I have opened the mailbox pst file within outlook and everything is there), but when I perform the restore using the pst file, nothing happens. There is no error messages, and Emerge goes through the motions (though it finishes supsiciously quick), but when I open the mailbox, no emails have been restored. Although it is great that Exmerge is working for the backup part of the stage, I am disappointed it i...

Problems with Outlook 2003
This only happens with externally sent messages. Internal messages work fine. One of my users alerted me to this issue, so I tested this myself. I created 2 messages (internal and external) and added delayed delivery to both. They were both set to go out at the same time. The internal message went, and the external message is still in the outbox. The user who alerted me to this found her message went "before" the scheduled time.We are not running in cached mode and of course running Outlook on Exchange. Is this a bug? ...

Event ID c0072030 #2
We have an Active Directory Organizational that contain all of our contacts. I have run the delegation of control wizard and given a user full control over the contacts OU. When I log on with that users account and try to update to add an SMTP address to a contact I receive the following error: There is no such object on the server Facility:Win32 ...

Outlook not opening #2
I recently installed Outlook. When I try to open it, I get the error message "Outlook requires Outlook Express 4.01 or greater". I do have OE 5.0 installed. Why am I getting this error? ...

List Boxes #2
Can someone tell me how to correct the following code so that both columns in the listbox will be updated? Only the first column distplays Dim MyArray() ReDim MyArray(mycount, 1) usrGLDist.lstOutput.ColumnCount = 2 For i = 0 To rst.Count - 1 MyArray(i, 0) = rst.Value("descr") MyArray(i, 1) = rst.Value("pcamt") rst.MoveNext Next i 'Load ListBox1 usrGLDist.lstOutput.List() = MyArray Richard wrote : > Can someone tell me how to correct the following code so that both > columns in the listbox will be updated? Only the first col...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

Lost Product Key #2
Wanted to install my publisher deluxe 2002 onto my new computer. I've lost my product key. What can I do? How do I do it? Whistle Britches wrote: > Wanted to install my publisher deluxe 2002 onto my new > computer. I've lost my product key. What can I do? How do > I do it? ================================= Have a look at the following KB article: (823570) How to Obtain a New Product Key for Office Program Setup http://support.microsoft.com/?kbid=823570 -- John Inzer return e-mail disabled ...

RMS POS 2.0 SP1 fails to install
We are running RMS 2.0, and when I tried to install POS SP1 I get the following error" The expected version of the product was not found on your system" I have searched kb and found nothing that may address this. Any ideas would be appreciated. Thanks Sounds like you got the wrong service pack. POS is a different product. You need RMS 2.0 Service Pack 1. Marc "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in message news:EE74C3F9-0CF6-4693-889C-8CD301CA691F@microsoft.com... > We are running RMS 2.0, and when I tried to install POS SP1 I get the &g...

How do I make a particular column required in Excel? #2
Our NPO is creating a reimbursement chart in Excel. We're trying to make it so that if employees do not fill in one particular column entitled "Projects," they will get an error or will be unable to have a final sum filled in. Is there any way I can make this particular column required so that if it is not filled in, the chart will not complete properly? Thank you in advance for any advice or help--I'm a bit of a novice to this! ...

help with lookup formula (sheet 2, not a double post)
I need to make a formula using a lookup. The first spreadsheet is a inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha tells number of gallons of alcohol per foot/inch/fraction. On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i cells R5 and T5. I want these numbers to lookup the number of gallon off of the P&G 103 worksheet. For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2" These two numbers should be added together and multiplied by the numbe ...

Public Folder/OWA problem
We're running a pure Ex2k3 environment. I'm trying to setup a public folder where end-user software can be posted. This way, users can access our front-end OWA server outside of the network, open public folders, and then save the software to their local PC. The problem is that i get a "403 Forbidden" error whenever i try to download the file off of OWA. I checked that users had all permissions to the folder.. I found this TechNet article that describes something very similar (http://support.microsoft.com/kb/834743/en-us). The only problem is that it refers to som...

Shared workbook and VBA problem
Shared workbook and VBA problem Hi Group, I am new at this Excel stuff and I am hitting my head on a silly problem that I am sure many have seen before. I wrote a very simple spreadsheet where I use the Calendar control to pick some dates. Everything was fine until I decided that I needed to share it on the network with other users. As soon as I share it the calendar control stops working. I get a "runtime error 1004, unable to set left property of oleobject class". That's because the first thing I try to do is position the calendar. If I don't do that, it still doesn't...

outlook express 6 password problem
Help I have outlook express 6, windows xp. My internet provider just changed from att broadband to comcast. now i keep getting a password rejected error and i cant receive any mail. i cant seem to find a way to shut off the password. in my wifes page of xp it works ok. Hi, Bill - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these n...