I have been able to format single cells to display negative percents (Budget to Actual hours), but I cannot copy the formatting to cells with positive percents without eliminating the format style I want. [I need to display, with the parenthesis, (13.6%)for negative results, but say, 18.6% for positive results.] When I copy the correctly formatted cell (13.6%) to another cell with a positive result, it sets the display to general formating.
As I have over 25 rows of data to compare against 62 projects and 12 programs, with each value potentially changing from one analysis to the other, I am looking for a method to automatically change the "look" of the results. I have looked at conditional formatting, but have had no indication this will do what I am looking for.
I have a percent 3.14%, that I want to show 15, 20 and 25% above and below that percent. I am showing below the results and then formula. The top calculation results in the same on both the left and right. However the -15,-20, -25% results are different. Which is correct.
3.14%
20% +/- 3.14%
0.63% or 6.28%
1.57%
2.51%
[code].....
basic math, but I don't understand why the results are different.
I have several thousand values to compare, using = Exact(A2, B2) to find exact matches and return TRUE/FALSE. I've gotten a bunch of incorrect results especially where both values equal zero.
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.
What does the (3) and (1) mean in the parentheses?
The (3) is sloppy coding instead of using the enumeration (xlUp), which you could actually read. The (1) means the first element of the range, which is wholly redundant; it already refers to a single-cell range.
I have a long list beginning either with 4 numbers or 4 numbers followed by parentheses and a single number between them. I have this formula to extract the data between parentheses:
I have created the following formula (with some help from this forum), but I am getting an error message saying I am missing a left or right parentheses.
I want to do something similar but it is looking for values between parentheses "()" and won't work with the VB suggested. I've seen some other formulas that do find the content within the parentheses but returns #VALUE! if there is not a parentheses in the field. I'd like it to copy the original value of A2 into B2 for those instances. PROBLEM: Database with First Names in A2 where some have "nicknames" entered within () and I need a clean column of values to use. "IF A2 contains () then copy (contents) into B2, else copy A2" is how I'd write in english... not sure how to do correctly in code/vb.
VB: Sub PartialText() Dim MyValue$ Dim ChangedValue$
An externally generated CSV file gives us one column which shows a location, year and then individual names in brackets, like so:
London 2013 (Firstname Lastname) Delhi 2012 (Firstname Lastname)
I'm wondering if it is possible to separate out the text before the parentheses, so that I can show "London 2012" (for example) in another cell. The number of characters changes from row to row, so using the LEFT and RIGHT functions doesn't quite do the job.
I've been playing around with GoalSeek in VBA because of this thread and found something about the syntax that I don't understand.
If I want to find out whether GoalSeek worked, I can use
Ok = Range("B1").GoalSeek(Goal:=10, ChangingCell:=Range("A1")) Debug.Print Ok This requires parentheses around the GoalSeek arguments.
If I just want to do the GoalSeek, then I need
Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1") with no parentheses. Why? The Help says that GoalSeek returns True if it works, and the proforma syntax includes parentheses, but then the only example uses the syntax without parentheses.
I tried to use the find and replace function to delete different numbers from different cells (all in one column) but the problem is that I want to delete different kind of numbers.
Let me give you an example:
Starting point:
Cell A1: Bookrunner: Barclays Capital(368.726m) Cell A2: Bookrunner: DBS(368m) Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)(367.163m) Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)(367.163m)
I want to reach here:
Cell A1: Bookrunner: Barclays Cell A2: Bookrunner: DBS Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore) Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)
(I don’t want the information in “()” like “(Singapore)” to be deleted! Only the “()” which contains numbers)
I used the “find and replace” function to delete ever single number, but I think it’s a little unprofessional to do so!
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.
I'm trying to nest if statements that also include "and" and "isblank" factors. The following formula isn't working, and I'm not sure if it's because of my use of isblank or lack/placement of parentheses.
Any formula for removing text within multiple sets of parentheses?
I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
and have it read: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.
NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.
I have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?
I've got ALOT of parentheses, 5 nested IFs, 5 ANDs, 4 ORs. The ANDs and ORs are within the IFs.
While editing the formula, I can, at times, achieve the condition where the cell references become color-coded. Or at least some of them do. But, when I get to this point, I'm usually at a spot where I can see the formula isn't right.
I'm confused. What does it mean when my text is all red when I go to edit a formula? Did I hit some limit? Again, I don't get error messages and the formula seems to work properly.
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
I have a spreadsheet that determines what percent increase over a previous quarter. The values can be negative or positive; however, I have one entry that I'm trying to divide zero by a number which results in the #DIV/0! error message. I rather have it say -1000% since that is the value I'm looking for. I now how to deal with a simple division by using an IF statement such as IF(B1,A1/B1,0), but this one is throwing me a curve.
The attached spread sheet is a quarterly percent increase over the last one. In the example, N00377 represents a machine in cell D14 and D17, where cell D17 is the last quarter, and I'm comparing it to cell D14 which should show an increase or decrease in cell F.
I have a set of data that is meant to distribute a certain number of items to different groups.
I have 10 groups, some will get more than others depending on previous usage. The problem is that I need the percentages to be in whole numbers and the total percentage needs to be 100%. I tried rounding but it doesn't work. Here is an example from one item's line.
I want to find the percent of increase over last year. If last year was 100 and this year is 500 then the percent would be 500%. However things get tricky if last year was -100 and this year is 500 or if last year was 100 and this year is -500 then it get's screwy and I'm not sure what formula to use to handle any situation.
Rate Hours =basePay plus 6% plus 7.1% total $50.00 10 $500.00 $530.00 $567.63 $567.63
What i want to have is one cell that I can Total everything. I want my spread sheet to display just rate, hours total I am having troule making the formula to display everything in the total cell
Somehow I seem to think I need to use the Value of C1 ( which is required btw) to get a percent in D1, but not sure how it would go in one complete formula in D1
I have a spreadsheet with a large list of plants. Each plant has a breakdown of colors by container size. Each cell contains a number that corresponds to a percent, e.g. a cell may contain the number 20, which would also mean this number is equal to 20%. I want to change all numbers to a percent of 100, or turn 20, for instance, into .20. There are many hundreds of numbers that I need to make a percent, so I was hoping I could do this in one fell swoop somehow. This percent number will be used in another spreadsheet for calculating on order. How do I do this?
