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
 

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
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
42,312
Office Version
365
Platform
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
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.

 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top