I am trying to find a formula to calculate the percentage of the positive numbers in a column. For example, if I have a column that has positive(green) numbers and negative(red) numbers in the cells to show over daily goal or under daily goal, and I want to at know what percentage of all the numbers are the positive ones.
View 3 Replieswhat I'm trying to do.
Background:
I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.
What I would like to accomplish:
I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.
I tried to make the question as clear as possible, if anyone needs clarification,
Originally Posted by shg
Welcome to Oz, Basca
spreadsheet with a representative sample of your data?
Games Played
Player 1 20
Player 2 7
Player 3 13
Player 4 20
Player 5 13
Player 6 13
Player 7 7
Player 8 7
Total Games Played20
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.
EX: -1 needs to be 1
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.
View 3 Replies View Relatedcalculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
How do I do summation of only positive amounts for a particular month, given that the column A is dates ranging from April to Sept while the amount column has postive and negative amounts.
For example
Column A Column B
Date Amount
1 9-Apr-09 10,000.00
2 9-Apr-09 (8,000.00)
3 30-Apr-09 153,000.00
4 12-Apr-09 (3,000.00)
5 15-May-09 20,000.00
6 14-Jun-09 (5,000.00)
7 20-May-09 (6,000.00)
I would like to see that I am continually saving on a per-day basis (as well as overtime). I allowance myself an allowance each day, but some days, I need to spend more money than that. The best solution I have to is keep a running total for myself, so that each day is affected by the preceding days. Therefore, if I spend too much, I now have a small defecit that needs to be corrected. This method keep me on track as far as saving and spending.
Question (and I copied the actual data from my spreadsheet):
Without having to manual enter the final calculations (because this is just a simple part of an overall larger, more complex spreadsheet), can I generate the values in my Value Change row, based on the information from my Running Total row?
For example, Day 1 to Day 2, I was able to incease my running total from 233 to 354 (meaning that I had saved +121 that day). Of course, this is an easy subtraction problem (Day 2 - Day 1 = Value Change). From Day 2 to Day 3, I spent way too much. The running total fell to -1198, meaning that I spent 1552 too much (that day's value change is -1552). Again, this an easy subtraction (Day 3 - Day 2 = Value Change, just like the first example). From Day 3 to Day 4, I was able to reduce my running total -1063, meaning that I was able to save 135 that day. This is my problem. Day 4 - Day 3 does not equal the value change of + 135. To get this value, the preceding formula now has to change to ABS(Day 3) - ABS(Day 4).
This is the issue. I need a formula that can work normally when the numbers are postive or when the second value is larger (in terms of absolute value). On the other hand, it needs to recognize the special cases like Day 3 - Day 4. Is this some form of a conditional function?
Running Total 233354-1198-1063-878-658-933-807-657
Percentage Change51.93%-438.42%11.27%17.40%25.06%-41.79%13.50%18.59%
Value Change121-1552135185220-275126150
I'm looking for a formula that would add only positive numbers located in the same row and in 5 different cells. These cells may contain negative numbers at times. The total after adding the 5 cells are to be displayed in a different worksheet.
View 9 Replies View RelatedI have to calculate percentages, lots of them,, have 4 scenarios
1.- positive / positive
2.- positive / negative
3.- negative / positive
4.- negative / negative
I wish to find a formula that covers all scenarios, have thousands of data to calculate
For Example, I have a list of numbers like this
3
-4
6
5
-2
Now, I need to get the sum of all positive numbers & sum of all negative numbers in different cells. Is it Possible to get using Formulas.
The Order (& also the number) of the Positive & Negative Numbers varies.
I want to find the min and max numbers within a thread of numbers that has both positive and negative values. The key is that I want the smallest amount or the largest amount based on the number and not on the positive or negative.
For example, below is my list of numbers.
7
5
2
-5
-6
7
-8
-3
I want the min formula to return 2 and the max formula to return 8.
************************************************************************>Microsoft Excel - Book2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7B8B9B10B11B12B13B14B15B16B17B18B19B20B21B22B23B24B25B26B27B28B29=ABCD1 amount Abs amtMark x when paried up 2 (43,746.73)43746.73x 3 43,746.73 43746.73x 4 43,746.73 43746.73 5 (60,579.30)60579.3 6 (60,579.30)60579.3x 7 60,579.30 60579.3x 8 (98,416.85)98416.85x 9 98,416.85 98416.85x 10 98,416.85 98416.85 11 (500,000.00)500000 12 (500,000.00)500000 13 (500,000.00)500000 14 (500,000.00)500000x 15 500,000.00 500000x 16 (700,000.00)700000 17 (700,000.00)700000 18 (700,000.00)700000x 19 (700,000.00)700000x 20 (700,000.00)700000x 21 (700,000.00)700000x 22 (700,000.00)700000x 23 (700,000.00)700000x 24 700,000.00 700000x 25 700,000.00 700000x 26 700,000.00 700000x 27 700,000.00 700000x 28 700,000.00 700000x 29 700,000.00 700000x Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Hi everyone,
I can't figure out how to mark a "x" for the paired up figures in column A as shown in the above spreadsheet using a formula(e) and/or VBA. At the moment I'm doing this manually. Imagine doing this for thousands of line... once a week
Need to sort the following in excel:
(2,000.000)(1,000.000)(500.000)(200.000)200.000 500.000 1,000.000 2,000.000
to the following:
2,000.000 (2,000.000)1,000.000 (1,000.000)500.000 (500.000)200.000 (200.000)
How can I sort like this.
IF formula.
I am trying to subtract 2 numbers, and they are 4 different combinations of positive and negative numbers.
I wrote the formula as
=IF(A5*B5>0,IF(AND(A5*B54,458 2,367 (322)FALSE(1,277)1,751 FALSE
I have a colum of positive and negative numbers I want to sum all the numbers regardless of the sign. Example
-1500
8000
-2500
4000
=16000
I can not work in another colum or covert the negative numbers in another colum then add them up.
I need last cell to read 16000. What formula do I need?
If you have a formula lets say ( sum A1-A2) and the total is negative i.e. A1 is 100, A2 is 50 would return -50, how do you get the value to show a plus sign if the value is positive? i.e. if A2 is 100 and A1 is 50, excel would simply show 50, but I'd like it to show +50. Also, if the result is 0, so both A1 and A2 are 50, how do I get Excel to display the words On Forecast in a cell?
View 6 Replies View Relatedto create with the default excel functions the following calculator. I need to calculate the maximum number of positive numbers which happen in a row and the maximum number of consecutive negative numbers. For example in the following list of numbers there are a maximum of 7 consecutive positive and a maximum of 6 consecutive negative numbers:
6 000
6 360
6 742
7 146
7 196
7 628
8 086
-4 071
7 898
-4 186
8 121
8 608
-4 562
to make a formula which will calculate the maximum length of positive and negative numbers in a row? I attached this table to the post.
I have a 3x3 array with positive numbers and zeros.I'm trying to write a macro that copies only the positive numbers in a row.
View 9 Replies View RelatedI have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values.
I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e.
=IF('Type I Or C'="C",-'Invoiced Price','Invoiced Price')
The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",-'Invoiced Price','Invoiced Price')
so it appears to be an alpha issue?
I would like to be able to enter numbers into a cell as a positive number, but have them actually display as negative in that cell.
View 2 Replies View RelatedI have a simple issue i cannot figure out how to write a formula for.
In A2 i have the number of operations.
In A4 i have the percentage of CPU usage it requires to complete those operations.
I need an output somewhere that will tell me how may operations I can get per 0.1% of CPU usage.
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.
The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
I have one debit and one credit column. I want to higlight duplicates, with positive and negative values.
Like this.
Column A
5000
1000
10000
3000
Column B
- 10000
-5000
-3000
-2000
I know how to do the conditional formating to find duplicates when there is only positive or negative numbers, but i don't know how to match both positive and negative numbers.
Any way to make a formula that would "COUNT" the number of positive values from 0 to 999 (no negatives and nothing higher than 999). Another way of saying it would be values greater than or equal to 0 but not more than 999?
For example. If you had the following numbers in a list, I would like a formula that would only count the 3 numbers that are less than 1000 but equal to or greater than 0.
$370 $1,070 $1,400 $300 $1,160 ($4,720) $1,080 $1,170 $30 $1,050 $1,150
I am looking for a formula to do the following:
In Tab 1, I have a negative number and the word "Original" next to it. In Tab 2, I have a mix of positive & negative numbers. I want all numbers that are negative to display the word "original" and all positive to display" new." How do I do that? Also, I want the opposite to work as well-- if Tab 1 has a positive number, I want all positive numbers in Tab 2 to display "original."
I have the following formula
=IF(VLOOKUP(F89,'2013 susp 2714035 Reissues'!F:F,1,FALSE)=F89,"yes",FALSE)
That is supposed to look at the tab 2714035 Reissues and find the same dollar amount. The issue Iam having is the dollar amount can be a negative or a positive amount but the formula is only picking up the number if it matches exactly negative to negative or positive to positive. formula to pick up the matching number if its a negative or a positive.
I am trying to restrict data input to a cell for only positive numbers. Currently, I have used data validation to accept decimals and numbers greater than zero. The cell is formatted for percentage with 2 decimal places. The "error message" alerts you that you have to have positive numbers only. The catch is that when the error message comes up and you use the 'retry' button, the entry is multiplied by 100.
View 9 Replies View RelatedI have a column which is populated with profits and losses from a trading account. Here is an example:
Column I
400
200
-100
-150
-50
What I want to do is average all the profits, and average all the losses so I have two numbers - the average winner and the average loser. Continuing with the example from the 5 trades above, the average winner is 300 and the average loser is -100. How can I do this in excel?
code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.
In 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.