Adding Separate Columns of a Table To Obtain 7% of Total

oldeirish

New Member
Joined
Nov 24, 2014
Messages
40
How to calculate 7% of tax from 3 different columns? Can I input all my data then convert to a table?

New Subtotal
Credit
Over Due Fee
Column1
HST 7%
$ 365.00
$ 5.00
$ 10.00
37070%
$ 500.00
$ 70.00
$ 5.00
57035%
<tbody> </tbody>


This is the first I tried
=SUM([@[New Subtotal]]+[@Credit]+[@[Over Due Fee]]*7%)

This is the 2nd one I tried
=SUMPRODUCT([@[New Subtotal]]+[@Credit]+[@[Over Due Fee]]*0.07)

Thank you so very much!! J
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

oldeirish

New Member
Joined
Nov 24, 2014
Messages
40
I spoke too soon, I apologize. The formula is great, but the percentage always comes out in the thousands.

Thank you again for all your help!

 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287

ADVERTISEMENT

That surprises me, but you could use =SUM([@[New Subtotal]],[@Credit],[@[Over Due Fee]])*0.07 .
Did I misunderstand? is the answer wrong, or is the formatting on the formula cell wrong, or is the original data in thousands and you want the new column to show the unrounded amount?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
I think it is just the formatting of that column the formula is in. Select the column and format it as Number, with 2 decimal places (or Currency if you want).

Excel Workbook
ABCDE
1New SubtotalCreditOver Due FeeColumn1HST 7%
2$365.00$5.00$10.00,,])*7%]26.60
3$500.00$70.00$5.0040.25
Calc %
 

oldeirish

New Member
Joined
Nov 24, 2014
Messages
40
Hi,

Yes, II figured out what is I did wrong. Apparently, I’m not that bright! Lol

I had the column set up for % of those combined numbers (in percentage), not 7% of the total that would give the 7% dollar amount to be added in the GST + HST Total Line.

I have to admit I want to articulate what I need the end result to be, but have a hard time conveying that. My apologies for this blunder.

On another note, I have a completely different set of questions to do with Table and Pivot Table information. It is in another post. Does anyone have any ideas of how I can accomplish these formulas and get the outcome I need? Here is beginning of original post.

http://www.mrexcel.com/forum/excel-questions/820378-help-pivot-table-data-calculation.html

Thank you so much, and my apologies for the murkiness in the question.

 

Watch MrExcel Video

Forum statistics

Threads
1,129,370
Messages
5,635,869
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top