In cell A1 is 13.00, the result of a formula.
In cell A2 is the formula =IF(A1>14.00,True,False)
The result of this formula is TRUE
In cell A3 is the formula =15-A1
The result of this formula is 2
why the formula in cell A2 would evaluate to the wrong conclusion? All cells are formated as numbers.
I need a way for tell Excel to NOT evaluate a formula. Basically, I want Excel to leave the value being displayed in the cell alone. The formula in the cell is a function that I have implemented in C++ and registered with Excel through the Register call.
View 9 Replies View RelatedI have to run an Evaluate function which is linked to an Array formula, but when I try just to put =ev(), the Array Formula which is located in another cell, does not run properly.
View 3 Replies View RelatedI have 2 sheets (book1.xls and book2.xls) book1 has over 20 sheets and has my primary data. Book2 is for a report. The report I want to only display one sheet at a time, by me typing in which sheet to access.
What I want to have:
A2 = Sheet Name
C2 = ="'C:Excelfiles[book1.xls]"&$A$2&"'!$B$1" C2 should have the value from B1 on 'Sheet Name' from the file book1.xls
however, all C2 will display is the text, I cannot get it to actually do the reference. (appears as 'C:Excelfiles[book1.xls]Sheet Name'!$B$1 )
Is there a function to say, evaluate data output from another cell?
(less complicated example)
A1=1
A2=2
A3=A1+A2
A4=3 (formula to evaluate output of A3)
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
I am trying to use an If statement and wanting to evaluate another formula.
The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6.
Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
I have the following in VBA:
Code:
ActiveCell.FormulaR1C1 = _
"=mod_a1&"" ""&mod_b1&"" ""&mod_c1&"" ""&mod_d1&"" ""&mod_e1&"" ""&mod_f1&"" ""&mod_f1&"" ""&mod_g1&"" ""&mod_h1&"" ""&TEXT(mod_i1,""m/dd/yyyy"")&"" ""&mod_j1&"" ""&TEXT(mod_k1,""h:mm:ss"")"
I only want the user to see the string, not the concatenation formula. So how do I get VBA to turn this formula into an actual string.
I have seen some functions using ByVal and Evaluate with this sort of thing but this is just different enough that I can't figure it out.
I have a spreadsheet that contains two worksheets called: MasterApplication and LocalAdmin. The Local Admin worksheet as shown below contains the following information starting on row A1:D456 ....
View 9 Replies View RelatedI've been trying to get this SumProduct formula to return anything except an error for a while now.
=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17
Using excel 2007 and I noticed some differences between how a caluation is done via PEMDAS (as I understand it) and evaluating vai excel.
The formula is just an example and doesnt relate to anything as such.
The formula is: 2*6+3-4/2-5+20/5*3+50
using [URL] the formula show the calculation as I would have calculated.
Manual Order.png
When using excel it does it in a different order
Excel Order.png
Any reason why?
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
View 2 Replies View RelatedThe formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).
IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},$O215:$R215,0))),AND(ISNUMBER(MATCH(MIN($O215:$R215)+{0,1,2,3},$O215:$R215,0)))),"C",IF(SUM(IF(FREQUENCY($O215:$R215,$O215:$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY($O215
******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [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.
I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.
View 10 Replies View RelatedI am using this formula
View 9 Replies View Related=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
I can't seem to tell if my formula isn't correct in concept, or if this is a known problem with Excel 2000?
I have written a short formula that is pretty straightforward, but is giving a different result in Excel's "Formula Editor" box (when you hit the = button) than is showing in the cell itself. The Formula Editor's result is showing as "TRUE" which is what I believe is the correct value, but the cell itself is showing the "FALSE" result of a different incorrect value.
This does almost exactlly what I want. The one problem that I have is that the active cell remains B1 as it enters the formula in the rest of column B. I need it to refer to the row that it is entering the formulas; meaning each formula entered in B2,3,4, etc. is still referencing cells A1 & D1 for sBook & a sFilePath.
Dim sBook As String
Dim sFilePath As String
Dim sLen As Long
Range("B1").Select
sBook = ActiveCell.Offset(0, -1)
sFilePath = ActiveCell.Offset(0, 2)
sLen = Range("A1").End(xlDown).Row
Range("B1").Formula = "='" & sFilePath & "[" & sBook & ".xls]Sheet1'!R1C1"
Range("B1", Cells(sLen, 2)).Formula = Range("B1").Formula
I have a formula =SUM(IF($I$4:$I$302="A",$K$4:$K$302)) works fine.
I am using the same formula referencing a different column =SUM(IF($W$4:$W$302="A",$Y$4:$Y$302))
And I get the error : "A Value used in the formula is of the wrong data type"
I have a spreadsheet, with a Match Formula, that appears not to work. It looks at cell F1, and scans through cell C2:C14, and want it to return the relevant row in Column B (My work network will not allow me to upload the file). Cell F1 is the name of the sheets within the workbook (one for every month). However my data currently only has Apr - Jun, but when I choose month May, the formula feturns Month June.
View 3 Replies View RelatedI am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
View 2 Replies View RelatedI am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is
Code:
{=FIND(($C$1:$D$1),A2)}
and $C$1:$D$1 contain REF and ATM respectively. [/CODE]
REF
ATM
203047 05AUG 08.55 OKEHAMPTON ATM
#VALUE!
CO-OP GROUP 380611 REF 191 7553375222 BCC
22
We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!
Is it possible, when user types for example "1+1" in cell A1, to calculate this sum in cell A2?
So I would have "1+1" in A1 and "2" in A2.
I cant figure out why this "evaluate" function is not working as I expected. I have number/text from $A$1 to $A$5 to try and learn how to use this function but have not had any luck. I need to learn how to use it properly so I can use a for loop to change multiple check boxes on a user form that I am trying to make.
View 8 Replies View RelatedI cannot figure it out for the life of me. When I type a formula into a cell it will not evaluate, instead it just displays the text of the formula.
When I go to evaluate the cell -- Tools | Formula Auditing | Evaluate Formula
It tells me that "the cell currently being evaluated contains a constant."
What do I need to change so that cells will evalute formulas I put into the?
Need the syntax for using a checkbox in a col. ?
If checkbox checked >> Syntax ??
If un-checked >. Syntax ??
Does it simply evaluate to val. of cell to either 'Y' or 'N' ?
I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.
I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.
I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs:
i.e.
Numbers Prefix Country
4476324125 44 UK
3354326544 33 France
9713425432 971 UAE
9143253245 91 India
1343543253 1 USA
4432412412
4924241214
7431242424
So I would need to add a column next to "Numbers" saying to which country each number belongs.
My list has a few thousand numbers and a couple of hundred prefixes.
I tried with some array formula, but cannot make it work.
Example numbers:
21130 & 21065
I want to check each number if EITHER of the two conditions is true:
1. if the third digit from the right (the hundreth place) is greater than zero;
or
2. if the second digit from the right (the tens place) is >=6.
If either is true I want to add a particular number to the original number.
My example numbers meet questions 1 & 2, respectively.
How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?
View 2 Replies View RelatedIm looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"
I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.
I have attached a test file which should clear things up