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