how to deduct a % from sum #2

How would I change this formula so that I can deduct 5% of the calculation? 
The 5% figure would be put in a seperate cell so that the % could be 
altered, meaning 5% would be in say cell A2, and I might require to change 
the 5% to say 7%.

The % figure relates to an inflation level to be deducted from a lump sum of 
money.

=D9+D10-D11

My attempt...

=D9+D10-D11-(A2%) 


0
nonense50 (76)
8/25/2009 10:02:27 AM
excel 39879 articles. 2 followers. Follow

2 Replies
1429 Views

Similar Articles

[PageSpeed] 7

In your case, depending on how you stored the percentage:

=(D9+D10-D11)*(1-A2)


But take a look at my standard reply about percentages in excel:

=============================================================
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% 
as 1. That makes it easy to calculate with; just multiply a number with a 
percentage and you get what you need. No need to divide/multiply by 100. In 
fact, if you see a calculation with percentages which has the number 100 
somewhere in the formula; be very careful, it might be wrong or at least use 
percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the % sign: 
if you enter "15%" (without the quotes) the value will be 0.15 and it will 
be displayed as 15%. If you then enter 12 in the same cell, two things can 
happen: It will be the number 12 or 12%. What happens in your case depends 
on a setting:
Tools>Options, Edit tab, "Enable automatic percent entry" (only Excel2000 
and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak 
use this representation of percentages: be careful when supplying parameters 
to these functions; never use whole numbers (like 8), always use fractions 
(like 0.08 or, even better, 8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %

Q:
In A1 I have he end price.
In B1 I have the VAT percentage, entered like 15%
What is the formula to find the base price, excluding VAT?
A:
 =A1/(1+A2)

=============================================================

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SS" <nonense50@blueyonder.co.uk> wrote in message 
news:OQOkm.5806$AC5.2825@newsfe06.ams2...
> How would I change this formula so that I can deduct 5% of the 
> calculation? The 5% figure would be put in a seperate cell so that the % 
> could be altered, meaning 5% would be in say cell A2, and I might require 
> to change the 5% to say 7%.
>
> The % figure relates to an inflation level to be deducted from a lump sum 
> of money.
>
> =D9+D10-D11
>
> My attempt...
>
> =D9+D10-D11-(A2%)
> 

0
nicolaus (2022)
8/25/2009 10:27:48 AM
  Thanks now working ok.

"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:7FB26564-4BC0-4C2E-B686-23C7B78C41C6@microsoft.com...
> In your case, depending on how you stored the percentage:
>
> =(D9+D10-D11)*(1-A2)
>
>
> But take a look at my standard reply about percentages in excel:
>
> =============================================================
> About percentages in Excel
>
> Niek Otten, July 26 2006
>
> In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% 
> as 1. That makes it easy to calculate with; just multiply a number with a 
> percentage and you get what you need. No need to divide/multiply by 100. 
> In fact, if you see a calculation with percentages which has the number 
> 100 somewhere in the formula; be very careful, it might be wrong or at 
> least use percentages in a way they weren't meant to be used in Excel.
> The conversion to a fraction happens automatically if you enter the % 
> sign: if you enter "15%" (without the quotes) the value will be 0.15 and 
> it will be displayed as 15%. If you then enter 12 in the same cell, two 
> things can happen: It will be the number 12 or 12%. What happens in your 
> case depends on a setting:
> Tools>Options, Edit tab, "Enable automatic percent entry" (only Excel2000 
> and newer).
> All built-in functions of Excel and all the functions in Analysis Toolpak 
> use this representation of percentages: be careful when supplying 
> parameters to these functions; never use whole numbers (like 8), always 
> use fractions (like 0.08 or, even better, 8%).
>
> Frequently Asked Questions:
>
> Q:
> I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
> A:
> Formula in C1: =B1/A1, Format as %
>
> Q:
> I have A1 and B1. How do I show the difference as a percentage in C1?
> A:
> As a percentage of A1: =(B1-A1)/A1, Format as %
> As a percentage of B1: =(B1-A1)/B1, Format as %
>
> Q:
> In A1 I have he end price.
> In B1 I have the VAT percentage, entered like 15%
> What is the formula to find the base price, excluding VAT?
> A:
> =A1/(1+A2)
>
> =============================================================
>
> -- 
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "SS" <nonense50@blueyonder.co.uk> wrote in message 
> news:OQOkm.5806$AC5.2825@newsfe06.ams2...
>> How would I change this formula so that I can deduct 5% of the 
>> calculation? The 5% figure would be put in a seperate cell so that the % 
>> could be altered, meaning 5% would be in say cell A2, and I might require 
>> to change the 5% to say 7%.
>>
>> The % figure relates to an inflation level to be deducted from a lump sum 
>> of money.
>>
>> =D9+D10-D11
>>
>> My attempt...
>>
>> =D9+D10-D11-(A2%)
>>
> 


0
nonense50 (76)
8/25/2009 11:24:43 AM
Reply:

Similar Artilces:

VLOOKUP issue #2
If I use a vlookup function, can I have three result cells? In othe words, if the value in a1 is found in the lookup table and there ar three columns (offsets) that could be used as correct answers, can display all three -- Message posted from http://www.ExcelForum.com Yes, you can use =VLOOKUP(A1,Lookup_Table,{2,3,4},0) assuming you don't want the value from the leftmost column, you need to select 3 columns across (like if you would select E2:G2 with E2 as the active cell), then enter the formula in the formula bar and instead pressing enter press ctrl + shift & enter or you co...

Renaming a Mailbox #2
Hello folks, What is the best way for changing a users email address? The reason for the change request is one of my users married over the weekend, and so her last name has changed? I understand some people recommend setting up a new address for the user over renaming an existing one. So, I'm not certain what the best approach is here. I currently manage a small Windows 2000 single domain network and run Exchange 2000 Enterprise Edition. Thank you in advance for any suggestions. WE ususally just create a new SMTP address, and leave the old one in place so that mail to the o...

missing FRx reports #2
Hello: A client of ours on FRx 6.7 service pack 6 and GP 8.0 says that she created three FRx reports and now they're gone. They have over 50 companies in FRx and about 15 different specification sets. I looked at the building blocks in the Specification Set window by clicking "Export". I do not see the three reports that my client says is missing. I deleted the .g32 files and compacted the system database and spec set data base of the company whose reports are missing. No success! Any ideas? Thanks! childofthe1980s Which components are missing? Do they have backu...

exported excel charts and pdflatex #2
pdfLatex is compatible with jpg and pdf images, and real pdfs loo better than jpgs it seems. I found that the best way was to make a .p file of the chart by printing to a postscript printer, and the converting to pdf. All is done in vba (uses ghostscript) -- hk ----------------------------------------------------------------------- hke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=155 View this thread: http://www.excelforum.com/showthread.php?threadid=26893 JPG is the wrong format for most charts anyway, being designed for photographic-type images with cont...

No sound from autodialer #2
I am using the auto dialler within outlook which works fine except .. I cant hear the call be connected. The modem sound is definitely on as I can hear the dial tone, dialling out and connection signal if a dial an ISP. However when outlook use the modem to place a call it is as if the sound has been turned off. It would be nice to hear it connecting and ringing so I can then pick the phone up when the other party responds. I have looked all over outlook 2000 but can't find a setting. Any ideas please ? ***** Ivo You need a voice/data(/fax) modem -- do you have one? "iv"...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

ERROR 1053 when strating CRM 1.2 on Windows 2003
When strating the MSCRM Security Service I get the following error. ERROR 1053: THe service did not respond to the strat or control request in a timly fashion. We have just installed CRM prior to migrating it from a 2000 server. Any sygestions on what could be causing this ?? Thanks ...

Resending Messages #2
In Outlook 2000, I sent an email message to a bad email address in my Contacts list. When the message bounced, I went into Contacts and entered the correct email address. Then, I opened the message from the Sent folder to resend it. When I double clicked on the email address, it showed the new, correct email address. However, when I sent the email, it bounced again from the old email address. I can fix the problem by starting from scratch, but I would like to know what I'm doing wrong. You probably didn't clear your AutoNameCheck List, so its still resolving the name to the ol...

outlook hanging up #2
I would like to pass along a solution to outlook hanging up when signing on. Here is the scenario: I ctrl/alt/del to get out and then when signing on again it would put me in safe mode I also notice that changing file names or setting up new folders also hung up vista. All of this relates to the search index which apparently had been clobbered. The solution is as follows: HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/WINDOWS SEARCH CLICK ON THE WINDOWS SEARCH FOLDER and then change the value of SetUpCompletedSuccessfully to 0 Open Indexing Options (you can find this in help "When sho...

Sum of a UNION Query
Hello, I want to sum the values together that are returned from the following two queries joined with a UNION from within the same query (instead of creating a second query that will SUM the two values that are produced from this one). I tried "wrapping" this code within a SELECT SUM, but it returns with "this is not allowing in subqueries". SELECT Count(*) AS SubCount FROM [TABLE1] WHERE (([TABLE1].USER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [TABLE2] WHERE (([TABLE2].USER)="EJONES"); The results are: SubCount -------------- 380 45...

company split
Hi all, hoping to get some advice on this, the company i work for consists of two divisions who are effectively looking to split, and do there own thing. But initially will continue to share resources such as IT. Is it possible in exchange to allow them both to have there own email addresses such as name@company1 and name@company2 and to hide these so that relevant individuals only see the names of users relevent to there division/company in their address book. Effectively what i need to be able to do is run 2 seperate companies from one exchange box. bearing in mind that both compani...

right click #2
Can a 'right click' menu be changed? I'd like to add some things For example, I'd like to select some cells then right click on the selection and convert it to values Also, I've recorded a macro that converts a selection to values, but it's probably not the most efficient way to do it. What's the easiest way to convert a selection to values through a macro? This macro would add the ability to bring up the sort wizard from the right click menu. I took the basic idea from John Walkenbach's book Excel 2000 Power Programming with VBA. For more information ...

XmlTextReader Question #2
Hi, I am starting out an xml file, example's below. It will be like that but larger. I am having problems actually getting at the inner nodes. Example the items I named InputOne, State, DebounceOn. Not sure how to get each data item Any help, directions would be appreciated. Right now I am doing in C#, SystemIO.StreamReader stream = new SystemIO.StreamReader("theFile.xml); XmlTextReader reader = null; reader = new XmlTextReader(stream); while( reader.Read()) switch(reader.NodeType) { case XmlNodeType.Element: Console.Write( reader.Name + reader.Value); break; case XmlNo...

How can I change the Z-axes of my window or dialog? #2
Hello, All: I am now meetting a problem that the sub-window( or sub-dialog) would not be shown out in front of the parent window, however, I just wanna let it be shown in front of my parent window. Is there anybody who knows how I can set the Z-axes with any function? Thanks in advance! ...

Camera Sale -Nikon , Canon, Minolta , Sony ,Xcam2 , Fujifilm , #2
Camera Sale -Nikon , Canon, Minolta , Sony ,Xcam2 , Fujifilm , http://shopforless.itgo.com/ Camera Sale -Nikon , Canon, Minolta , Sony ,Xcam2 , Fujifilm , from $50 -up for the top brands http://shopforless.itgo.com/ ...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Novice Q: open 2 VSD files
I would like to open 2 drawings at the same time, on the same workspace, so I can create a single drawing from both. How can I do that? TIA You can open multiple vsd files simultaneously. I often do this and drag/drop components among them. Al <dnoam@il.ibm.com> wrote in message news:1109937598.678865.262500@o13g2000cwo.googlegroups.com... >I would like to open 2 drawings at the same time, on the same > workspace, so I can create a single drawing from both. > How can I do that? > TIA > Thanks. In your case I will have to switch between the open windows. Is there a ...

auto sum function
I just realized my final sum changes if I use the "sort ascending" function..... How do I fix this ? thank u Hi where is your sum formula located? -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > I just realized my final sum changes if I use the "sort > ascending" function..... > > How do I fix this ? > > thank u Down a colum But also, i just found out info changes on each lines when i use the "sort ascending" tool... >-----Original Message----- >Hi >where is your sum formula located? &...

Macro to find a row and delete it and the 9 rows above it. #2
Thanks alot that did the trick. This forum and you guys are life saver -- Rusty_n ----------------------------------------------------------------------- Rusty_nl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=671 View this thread: http://www.excelforum.com/showthread.php?threadid=31376 ...

OPOS drivers #2
hello all still no solution found on my original tread. anyway about OPOS drivers this is the question. generally, should a configuration utility become installed with this drivers to configure the devices? the magnetic card reader OPOS driver loads one with the drivers while my thermal printer does not. just wandering. try to estabilish witch one is bad. thank you in advance ...

cell reference #2
I have a list of numbers in column A. I wish at the bottom to return the maximum value in column A using =max(a1:a100) but i also want the cell reference of this value. how can i do this. thanks Use the MATCH function - it will give you the relative position of the maximum value in your range, and as this begins at A1 then this is the same as the row. If you have two maximum values, however, it will only report the first occurrence. Hope this helps. Pete lees news wrote: > I have a list of numbers in column A. I wish at the bottom to return the > maximum value in column A using =m...

Sum every other cell? #2
I just previewed the post below and saw that the format didn't hold. don't know how to make it look right. I hope you can figure it out fro what you see. Sample rows: Day 1 Day 2 Day 3 Category #Books $$$ #Books $$$ #Books $$$ 1 4 16.00 0 0.00 7 29.95 2 3 17.50 3 17.50 3 17.50 3 0 0.00 6 24....

Preview Pane in Outlook XP #2
Does anyone know how to move the preview pane in Outlook XP? I thought it couldn't be done but I have a client that has his moved to the right instead of the bottom, I've looked everywhere I can think of but I can't seem to find how to change it back. Any help would be greatly appreciated! Thanks!! Lee Your client apparently has Outlook 2003.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers...

ListCtrl #2
Hello I have a bitmap background and I want my List Control to have transparency so the background goes through, Is there anyway to do this? TIA Nancy ...

Excel Crashing #2
Hello all Apologies all, I originally sent this to the VB newsgroup by mistake. I am using Windows 2000 with Microsoft Office 2000 Professional. The Excel version is 9.0.3821 SR-1 All of a sudden for no reason Excel started to crash on opening with the message "Excel.exe has generated errors and will be closed by Windows. You will need to restart the program." If i start Excel with the "/o" command line switch, it starts OK, but I cannot start Excel directly or use any shortcuts to my data files to start Excel like I used to. I have tried uninstalling, reinstalling, un...