Percentage Formula With Negatives
Jan 28, 2009
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.
View 9 Replies
ADVERTISEMENT
Dec 8, 2006
i'm looking for a formula that gives the greatest number of a range
problem is that there are positive and negative numbers in the range
but I only want to look at the value (+/- signs are not important)
View 4 Replies
View Related
Jan 21, 2010
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
View 2 Replies
View Related
Jan 30, 2014
I am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx‎
View 1 Replies
View Related
Apr 15, 2009
I have a column with both positive and negative numbers, is there a macro or a bit of code that will convert all negative numbers in a selected column to zeroes?
View 3 Replies
View Related
Apr 30, 2009
I'm trying (with little success) to match some negatives and positives, but the formulas that I found on line keep on picking up all matches (just like a VLOOKUP formula)
23
23
-23
The formula would match the -23 with the 2 positive 23s. This formula goes in Column O
View 4 Replies
View Related
Jul 19, 2006
Long Version: This should be a fairly simple question, with a solution involving unchecking a box somewhere, but I can't find it. Whenever I enter a number in parentheses (for example: "(187.13)") into a cell Excel automatically converts that number to a negative--- "(187.13)" becomes "-187.13". I've been using the apostrophe option as a workaround (i.e., '(187.13)) to store it as text instead of a number, but this is becoming a hassle because it recognizes that (187.13) is a number and alerts me with an error message of numbers being stored as text.
Short Version: Basically, I want it to stop automatically making "(187.13)" become "-187.13" but I don't know where the setting is.
View 7 Replies
View Related
Apr 7, 2009
I'm trying to SUM a column, but it has a negative number in it. I want to sum the column but ignore the negative number.
I.E.
5
46
12
-15
3
so the total would be 66. How is this possible or would i have to use another formula instead of SUM.
View 5 Replies
View Related
Aug 23, 2007
Let us say that this is a certain cash flow
Year - Cash flow
2007 - (200)
2008 - (100)
2009 - 50
2010 - 100
2011 - 200
2012 - (100)
2013 - (25)
2014 - 50
2015 - 300
For year 2007,2008,2012 and 2013, we have negative cash.
I want to compute for the investment cost, which I define to be the total accumulated cost before first positive cash. First positive cash occurs at 2009, so I want to sum 2007 and 2008, However, 2012 and 2013 also reports negative cash, so in the SUM formula, their values will be included.
How should I write the formula in order to sum all the negative cash before positive cash, disregarding all the other cash flows thereafter (be it positive or negative)?
View 9 Replies
View Related
Jan 25, 2010
I have the following code in a user form, attached to a data field that the user should fill. This code should alert the user if he enters a negative number i.s.o. a positive (and vise versa). Due language related issues, it is quite possible that the user will enter (by mistake) a negative sign but it will be at the end (500-) and the user will not notice this mistake. For some reason, my code does not pick up on this, and does not pop up the error message. Therefore, I decided that I need to check if the value entered by the user also contains a “-“ at the end of the string. This, I believe, will take care of the problem.
In excel, I would simply enter a formula with Mid and Len (to check if the last character is “-“) , but I don’t know how to implement it in my code.
Sub txt_sum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Verify that a negative sign was added for expenses
With Me.txt_sum
Select Case cbo_act
Case "Expense",
If Me.txt_sum.Value > 0 Then
MsgBox "Number must be negative"
End If
Case "Income"
If Me.txt_sum.Value < 0 Then
MsgBox "Number must be positive"
End If
End Select
End With
End Sub
View 9 Replies
View Related
Feb 29, 2008
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 Related
Dec 15, 2009
I use keyboard commands whenever possible. They are much quicker than the mouse.
For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.
I like to see negatives in red with parenthesis.
View 5 Replies
View Related
Aug 22, 2006
This is my formula and it returns: -55 bps.
=[ASK.xls]QTR!$AT$81&" "&"bps"
However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.
View 7 Replies
View Related
Mar 6, 2008
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.
View 11 Replies
View Related
May 7, 2009
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 Related
Mar 26, 2008
how 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?
View 9 Replies
View Related
Feb 11, 2014
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
View 2 Replies
View Related
Nov 16, 2005
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?
View 14 Replies
View Related
Aug 28, 2002
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.
View 4 Replies
View Related
Dec 20, 2013
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.
View 4 Replies
View Related
Jun 1, 2006
Formula request
I need to ( in currency)
Example
Col B = $2.06 . Col C = $3.42
I need to in Col D to calc the difference between B and C less 25%.
So in the above example D should = $2.90
View 6 Replies
View Related
Nov 15, 2006
I have in row D a list of numbers and in cell F a list of salaries. I need a forumla to tell me what percentage D1 is of F1 etc.
IE the formula will be something along the lines of D1 * 12 / F1 * 100
View 5 Replies
View Related
Feb 5, 2007
Need a formula for level report. cell c-1 contains percentage for shop production. 100% 105% etc, etc. 100-109=level 1, 109.1-119=level2, and 119.1 and above=level 3. I am in need for a cell to translate the percentage to type 1,2, or 3 accordingly. cell c-1 will recalculate when work production figures change.
View 3 Replies
View Related
Mar 14, 2009
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:
For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.
What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.
Therefore, I came to the conclusion that:
0.25% x 80 = 20 hours
0.50% x 80 = 40 hours
0.75% x 80 = 60 hours
Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
View 9 Replies
View Related
Dec 19, 2013
The attached spreadsheet is a sample of a master sheet I need to maintain. Keep in mind that the spreadsheet has hundreds of names on it and 10 sheets in the workbook. What name appears most in the "person who called" column, then I need to know out of all the people who called, what percentage of calls he made....
I have to do this on each sheet, so if it is possible to have it work for all the sheets.
View 6 Replies
View Related
Mar 24, 2012
In cell B77 i have a percentage formulated from other information in the sheet. I want cell B78 to show a word based on the percentage score.
As follows:
0%-25%- Poor
25%-50% - Competent
50%-75% - Professional
75%-100% - Expert
View 4 Replies
View Related
Jul 24, 2012
I have a set number in col A for example 100. Col B is variable value. And Col C is where I need the formula.
If for example Col B is within 5% of Col A return value of "G" in Col C.
If Col B is within 20% of Col A return value of "A" in Col C.
If Col B is under or over 20% ( example if Col B is 79 or 121 ) of Col A return value of "R" in Col C.
View 4 Replies
View Related
Jun 10, 2013
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?
View 2 Replies
View Related
Jun 27, 2013
=IF(AND(G9=0,H9=0),"",IF(AND(G9=0),1,H9-G9/G9))
this works great execot for the percentag is wrong when h9 and g9 are populated
View 4 Replies
View Related
Oct 1, 2013
here is the formula I am using. =IF(A5 < =Schedule!$K$9,100%,0%) We only track complete and not complete....so my formula above just comes out to 100% or 0%. What I am wandering is if I can add a modifier so that it can be adjusted w/o just over writing the formula. Ok so my vision is, the formula above is in cell B5.....next to it in cell C5 I would like a modifier so that if that amount should really be 85%. I can type 85% in C5 and it will change B5 to 85%. So I guess the formula would be more like =IF((A5 < =Schedule!$K$9,100%,0%),or = C5))
View 2 Replies
View Related
