I need to make a formula to extract GST from a total amount, when the total amount also has PST (like state tax) in it.
GST is 7%
This is how I would figure it out manually
$100/1.07=93.46
$100-93.46=6.54
$6.54 is the number I'm trying to figure out how to get to with an excel formula.
I am looking for a formula that looks across and row or column and makes sure that a max of 100% or dollar value is not reached. Is there any solution that would allow for flexibility. Below would be the wrong result.
30% 20%25% 10%30% 115%
600,000 200,000 250,000 100,000 x 1,150,000
Formula to calculate the YTD % ?
If A1= YTD 100% and B1, C1 ,D1 etc.. do represent the month of Jan, Feb, Mar...etc
What do I have to use for A1 to auto-cal Jan - Dec?
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies View Relatedfor example:
Period Revenue 5% 10% 15% 20% -5% -10% -15% -20%
$72,003.33 $75,603.50 $79,203.67 $82,803.83 $86,404.00 $68,403.17
I have created an old fashioned pivot table. I would like to have it show percentage of subtotal and show percentage of Grand Total, to 3 decimal places. See REPORT tab. In column D of attachment, I have entered in text what I would like to see in this column for INCOME, EXPENSE, AND SPENDING pivot table.
View 2 Replies View RelatedI have a table representing the usage of several thousand product in a market. Each row represents a product and one column indicates the frequency of use. I'd like to find out how many products represent the Nth percentage of the whole, when ranked from most used to least.
Shorter, if I want to know the Top 25%, how many products equate to the top 25% of all product frequency? ....
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View 4 Replies View RelatedIn the attched sheet, I am wanting to draw data from the worksheet "On-Off" Array $AI$14:$AJ$91 into column AA, while at the same time choosing the closest match to the time. I have managed to get the closest matching time into column AC.
What I am having trouble with is now calculating the number of staff on that break time (column Z) against the number of staff rostered on at that stage "On-Off" (column AJ). I thought it would be a simple division and format as a percentage, however something in it using the extract from the "On-Off" sheet is stopping me, and I cannot figure it out.
how to remove the percentage of row total in a pivot table. I only want the sum of the row total but the percentage of row column always say 100% and I want it to be removed.
View 3 Replies View RelatedFormula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W-2. The problem is that the 'Total W-2' column then deducts these taxes and pension.
Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.
View 4 Replies View Related when you open up the file please ignore everything above the blue bar. That was the old data which was arranged wrong. I have actually figured a lot of this out but i am stuck. The graph is set up so when you click on the drop down menu (F26) it changes the graph to the relevant data. Now the bottom graph is currently graphing the Months Expense1 as a total of all Expense1. So January Expense1 was $100 so its 26% of all Expense1. and when you click on the drop down button you can changed the data to Expense2 and the graph changes. YAY ok thats cool. However, thats not what i want. I want the pie chart to be graphing the expenses as a percentage of total Expenses. So the drop down menu would be of the months instead of the expenses.
I have changed that with relative ease, but I cant get the data to graph how i want it to.
I have been using OFFSET() formula and the define name manager to set up the previous graph. You can easily look at the formulas i have used instead of me trying to explain everything. Please help. This is the test bed for a budgeting spread sheet I am working on.
if you can help me out that would be great.. Please try not to use Macros because i dont understand them all that well and I need to take what is done in this spreadsheet and learn from it and change it so it works when are thre 10 expenses.
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell.
I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
i'm trying to crack what should surely be a simple problem. Attached is my spreadsheet.
As you can see I have 3 columns of detail, with totals spread over a number of months.
I want to be able to display the percentage of the Resource total for each other grouping.
For example, in my spreadsheet you can see resource Mick Arber at the top. I want to be able to see each of his rows as a percentage of HIS total.
I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category.
Here is an example of what I'm trying to accomplish:
A1 = 2 (Total audits)
B1 = 07/31/2009 (Date of first audit)
C1 = 3 (Rating of first category)
D1 = 1 (Rating of second category)
E1 = 2 (Rating of third category)
F1 = 08/03/2009 (Date of second audit)
G1 = 0 (Rating of first category)
H1 = 2 (Rating of second category)
I1 = 2 (Rating of third category)
The percentage of audits within the first category that have a rating of 3 would be 50%.
The percentage of audits within the second category that have a rating of 3 would be 0%.
The percentage of audits within the third category that have a rating of 2 would be 100%.
how to make a % of running total in pivot tables in excel 2007. the running total in is only available not the "%".
View 1 Replies View RelatedI have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies View RelatedI have a spreadsheet with 4 columns named:
type
date received
time received
number received
Is it possible to use vbscript and extract the total number received for the various scenarios:
all numbers received
numbers received before specific date
numbers received on specific date before specific time
numbers received after specific date
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies View RelatedI have medium size sets of data. They have different properties including alpha, integer, date, etc..
I would like to set up a macro to search through the data dump based on criteria asked of me specific to a row and return a random list of entire rows that is approximately 10% of total rows where the criteria matches up with what I have specified.
Site
Building
Room
Tag
100-Emerson Court
A
A101
SS102058
[code].....
So if I choose the site to be "100-Emerson_Court" and there are 400 rows with "100-Emerson_Court" in column A, in a separate sheet called "audit", it will transpose a random 10% or 40 of the rows.
Is there a formula that I may use to keep track of the YTD percentage of attendance of volunteer workers over the course of a year? I have approx 200 workers meeting once a week. Their names are listed in column "A" from A-4 to A-204. Starting in column "E" is the date of the weekday for attendance.
For Example: E-1 is dated Jan. 7, 2008. F-1 is dated Jan. 14, 08 etc. I place a one (1) in the corresponding cell to show that they attended. The formula I need would start in cell D-4 and be dragged down to D-204.
There also must be a way to factor in the weeks which we are to meet that must be cancelled due to weather etc. The worker should not be penalized due to our cancellation.
I have a simple spread sheet where i need a formula to work out a percentage. eg total cases 35246 number of errors 3. what percentage is the 3 of the total cases and what is the formula required to reflect the answer.
View 3 Replies View Relatedhow to construct a formula that would give the amount of deposit needed initially that would equal, after an immediate percentage reduction, a certain amount.
For example, if the initial deposit was immediately decreased by 2.9 percent and 30 cents (as in a PayPal transaction), what would that initial deposit have to be to equal exactly 50 dollars after being reduced?
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers
'chosen' at random by 500 unique people.
I have another row of numbers representing the 'frequency' that the
aforementioned number was chosen in cells A2 to Z2.
Example:
1 2 3 4 5 6 7 8 9 10 11 ...
10 4 16 9 23 20 61 13 18 43 17 ...
Is there a formula that I could use to calculate the percentage (0%-100%)
that each number was chosen?
I need to write a formula to minus a percentage of a total.
for example I want to take away 20% of the total of £8.75.
I need a correct formula to calculate percentage...mine is not correct
Cell J18 will hold the formula.Range C18:I18 will have values.
C18 will have a value for today,D18 will get a value tomorrow,E18 will get a value 1 day after tomorrow etc..etc..
J18 formula - =sum(C18:I18)/7
If I only has a value in J18 then it will divide that single number by 7.......can a formula only divide by entries in cells....if I have 1 entry then it divides by 1,if I have 4 entries then it divides by 4 etc..etc until thee is 7 entries.
I have a question regarding calculating percentage differences, here is how our formula works, but I've done a little research regarding using negative number for percentage calculations and am not sure...
For sample:
this year (TY) we had net profit of $200 million
last year (LY) we had net loss of -$32 million
our formula works kinda like this:
=IF(B49="","-",IF(B49=0,0,D49/B49-1))
but if, the comparing numbers is negative should I convert that to an absolute number using ABS ? what if both numbers are negative ?
The way I understand the current formula is like this:
if B49 is blank, then insert a dash, otherwise IF B49 is 0, then 0 (since we can't divide by 0), otherwise do the formula.