Free Microsoft Excel 2013 Quick Reference

# Formula for subtracting a percentage Results

## Formula for subtracting a percentage

Please might I have help with a formula for the following:
Col A contains 25
Col B contains 28
Col C contains 15

I need to add 25 to 28 and from the total minus 15 as a percent, i.e. AplusBminus15%
I'm sure it's elementary but I'm grateful if anyone can help
Many thanks

## Calculate a percentage of a percentage

hi

I need to calculate a percentage of a percentage but i cannot do it in excel. Basically to be more clear I need to subtract a percentage of an amount and then subtract another percentage of the answer how to do the formula in excel. Have been trying for hours and did not manage!!!!.

Ex subtract 30% of 264 and then subtract 5% of the answer - ie 264-30% = 184.8 - 5% = 175.56

thanks

## How to multiply a column by a percentage without blank cells being replaced by 0

how do i multiply a column by a percentage without blank cells being replaced by 0 in

i have ried the following to change the percentage of the collumn, but all the blank cells atomatically turn to zero's...

Consider this scenario: Your company's product price list stored in a worksheet. Your boss informs you that, effective immediately, all prices must be increased by 5 percent.

You could create some formulas to do the math, and then convert the formulas to values, and then copy and paste the new prices over the old prices. Or, you could use this more efficient method:

1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.
2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.
3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.
4. Choose the Multiply option and click OK.

Voilŕ! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.

Note that the Paste Special dialog box lets you carry out other mathematical operations--like add and subtract, for instance--so you can use this technique for a variety of other calculations.

## Calculate X Percentage Of Cost

I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance.
Assume \$100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from \$100 I get \$90, however 10% of \$90 is \$9 which equals \$99. Any assistance is greatly appreciated.

## Sum An Array Returned By Formula

I am using a formula in a gradebook I have created to divide a student's total points by the total points possible to get a percentage. Here is the formula:
```
VB:
=SUM(OFFSET(TotalRange1_1st,ROW()-4,0))/MAX(TotalRange1_1st)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
I use MAX with TotalRange1_1st (the named range for the points possible of all assignments) to get the total points possible then divide it by the sum of the offset of the row that the formula is located in minus 4 to locate the total points of that particular student.

Rather than returning the max of TotalRange1_1st, I would like to sum only the cells whose corresponding cells (in the offset of the range) are blank. I will then take that total and subtract from TotalRange1_1st and divide it by the students's total points to get a new percentage based only on the assignments he or she has completed.

I tried creating a udf to do this in vba but, I keep getting a #NAME? error. Here is my udf:
```
VB:

Dim score As Range
Dim column_count As Integer
Dim possible_total  As Integer
Dim possible_value As Integer
column_count = 1
'step through the score_range and look for the possible_value
For Each score In score_range
If score = "" Then
'get the possible value
possible_value = WorksheetFunction.Index(possible_range, 0, column_count)
possible_total = possible_total + possible_value
End If
column_count = column_count + 1
Next score

NOTFACTORED = possible_total

End Function

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
I either need to figure out why the udf isn't working or, preferably, use a formula included in Excel. I attached a file if anyone would like to check it out.

Thanks,

Craig

## Formula in pivot table ....

Hi there

Hope someone can help with this problem, which often make my pivot tables look not nice.
Pls have a look in the attached file - in sheet 1 are some data which form basis for the pivot table i sheet 2.
In the pivot table I have created a third column (variance) which subtracts year 2005 from year 2006.
The problem is that in "Area A" the "product 3" is not sold, but the pivot table inserts the "product 3" anyway (with the calculated variance = 0, of course).
Now, when I have a very big pivot table with hundreds of data lines, then this phenomenon creates additional hundreds of lines with useless lines.

Question is, can anyone tell me how I avoid the pivot table make the line "product 3" in "area A" with variance=0?

And while we're at it: If I want the variance calculated as a percentage, then I just change the formula of course, but how do I format the "variance" column as percentage, while keeping the other 2 columns in absolute numbers?

I would be so great if someone can solve these 2 problems.

BR and have a good day.

Dich Stoffer, Denmark

## Calculating totals in a range using IF,=,

I'm still learning Excel, so I apologize for asking anything obvious. I also don't know how to ask the question properly, so I apologize if the answer has already been posted somewhere and I don't see it.

I have to set up several spreadsheets for a new business. But one of the spreadsheets requires that I be able to use the cells from specific columns if the contents are => or =< certain amounts. For instance:

I need to calculate the total fee on a transaction based on a sliding scale of percentages. From \$1.00 to \$25.00 I need 5.25%. I can do that much.

If the amount is greater than \$25.00, then I need to calculate first up to the \$25.00. I subtract the 5.25% from the first \$25.00, then calculate 2.75% of the remainder, up to \$1000.00. Again, I can do these as separate calculations.

If the amount is greater than \$1000.01, then I need to first subtract 5.25% of the first \$25.00, then subtract 2.75% of the remainder up to \$1000.00, then calculate 1.50% of anything over \$1000.00. Again, if I do it separately, I can manage.

However, I need to be able to enter a formula for each column requiring one of these totals, and then I can simply enter the transaction amount in the first column and have the adjusted balances calculated for me. Does any of this make sense?

Example:

I have an amount of \$25.00. I calculate 5.25%. Subtract that percentage total from the \$25.00. I have my FINAL FEE and FINAL NET.

I have an amount of \$500.00. I calculate 5.25% of the first \$25.00. I subtract that percentage total from the first \$25.00. I have my FINAL FEE SUBTOTAL and my FINAL NET SUBTOTAL. Than I calculate 2.75% of the remaining \$475.00. Subtract that percentage. I now have a second FEE SUBTOTAL and second NET SUBTOTAL. I add the two sets of subtotals for my FINAL FEES and FINAL NET.

I have an amount of \$1400.00. I do all of the above, only the last calculation is 1.5% of \$400 and I add all the fees together and all the nets together for my final totals.

So is there a formula that will do something like "determine X% of \$X if \$X is => \$25.01 but

## Attempting to count number of cells that have a date URGENT!

=COUNT(K4:K554)/ROWS(K4:K554)
This is my current formula that would return a percentage (cell formatted). I have linked the column with another notebook that mistakingly returns a value of 1/0 for emtpy cells formatted to display dates.
1) How can I get it to return an empty cell instead of attempting to fill it in?
2) If not, how can I get this formula (listed above) to subtract those supposed-to-be empty cells?
A sincere Thanks!

## How do I create a formula for multiple levels of discounts?

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria a if material in square inches (cell D18) is
between 288 and 288 square inches, then a disount of 10% is subtracted from
the subtotal. If material in cell D18 is greater than 488 square inches,
then a disount of 15% is subtracted from the subtotal. I am a novice at
this, so I need some help from the experts.

## Multiple team "All-Play" Won, loss, tie records

Forgive me if this is a beginners question... I did some searching and can't
seem to solve this problem. Any help would be appreciated.

I have 96 teams who play in 8 12-team leagues. I am trying to come up with a
formula for determining a weekly W/L/T percentage as if they were all playing
eachother each week.

e.g.
Team A = 37 points
Team B = 35
Team C = 33
Team D = 33
Team E = 30

Where
Team A's record would be 4-0-0
Team B = 3-1-0
Teams C & D = 1-2-1
Team E = 0-4-0

I have tried using a Rank function, then subtracting out the total games
played, but it doesn't account for ties.
And I currently have separate columns for W, L, & T's.
Any help?

## Formula for "counting" numbers - not cells!

I am trying to create a formula that will â€ścountâ€ť a set of given numbers. I
want to hand out numbered raffle tickets and have the ability to track how
many are given to each representative of my group. The issue is the numbers
printed on the tickets are large and each member receives various amounts of
tickets.

Here is an example

I give Rep A ticket numbers 73267 through 73356, which are 90 tickets. If I
use the subtract function â€“ it equals 89! I am one off!

Unfortunately, it is complicated. The collective â€śTheyâ€ť want to be able to
track the tickets, who sold what tickets, what tickets were not sold,
percentage of tickets sold by a Rep that won and lostâ€¦. I could go on and
onâ€¦.

FUN, I knowâ€¦..

Any help would be appreciated!

## Calculating a Percentage

Hi there,

I'm trying to separate GST from a total amount and am wondering if anyone
has a formula that can help me do this.

For example, I have a total of \$901.07 and would like to know how much is
the base amount and how much is the GST. I've tried to subtract 7% but that
doesn't work out correctly.

Does anyone have any suggestion???

Thanks very much

## How do I create a formula for multiple levels of discounts?

I am trying to create a pricing sheet that will include a cell for discount
percentages. The criteria are: if material in square inches (cell D18) is
between 288 and 288 square inches, then a disount of 10% is subtracted from
the subtotal. If material in cell D18 is greater than 488 square inches,
then a disount of 15% is subtracted from the subtotal. I am a novice at
this, so I need some help from the experts.

## Multiple condition formula

I hope the title is appropriate though It doesn't fully express what I need. I need a formula that is basically an IF OR UNLESS statement. When you type that into Google it has nothing to do with Excel.

The formula below says is that if G40 = any of the following then the cell should fill with "Not Changed" but if P40 is less than Q1 which is a Gross profit percentage then subtract S40 from E40 and populate the cell with the answer. E40 = a billing rate and S40 is that billing rate times Q1. The formula is either calculating a new pay rate for me or not changing the old rate and placing "Not Changed" in the field. The problem now is that I also need the formula to take into consideration another job title (Column G) + a company name (Column M) and instead of using the percentage used in Q1 use a percentage in R1.

Is this even possible. Please let me know if this is not understood.

THANK YOU TO ALL OF YOU WHO ARE TEACHING ME!

## Formula for percentage ranges, can't figure out

Hi everyone! I am a excel newbie and I am ready to pull my hair out! Having trouble with this formula I want to reference the percentage in cell G2. Here is the formula,

45-54.9% none
35-44.9% subtract 1
25-34.9% subtract 3
Less then 25% subtract 6

Could someone help me with this, I keep getting errors with everything I enter after IF(G2>=75,6,IF(G2?

Thanks

## Multiple team "All-Play" Won, loss, tie records

Forgive me if this is a beginners question... I did some searching and can't
seem to solve this problem. Any help would be appreciated.

I have 96 teams who play in 8 12-team leagues. I am trying to come up with a
formula for determining a weekly W/L/T percentage as if they were all playing
eachother each week.

e.g.
Team A = 37 points
Team B = 35
Team C = 33
Team D = 33
Team E = 30

Where
Team A's record would be 4-0-0
Team B = 3-1-0
Teams C & D = 1-2-1
Team E = 0-4-0

I have tried using a Rank function, then subtracting out the total games
played, but it doesn't account for ties.
And I currently have separate columns for W, L, & T's.
Any help?

## Percentage formulas?

Im using excel 2007, Ive created a table for a game I play which adds up totals etc, but what i'm not sure how / if it can be done is take a percentage away from a total figure??? but i want it so:

If the totals in the tables add up to say 100 i want to be able to put a box in which for example would have number 1 in, and if this box has number 1 then the tables would subtract 4% from the totals, but if it has a figure of nothing or 0 then it would do the standard calculations.

Is this possible?

## Formula to return percent of change in last 2 cells at end of row

If I have a list of data from b3 to b8, for example, what formula will subtract b8 from b7 and then calculate the percentage of change and display the
result in b2? (b1 will have a text title) Now here's the tricky part... I will constantly be adding new data and inserting it at the end of the row and the formula must adapt to always perform the calculation on the last 2 cells in the row.

I understand the basic concept of ((b8-b7)/b8)*100 to get the percentage. What I can't do is figure out how to use functions to make sure that this operation is always performed on the last 2 cells in the row no matter how many times I add data.

Example.. When I add a value to b9, the formula must now return results for b8 and b9. When I add to b10, it must work on b9 and b10, etc....

## Formula to Show Percentage Amount

Hi All.

I know how to calculate percentages. I know how to do a bit of formula stuff in Excel, but I have a simple spreadsheet which I could complicate (read: Change the entire look of the invoice) to get the result I'm looking for, but I'm hoping there's a simple formula to place in the one cell that will do it and I keep effing it up. :/

Here's the simple setup:

Column A has descriptions of items (manually typed in)
Column B has Prices of items (manually typed in)

B15 is SUM(b1:b14)

Now, A14/B14 is reserved for a discount on all items (Not being selective here.. it's all or nothing).

A14 = 10% Discount

I want B14 to show the actual amount being deducted (I.E. A negative amount) so that B15 still works correctly, and if I add an item later (To say A9/B9) B14 will auto calculate the new amount off.

Since I know how to work percentages, I've just been manually entering the discount, but I made a mistake where I forgot to recalculate the discount when I added an item. SO I've tried a few different things but it just never works out right for me. I'm missing something.

Here's a simplistic look:

A1 Widget B1 25
A2 Wudget B2 20

A14 10% Discount B14 (4.50)
A15 Total B15 40.50 (SUM B1:B14)

So, I need a formula for B14 that will SUM b1:b13, calculate the percentage off, and display the actual money being subtracted.

## Attn: baseball fans.. formula for career similarity scores?

This is the definition of similarity scores. I'm trying to come up with a function that i could plug into excel to come up with the similarity score. Any ideas? I got about as far as getting the absolute number in the difference.

From B-Ref
Similarity Scores

Similarity scores are not my concept. Bill James introduced them nearly 15 years ago, and I lifted his methodology from his book The Politics of Glory (p. 86-106). To compare one player to another, start at 1000 points and then you subtract points based on the statistical differences of each player.
Batters

* One point for each difference of 20 games played.
* One point for each difference of 75 at bats.
* One point for each difference of 10 runs scored.
* One point for each difference of 15 hits.
* One point for each difference of 5 doubles.
* One point for each difference of 4 triples.
* One point for each difference of 2 home runs.
* One point for each difference of 10 RBI.
* One point for each difference of 25 walks.
* One point for each difference of 150 strikeouts.
* One point for each difference of 20 stolen bases.
* One point for each difference of .001 in batting average.
* One point for each difference of .002 in slugging percentage.

For example. Here are two players information. My goal is to have a career similarity score stat compiled just to the right.
GM AB R H 2b 3b HR RBI BB K SB AVG SLG
A 1746 6767 1358 2067 364 26 464 1347 820 1404 241 .305 .573
B 1535 5572 1067 1760 381 14 390 1270 874 1230 33 .316 .599

Thanks in advance for you assistance.