Formula Returns Wrong Value
Feb 27, 2010
The 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.
View 9 Replies
ADVERTISEMENT
Jul 16, 2009
I am using this formula
View 9 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
Mar 27, 2007
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
View 9 Replies
View Related
Jan 20, 2014
=IF(AND(Weekly!$E4<>Admin!$E$2,Weekly!$E4<>Admin!$E$3),
COUNTIFS('Issue Log'!$A$2:$A$50,$A4,'Issue Log'!$B$2:$B$50,$B4,'Issue Log'!$C$2:$C$50,'Issue Log'!$C2,'Issue Log'!$D$2:$D$50,"<>"&"Completed",'Issue Log'!$D$2:$D$50,"<>"&"",'Issue Log'!$D$2:$D$50,"<>"&"Cancelled"),0)
WEEKLY
ResourceProjectContact ActivityStatusEst. TimeAct. Time # of Issues
Elaine W.PIPBA - Bonnie SchaferTESTIn Planning1
ISSUE LOG
ResourceProjectActivityIssue Status
Elaine W.PIPTESTCompleted
[code]....
View 3 Replies
View Related
Nov 28, 2007
I'd like to add items on a UserForm1 ComboBoxes in the correct order but I can't. That's because with the code below, in ComboBox6P1 the items are added with mixed order:
For Each thing In .Range("AC6", .Range("AC65536").End(xlUp)).SpecialCells(xlCellTypeConstants)
If Trim(ComboBox4P1.Text) = thing.Value Then
For Each aNew In .Range("AD6", .Range("AD65536").End(xlToRight)).SpecialCells(xlCellTypeConstants)
If aNew.Row = thing.Row Then
ComboBox6P1.AddItem aNew.Value
End If
Next
Exit For
End If
Next
I should add items in ComboBox6P1 in order -> AD6, AE6, AF6, AG6, AH6, the rest are empty cells. Instead of it adds AF6, AG6, AH6, AD6, AE6. When I change .SpecialCells(xlCellTypeConstants) to .SpecialCells(xlCellTypeVisible ) it adds it in the correct order but with another 200 emtpy cells in the ComboBox6P1. The format of the cells is General.
View 9 Replies
View Related
Apr 24, 2008
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
View 9 Replies
View Related
May 16, 2014
OK, I have a huge SS with lots of named ranges. Many of which are dynamically assigned lists.
All of the existing ones work fine, but when try to add a new named range, it returns the cells from a previously named range. (Always the same old one.)
example:
A range (one of many) is named "Shift_List" and is defined using
=OFFSET(info!$C$3, 0, 0, COUNTA(info!$C$3:$C$2000),1)
New range is created named "PN_List" and is defines using
=OFFSET(info!AA$3, 0, 0, COUNTA(info!AA$3:AA$2000),1)
When I create a Data Validation List or otherwise use "Shift_List" as the source it works fine.
However if I do the same thing and refer to "PN_List", it returns the items from "Shift_List"
Any new named range returns the Shift_List cells, although older ones still work correctly.
View 8 Replies
View Related
Jan 29, 2014
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 Related
May 1, 2014
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 Related
Nov 25, 2007
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.
View 9 Replies
View Related
Nov 21, 2008
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.
View 9 Replies
View Related
Aug 16, 2006
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
View 4 Replies
View Related
Jul 27, 2012
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"
View 2 Replies
View Related
Nov 1, 2006
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 Related
Jul 17, 2013
I 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 Related
Jun 30, 2013
I 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!
View 9 Replies
View Related
Feb 2, 2010
when i use a simple sum it only returns 0 but if I use the same cells and manually add them ie A1+A2+A3 and so on it give me the answer ? As there are over 500 cells it would be a pain to type them.
View 9 Replies
View Related
Jul 26, 2006
I have a workbook that is taking the avarage percentage of efficency over a possible of 7 days. If there were only 5 days of production it would average only 5 days. It pulls info from a tab that is call "Datalink" which is just that. As we have started our new fiscal year the person who uses this came to me with this problem
1) GrpIV on C15 is returning # DIV/0! and we need it to return 0. See the attachment.
View 6 Replies
View Related
Nov 5, 2006
I have always used unique ways to randomly make selections in my life, and was thrilled to learn that Excel could pick a name(not just a number) from a list. I've used the =INDEX($A:$A,RANDBETWEEN(1, COUNTA($A:$A)),1) function and entered items into Column A (or another if needed, and updated by trial and error accordingly). It worked very well for me for a long time and then stopped working. In multiple documents, with multiple lists (not connecting to each other), all of a sudden every random item started giving the #NAME error.
View 5 Replies
View Related
May 27, 2014
I need a formula that can return the value from a cell in the same row it searches. The row is unsorted and has blank cells. Seacchs row A12:X12 returns the text in C4 (data validation List) in A19 on a different sheet.
View 6 Replies
View Related
Feb 5, 2010
The results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong.
View 7 Replies
View Related
Oct 21, 2008
How to make vlookup work right? I have tried it once and it worked but I can't get it ot work again.
I changed the format to text for the data I using and the data I looking in to see if that would fix the ref# or N/a error.
Is the formatting one possible issue?
I am slow to vlookup but I used the formula as
1. the lookup value is the serial #
2. the table_array is the entire worksheet that I am searching in - or should I just use the column that hold the values I am looking for?
3. the column_index_num is ??
4. [range_lookup] is FALSE.
View 11 Replies
View Related
Jan 3, 2008
I have a formula that looks like this:
=IF(ISERROR((C101/G119)*G104)," ",((C101/G119)*G104))
There are about 15 cells with formulas similar to this. They are calculating a value for a row and at the end all of them should add up should equal C101.
The problem is the value that formula returns has to be two digits and I know it almost never is. But it ALWAYS equils C101. However because it only shows two digits somtimes if you manulally add up the value that you see it does not its off by +/-.01.
I need to fix it so that it does equal C101 AND if you were to add up the digits (with 2 decimal places) it will also equil C101.
View 12 Replies
View Related
Apr 29, 2009
I placed this formula in cell A1 in workbook number 2which is suppose to grab what ever name is in cell A4 of workbook 1 However if the cell is blank it is returning a "0"I want it to be blank if there is no name in the cell?
View 4 Replies
View Related
Jun 30, 2008
=IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))/SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$J$2:$J$19959*4.33))))
For some reason it is returning #VALUE! in some cells. I think its something to do with the last part of the formula where it is bold.
The formula as you can see looks at data and matches relevant data to return specific depending on what has been matched. Now at the end it is matching the correct data and return that value however i want to divide the value by another (another value*4.33).
View 9 Replies
View Related
Sep 21, 2008
I need a dynamic formula in A1 that returns min value in column B in a range of the last input and 15 cells back.
Then I need another dynamic formula in B1 that returns max value in column B in the same range.
View 9 Replies
View Related
Oct 2, 2008
Can an if statement return a formula (e.g. =IF(A1=1,C1+D1,0))
View 9 Replies
View Related
May 20, 2006
Some reason my sumproduct formula returns #Value!
=SUMPRODUCT((Data_2005!$A$2:$A$170=A$2)*(Data_2005!$F$2:$F$170=$A5)*(Data_2005!$S$2:$S$170))
wb is attached (stripped down for upload)
View 2 Replies
View Related
Jul 27, 2014
I have a two different formulas the return a numbered result(PO Number) in the same column. I then vlookup both of them with the same formula into a pivot table, one returns the result one doesn't. format appears to be the same.
View 6 Replies
View Related
