Determine Math Signs/Operators In Cells Before Calculation
Apr 1, 2008
I have Three different combinations which are in cell A & B, These combinations are with similar signs as in A1=2- & B1 = 2- (& A1 = 2+, B1=2+). Then I have numbers with no signs which A1 =2 B1= 2. My approach is to limit to these and these combinations only and use a formula which is uniformally applied on these combination. I want A1 and B1 to take only like signs like -- or ++ or without any sign. The rest shd be ignored. IS there a way of sorting two columns which have different combinations liek -- or ++ or -+ into a area where u can apply a particular formula? Hence first i take a number and move signs on the left and then apply this
IF A7 = 2- . B7 = 3- THEN
IF(OR(RIGHT(TRIM(B7),1)="-",RIGHT(TRIM(B7),1)=" ",RIGHT(TRIM(B7),1)="+"),LEFT(TRIM(B7),LEN(TRIM(B7))-1)*3,B7*3)
View 6 Replies
ADVERTISEMENT
Aug 24, 2006
Can anyone list the formulas needed in Excel
+
_
x
divide
View 6 Replies
View Related
Oct 19, 2007
I need to create a macro that will make a cell only run its formula when an associated cell has something in it that will trigger the function.
View 2 Replies
View Related
Jul 24, 2009
Assume A1=25 and A2 = 35.
I typed, in cell A3: =A1+A2 which returns: 60.
Is there a way to present, in a single cell, instead of the formula references - something like: =25+35 as TEXT ?
(Any change in A1 and/or A2 will be reflected in the presentation of those two values
I checked some add-ins functions, like "MoreFunc" etc, and INDIRECT - but no results.
View 4 Replies
View Related
Apr 22, 2008
I am doing some data analysis and need to figure out whether a number in column B is greater than a number in column A by £X.XX (Number to be determined) - is there a function, or custom function that would let me do this?
View 2 Replies
View Related
Aug 9, 2012
How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.
My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)
Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2
If there is any way to include the month/year in G2 that would be ideal.
View 6 Replies
View Related
Dec 21, 2007
i want to divide eg. >53/>12 but i get #value! error. i have huge columns full of is so i dont wanna so 53/12 kinda thing 1000 times. whats the formula i need to use?
View 6 Replies
View Related
Mar 7, 2009
I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("c5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
But this dosen't
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("b5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.
View 9 Replies
View Related
Dec 5, 2006
If UCase(TextBox1.Value & "," & TextBox6.Value) < UCase(currsheetname)
This works most of the time, but for something like:
Smith,Bob < Smith,Jim it will give erratic results. currsheetname is not declared so is a variant I guess.
View 5 Replies
View Related
Jan 5, 2008
I am trying to write some code that will repair errors in a standard workbook. One of the errors is a blank cell that needs to be changed to "325¨¬F ¡Â T ¡Â 350¨¬F". How do I insert these symbols/operators into the VBA code?
View 6 Replies
View Related
Jul 21, 2009
Is it possible to set a range using comparison operators? ie. if a number lies within x and y then such and such happens.
View 6 Replies
View Related
Jun 26, 2006
I am trying to alter the type of operator used in one formula based on the result of an argument in a separate cell generally as follows: If Cell 1 equals something then use the ">" operator, otherwise use the "=" operator. I appreciate this sounds really simple and I'm having a major brain fade as I cannot figure out how to relate the two arguments as follows: [If(Value 1 "Operator" Value2, then do something, otherwise do something else)] where the operator is pre-defined in another formula.
View 4 Replies
View Related
Mar 26, 2008
I'm trying to figure out how to reorganize data based on boolean values in two different columns. My goal is to write something in VBA that will look to see if the boolean in the first cloumn is true then procede to check if the boolean in the next column, same row is true. If that is the case, it will take several values from the row and move them to another worksheet. If the boolean in the first column is true, but the second column is not, it will look down the rows of the second column until it finds true then move several values from rows it has passed through. Here is an example of the sample data I will be dealing with.
Date Time Duration Boolean1 Boolean2
6/22/200715:0032TRUETRUE
6/22/200718:0058TRUEFALSE
6/22/200719:0060FALSEFALSE
6/22/200720:0060FALSEFALSE
6/22/200721:0060FALSEFALSE
6/22/200722:0060FALSEFALSE
6/22/200723:001 FALSETRUE
Based on the example data my goal is to move the data in a new worksheet that would look something like this
Date Start End Duration
6/22/2007 15:28 16:00 32
6/22/2007 18:02 23:01 299
View 5 Replies
View Related
Jun 3, 2009
i am trying to make an excel sheet that does calculations to help drydock a submarine....
docking math is used and it works as follows:
6-11-7
6-11-7+
6-11-7++
6 is feet
11 is inches
7 is 1/8ths
+'s are 1/16ths
so i want to be able to have a list of set measurements like above and then have one set measurement be subtracted from the list. like
12-4-6+
13-5-4
14-2-4
and have 6-2-5+ be subtracted from all of the above and be displayed on a second page. I have slammed my head against the wall for about 3 days now and i am a excel newbie. so if i messed up somewhere or anyone could help i would greatly be appreciate it.
View 14 Replies
View Related
Mar 12, 2009
Using vba textboxes too do math .I have attached a sheet too explain, not sure if its possible. If it is I know this is the place too find out.
View 4 Replies
View Related
Mar 13, 2009
Having a real hard time with this,able to do it in normal excel, this project I'm using vba. Here is a example of what I can not do, if you can give a a formula for this I will be real Happy.
View 5 Replies
View Related
Aug 20, 2007
I am trying to create a macro that will do some basic math using J, I as referencing.
Sub Table()
Dim Temp
J = 2
I = 1
For Temp = 1 To Question
Cells(J + 35, I).Formula = (Cells(J, I + 2) + Cells(J, I + 2) * Cells(J, I + 3) - Cells(J, I + 4))
J = J + 1
Next
End Sub
how do I get the math to work without the R1C1 referencing?
View 9 Replies
View Related
Jul 7, 2006
Trying to compute annual change I've always used the formula "(b1-a1)/a1" - where b1 is the current year, a1 is the prior year. I've been asked to compute average annual change over a number of years and thought simply by using the above for each year and then taking the average of all these, I'd be ok. I recently found a formula as follows: "(y2/y1)^(1/n)-1", where y2 is current year, y1 is 1st year I have data, n = the number of changes.
View 7 Replies
View Related
Feb 6, 2008
I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.
View 5 Replies
View Related
Nov 10, 2006
If I have A1:A1000 in a column, and I know that A1000 is the sum of 3 cells within A1:A999 but not sure which, what kind of functions can I write to trace out these 3 cells?
View 8 Replies
View Related
Mar 27, 2008
Below is the code I used in order to calculate the price of an item depending on the number of items ordered, where the items are in column "I" and their corresponding prices in column "J"
The "If" and all of the "ElseIf" statements work fine except for the last "ElseIf" statement. It's not any different from the ones above it though.
Private Sub cmdCalculatePrice_Click()
Dim strDish As String
strDish = Range("B21").Value
Dim intQuantity As Integer
intQuantity = Range("C21").Value
Dim intPrice As Integer
View 4 Replies
View Related
Nov 11, 2009
i have a formula that i am trying to place in excel, however i am not sure what the function is I need to call or if there is one. the formula is G(sec Z)
View 2 Replies
View Related
Jan 15, 2006
How do I correct to make conditonal formula with number values ?
My main worksheet has at leat 24 columns and as many as 30 rows, all have
numbered values. Rows are defined names.
I want to get average values when at least 2 conditions have certain values.
I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcyc:grmcyc))
and answer given is really total of all 30,000 row values for grmcyc and not
when mapkpa values = 40 and RPM = 1000.
Also best would be if the conditions allow lets say when Mapkpa is between
40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc average
I used Sum but really want an average of grmcyc when Mapkpa and RPM meet the
conditions so I can build a results table of what the grmcyc average value
was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.
View 12 Replies
View Related
Jun 24, 2006
I have a spreadsheet acting as a check register. In looking through the over 2300 entries, I am getting "odd" rounding issues. I have attached a small piece of the worksheet to demonstrate this quirkiness. As you can see, I am adding numbers out to two decimal places (which should result in two decimal place answers). The result for 11/11/2002 has become a more complex number. And, the result for 11/14/2002 goes back to two decimal places.
I have upgraded my office suite since 2002. Could that be an issue? I prefer not to have to "force" the rounding =round(result,2) to solve the problem.
View 9 Replies
View Related
Jul 21, 2006
I have a user that wants me to calculate a value for x. The user will input the variables a and b into this equation: b/a = .0231x * EXP(.0439*x). The user insists that the solution to this problem must be arrived iteratively, honing in on the correct answer. Is that right? It looks to me that surely there is a way to solve this mathematically.
View 7 Replies
View Related
Oct 14, 2013
I need to detect 2 levels of duplication.
I have a data table that is similar to below (actual table is very large 1000+ rows) but duplicate column is empty.
I don't want to add additional data (i.e. formula to perform concatenate) to other columns as it takes a very long time even with screen updating turned off.
I want to use vba to
1) determine how many rows there are in the worksheet (this updates from an outside datasource)
2) Concatenate baseno & altno and determine if the concatenated value is duplicated
3) Within those duplicates for each baseno&altno pair determine if verno is different
4) Set "Duplicate" value for each duped baseno&altno pair where verno is different
So the end resulting table after macro runs would be
baseno
altno
verno
duplicate
[Code]....
I have been for weeks now and just can't get this to work. I can get it so that I can determine duplicates on one column but I can't seem to get it to concatenate inside the script and compare without writing back to the sheet (which is too slow and I don't want to do).
View 8 Replies
View Related
Apr 11, 2007
I'm feeling pretty inadequate at the moment ... I'm trying to determine something that (I think) would be relatively simple ... How do I determine if a single column , for example, (B:B) is blank (or null)? Alternatively, how do I determine if a range of cells in a column or columns, for example, (B1:B30) or (B1:D30) is blank (or null)?
View 2 Replies
View Related
Jun 5, 2007
The compliment of a set is those elements not in the set. Excel doesn't do compliments well.
In Automatic Filter, one can easily see the rows that match criteria, but selecting the compliment of those rows (the ones that don't match the criteria) is difficult.
Similarly, there are the range functions Intersect and Union, but no Symetric Difference.
(Def: the symetric difference of two sets, AB, is the set of those elements in A that are not in B. {1,2,3,4,5}{2,3,4,10,11} = {1,5}.)
Other than by looping through cells:
Given two ranges, aRange and bRange, how would one code for those cells that are in aRange, but not in bRange.
View 9 Replies
View Related
May 5, 2012
Let's say in cell A1 I have something like "10-25" or "4+4". I want to keep A1 just like that, but I would like to solve the arithmetic in A1, in cell B1. So B1 would need a formula that gives the answer "-15" or "8". How can I do this with large data sets?
View 7 Replies
View Related
Dec 16, 2006
I am looking for the easiest way for my worksheet to perform the following:
My 1st sheet (Sheet1) has columns A-C
In Column "A" there is a Cust # call it "P200"
In column "B" I have an invoice amount of $500.00
In column "C" -In this case, I need this field to calculate 15% of the amount in column "B"....and return the result of $75.00 (however, this 15% figure will vary based on data looked up in (Sheet2)
ie... in (Sheet2)
Column "A" will list all cust #'s that my company has - A1=P100, A2=P105, A3=P125, A4=P127, A5=P200 etc....
Column "B" has each respective discount amount for that customer by %... B1=10%, B2=10%, B3=12%, B4=20%, B5=15%
So the macro I need for (Sheet1- column C) will need to search "Sheet2" column "A" to find the "P200" out of the long list and then take the corresponding % amount in column "B" (in this case it's 15%) and use this figure in (Sheet 1) to perform the calc.
View 9 Replies
View Related
