Free Microsoft Excel 2013 Quick Reference

- Formula for subtracting a percentage
- Calculate a percentage of a percentage
- How to multiply a column by a percentage without blank cells being replaced by 0
- Calculate X Percentage Of Cost
- Sum An Array Returned By Formula
- Formula in pivot table ....
- Calculating totals in a range using IF,=,
- Attempting to count number of cells that have a date URGENT!
- How do I create a formula for multiple levels of discounts?
- Multiple team "All-Play" Won, loss, tie records
- Formula for "counting" numbers - not cells!
- Calculating a Percentage
- How do I create a formula for multiple levels of discounts?
- Multiple condition formula
- Formula for percentage ranges, can't figure out
- Multiple team "All-Play" Won, loss, tie records
- Percentage formulas?
- Formula to return percent of change in last 2 cells at end of row
- Formula to Show Percentage Amount
- Attn: baseball fans.. formula for career similarity scores?

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

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

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

help please?:

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.

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.

VB: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.=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

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: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.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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

Craig

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

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

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!

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.

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?

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!

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

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.

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.

=IF(OR(G40="Behavioral Specialist Consult",G40="Mobile Therapist",G40="Lead Clinician",G40="Licensed Clinician/New Jersey",G40="Psychologist / Psychiatrist",G40="Non Licensed Clinician",G40="Counselor"),"Not Changed",IF(P40<$Q$1,E40-S40,"Not Changed"))

THANK YOU TO ALL OF YOU WHO ARE TEACHING ME!

>= 75% add 6

65-74.9% add 3

55-64.9% add 1

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

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?

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?

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....

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.

Thanks in advance.

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.