#### Can you work this backwards

Previously I used 3 simple formulas (if statements) to calculate a
charge, so if E9 =3D=A33000 then the formula for the charge was

=3DIF(E9<=3D1000;E9*1.1;"=A3100") then
=3DIF(AND(E9>1000;E9 <=3D2000);(E9-1000)*0.9;"=A3200")
=3DIF(E9>2000;(E9-2000)*0.5;)
Total Cost ... of above 3 items plus E9 (=A33000)

Now people are asking to spend a total of =A33000 including the charge,
so now Total Cost is to be =A33000 and then I have to work back to find
E9.

How is the best way to do this please.

Bob


 0
bob2381 (34)
11/22/2005 2:57:56 PM
excel.misc 78881 articles. 5 followers.

1 Replies
571 Views

Similar Articles

[PageSpeed] 38

Hi,

Use the "Solver" utility in Excel.

Let us suppose that the total cost [which is the sum of E9 and the charges
calculated with your formula(s)] is in the cell J9.
Invoke "Solver" from the "Tools" menu in the Toolbar,
"Set Target Cell"    J9;
"Equal To"  Check "Value of:" and enter 3000;
"By Changing Cells"  E9
Click "Solve"

Note that the 'Solver' add-in is not installed in a standard installation of
Excel.  If that is the case, install it as follows:

Regards,
B. R. Ramachandran

"Box666" wrote:

> Previously I used 3 simple formulas (if statements) to calculate a
> charge, so if E9 =£3000 then the formula for the charge was
>
> =IF(E9<=1000;E9*1.1;"£100") then
> =IF(AND(E9>1000;E9 <=2000);(E9-1000)*0.9;"£200")
> =IF(E9>2000;(E9-2000)*0.5;)
> Total Cost ... of above 3 items plus E9 (£3000)
>
> Now people are asking to spend a total of £3000 including the charge,
> so now Total Cost is to be £3000 and then I have to work back to find
> E9.
>
> How is the best way to do this please.
>
> Bob
>
>

 0
11/22/2005 5:37:01 PM

Similar Artilces:

Junk Mail and Detect and Repair wont work in Office 2003
I am trying to update the Outlook Junk Mail filters, KB914454 it wont let me in Office 2003 it continually shows the shield icon saying I have an update to complete when I do it fails, tried to repair, that fails too, should I try install even tho my software or CD is Office 2003 and I have SP2 on the computer already or is there a registry fix, do I really need Junk Mail, how can I stop this update from happening, I use AVG and it works fine ...

Can I add a domain to the Blocked Senders list?
I am just getting used to Outlook 2003 and trying to make use of the SP{AM filter. I see that when I right click on a message that one of the options id to add a sender to the "Blocked Senders List" Is there a way that I can also include the option to block a domain? Thanks Dunc Dunc wrote: > Is there a way that I can also include the option to block a domain? There is an easy way, if you're running Outlook 2003+SP1. Dunc wrote: > Is there a way that I can also include the option to block a domain? Right-click on any e-mail, choose (menu) Junk Mail, Junk Mail...

Can I embed a clickable link that includes command line arguments?
I 'm familiar with the process of embedding a clickable link to a file located on a share accessible to the recipient, and do so regularly by using a UNC formatted path: \\ServerName\Path\to the \location\of the\file (Interesting ... the "<" ">" I wrapped the above line in dissappeared as soon as I entered the close bracket. In Outlook [plain text] I'm not used to that happening.) My question: is it possible to do the same thing to launch a .exe (or ..cmd) with command line arguments? Something like: \\UNC Path\Executable /argument1 /a...

How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I want my members to report their wins - 1st, 2nd, and 3rd place - in tournaments to my web site . I would like them to imput: their nickname, date of tournament, tournament Room #, tournament points played for, and host of the tournament. I would further like the calculation to display any change in Ranking as a text cell. Additionally, I want to show for the member, how many more points are needed to get to the next levle and what that (Text)level is. If there is a "what if" for this please advise. Ri...

users can't login on one PC
I have 1 PC that was just upgraded to GP 10 SP 2 and now only the SA can login. When a regular user account tries to login they get the error: "The login failed. Attempt to login again or contact your system administrator" That same id is able to login on every other PC so I know it is not SQL DB security and the SA can login from this PC so i am pretty sure it is not the ODBC. Any help would be appreciated. Fliehigh OK So I figured it out this DSN was setup using the IP address of the SQl server and the rest where using the DNS name. I changed it and it worked great. Fli...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

Working with Managed Allocation Funds
Are there any recomendations for working with managed allocation funds that don't have ticker symbols, but are made of up funds that do. For example, I have a CHET 529, which has as one of its options an "Agressive Managed Allocation Fund". That fund is actually comprised of a set of TIAA-CREF Funds (Equites, International Equity, Real Estate, etc.) which do have ticker symbols. Is there any way to set up this mapping, or should I just manually configure the asset allocation for the "wrapper" fund? Regards, Brian In microsoft.public.money, BrianLeonard wrote: &...

My forms will not work with my email when I click submit.
Sending Form Data via E-mail, it will open up my Email but, the data is NOT contained in the body of the e-mail message. The e-mail address I entered for the ACTION attribute shows up. BUT NOT DATA IS CONTAINED IN THE BODY. ***************************** <FORM METHOD=POST ENCTYPE="text/plain" ACTION="pamjck@juno.com"> Name: <INPUT TYPE="text" NAME="name"> <INPUT TYPE="submit" VALUE="submit button"> ******************************** why is my Email NOT working anymore and adding the data to the body of the e-mail ...

getting sql report 2005 web service to work
The error I am getting in sql reporting service 2005 is 'The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)'. There are lots of answers on the web and I have tried alot of them including obtaining extract space. The error I am getting is: " The report is still being rendered or a report snapshot is not available. (rsReportNotReady)". I am using using windows-authentication to the datasource, and I am wondering if I am not setting up the pass-through properly on t...

IF Statement won't work
I am trying to include an IF statement on a spreadsheet using Excel 97, but the programme always interprets it as a text statement. I'm using the syntax IF(B4>A3,D1,0) or (test,value if true,value if false) which appears to be correct but the problem won't go away. Any ideas? Thanks in advance! Did you type an = sign in front of the IF? =IF(B4>A3,D1,0) The = tells Excel this is an expression, not a string. Lee Schipper "Caliban" <mrkleen@nospamprimus.com.au> wrote in message news:41b28dbb\$1_1@news.iprimus.com.au... > I am trying to include an IF state...

Can't delete a personal folder
Hello. My problem is that I somehow ended up with an extra personal folder and I simply can't delete it. I've tried moving the .PST file and creating a different one. No luck. I've even uninstalled outlook 2000 three times to no avail. After I uninstalled it I looked in "My Documents and Settings" where Outlook and other applications leave data behind and I deleted everything that I found but that didn't work either. I reinstalled Outlook 2000 and there was my Personal Folder again! When I click on the properties and I try to close it it says "Ob...

Can't open some mail after upgrade
Hello.. We upgraded our server to Exchange 2003. After the upgrade our clients using Windows 2000 and Outlook 2000 do not seem to be able to open mail that has been forwarded more than once and contains attachments. I can forward the same piece of mail to someone using an Outlook 2003 client and they can open it, but the client using Outlook 2000 cannot view the mail, preview the mail, forward the mail or even see or save the attacment. The error is: "Can't open this item. Could not complet the operation because the service provider does not support it." Any help...

User Interface stops working
Hi, I am developing an application using MFC in MS Visual Studio 2005. I created a single dialog based MFC application. The framework generates the code required to create the single dialog box. In the InitInstance() function, the dialog box is created using DoModal() to create a modal dialog box. I have added a tab control in the main dialog box. I have 5 tabs. For each tab, I create a dialog box using MFC. Each dialog box contains various buttons and controls. One dialog box contains an ActiveX control object. When a particular tab is selected by the user, the corresponding dialog ...

can't edit or delete rules in Outlook 2002
Hi All, In the Rules Wizard, some rules are shown, but I cannot Edit Modify or Delete them or see the conditions for them. Nor if I click the RUN NOW button do the rules show in the rules list box. Other rules are OK. Why does this happen? Can this be fixed? Does it make a difference that this pst file was viewed in Outlook 2003 (but not converted to Outlook 2003)? Many thanks in advance ...

Can not create mailbox on new volume
Hi, I installed new volume and attached through SCSI to Exchange 2003 server. I created new database on new volume and started to move mailboxes in order to free space. When I try to create new mailbox in new volume it,s fault. i can create account , but not mailbox.i can create on old volume and after to move. I do not know if this is active Directory problems or new HD. Please, help >>When I try to create new mailbox in new volume it,s fault. What does the error say? Is the new store mounted? Vlad wrote: > Hi, > I installed new volume and attached through SCSI to Exchang...

How can I create and display a chart dynamically on a UserForm wi.
I am trying to create charts dynamically using VBA and Excel. The data being plotted will change frequently, and my client wants to interface only with the user form, and not the worksheet itself. I know that you can use the MSChart control to do this in VB6, but I was wondering if there is a similar control for use with Excel and VBA. If so, any additional info on how to use it would be much appreciated. Thanks. I think it's an incredibly dumb idea to want to interface only with a userform (and sacrifice the flexibility of direct access to a chart). Maybe, you can convince your c...

Queries not working after compiling the DB
I have built a Database and spilitted into FE & BE. in the FE i got 2 forms that are based on queries and used for searching. I made MDE file and used startup option to remove admin options so noone can change the DB. Now when i copy the MDE file on different PCs on network to access the BE on server I have noticed that Search option that are based on the queries is not working. Any Idea? while the same file is working fine on my computer. I have made sure everything else is fine. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1...

I can not remove the read only from a file on a cd why
Can someone help me. When I store a file on a cd and then retrieve it, I can not edit it, it comes back as read only. When I go to the file and try to change the attribue within the properties and remove the read only access it is denied. If I save on the hard drive I can make what ever changes I want. Your help will be greatly appreciated. Jeff Smith (212) 929-8024 Jeff, as far as I'm aware, it is still not possible to save a file directly to a CD, as it needs to be "burnt" via burning software . This includes any changes to the file if you have opened it directly from...

CAN'T STOP GLOBAL FORMATTING
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Any number I enter is formatted to 1% of its value and rounded off. E.g. I enter 1485, Excel returns 1.49 Problem not in preferences; and Formatting palette won't cooperate. No macros running. Have re-installed twice. Answered elsewhere � please don't post the same question in multiple groups. Regards |:>) Bob Jones [MVP] Office:Mac On 3/8/09 12:38 PM, in article 59b6ebfd.-1@webcrossing.caR9absDaxw, "JungianPsy@officeformac.com" <JungianPsy@officeformac.com> wrote: > Version: 2008 >...

User Can't Send Mail
When a user has hit their quota limit and I increase it for them what do I need to do to allow the updated limit to be applied to the user so they can send Email again? wait...the default update time for this is 2 hours...or see this: http://support.microsoft.com/kb/327378/en-us -- Susan Conkey [MVP] "D303M" <D303M@discussions.microsoft.com> wrote in message news:563E524F-709F-40CE-A202-228944A920F2@microsoft.com... > When a user has hit their quota limit and I increase it for them what do I > need to do to allow the updated limit to be applied to the user so th...

Can't send email to our domain...
Hi -- We have a client who says they can't send email to us. Can someone decode this message for me? I asked him for the bounced message: The following recipient(s) could not be reached: PBen@mydomain.com on 8/10/2005 8:02 AM There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <IHA4.IHA-CENTRAL.local #5.5.0 smtp;553 Requested action not taken: mailbox name not allowed or chunk too large - on relay of: MAIL FROM:<Mike_O'Donnell@theirdomain.com>> sounds like h...

How can i insert multiple pictures in a word doc, simultaneously?
Hello! I need to put in multiple photos from a file in my reports, and i would like them to be sized to fit 6-10 per page. Is there any way to do that in one simple step? Thanks! It depends on your definition of simple. In order to control the layout of the images you need to insert them in a table of fixed cell width. The images will then adapt to the width of the cell. How many will fit of a page is determined by whether the images are portrait mode or landscape mode - or mixed modes. The following macro will insert a two column table at the cursor and proceed to en...

How do I keep the help window on top and work on my worksheet
When working on a sheet in excel, I open the help window and wish to read it whilst making a suitable entry on the page. The help window closes as soon as it loses focus. In earlier versions this did not happen. Is there a fix for it? If you are using Excel 2003, the top left corner of the help-presented window has a Tile/Untile option. Not really pretty but does enable you to read the help whilst working on the sheet. Howard Walker 635 Wrote: > When working on a sheet in excel, I open the help window and wish to > read it > whilst making a suitable entry on the page. The hel...

Can I Have Beter Code For Search Than This
I Have This Code To Run Search By User Form With Textbox To Enter Search Text Then Return Result On 6 Columns Listbox , Code Working Ok BuT Sometimes If Too Much Data It Takes A Long Time , If Can Be Beter Code I will Be Very Grateful . thanks Private Sub TextBox1_Change() On Error Resume Next Dim KH_Range As Range Dim M As String Set KH_Range = Range("ITEMS_INFO") M = TextBox1.Text ListBox1.Clear If M = "" Then GoTo 1 W = ListBox1.ListCount With KH_Range KH_1 = .Rows.Count KH_2 = .Columns.Count Set A = Range(.Cells(1, 1), .Cells ...