Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disounts Help Please
Hi all
Excel 97 I have a sheet like A1........B1.............C1...............D1...... .........................E1....................... .....F1 qty....unit cost..... discount... total with discount... vat..........................total with vat at 17.5% 1........£100...............30.00%..........£70... ....................£12.25.....................£82 .25 Now what I cannot find out how to do is this In D1 Take B1 then multiply by A1...... then take C1 off I have put it like this =(B1*A1)*C1 BUT that does not work. Because it gives me £30.00 and it should be £70.00 Also how would I put in F1........ B1 plus A1 less C1 multiply by 17.5% to give me a grand total As always, any and all help is greatly appreciated. Thank you in advance. John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disounts Help Please
First formula required in D1: =B1*A1*(1-C1)
Second formula in F1: = D1+E1 In E1 to get the VAT amount put: =D1*0.175 You might consider putting in a reference cell at the top of the sheet to set the VAT rate so if it changes you can update all rows. For example if you put in A2 17.5%, then the formula in E1 would be =D1*$A$2 The $ symbols makes the cell A2 absolute the same as you copy the fomula down the column. Cheers Nigel wrote in message ... Hi all Excel 97 I have a sheet like A1........B1.............C1...............D1...... .........................E 1............................F1 qty....unit cost..... discount... total with discount... vat..........................total with vat at 17.5% 1........£100...............30.00%..........£70... ....................£12.25 ......................£82.25 Now what I cannot find out how to do is this In D1 Take B1 then multiply by A1...... then take C1 off I have put it like this =(B1*A1)*C1 BUT that does not work. Because it gives me £30.00 and it should be £70.00 Also how would I put in F1........ B1 plus A1 less C1 multiply by 17.5% to give me a grand total As always, any and all help is greatly appreciated. Thank you in advance. John ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disounts Help Please
John,
Try D1: =A1*(B1-(B1*C1)) E1: =D1*17.5% F1: =D1+E1 or simply =D1*(1+17.5%) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) wrote in message ... Hi all Excel 97 I have a sheet like A1........B1.............C1...............D1...... .........................E 1............................F1 qty....unit cost..... discount... total with discount... vat..........................total with vat at 17.5% 1........£100...............30.00%..........£70... ....................£12.25 ......................£82.25 Now what I cannot find out how to do is this In D1 Take B1 then multiply by A1...... then take C1 off I have put it like this =(B1*A1)*C1 BUT that does not work. Because it gives me £30.00 and it should be £70.00 Also how would I put in F1........ B1 plus A1 less C1 multiply by 17.5% to give me a grand total As always, any and all help is greatly appreciated. Thank you in advance. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|