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 RepliesAttached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
View 4 Replies View RelatedI 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 working on creating an examination questions in Excel. I want to also build in formulas in the Excel sheet so that the answers will be calculated automatically as the candidates choose the answers. I have inputed an option button from the froms toolbox beside each answers for the candidate to click to indicate the answers. Is it possible to calculate the correct answer or wrong answer with a formula when the candidate clicks on the option button ? Is there a formula that I can use?
View 2 Replies View RelatedI am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View RelatedI have this formula that returns a grade mark for a specific score in a test:
=VLOOKUP(D4,'Grade scale'!$A$34:$B$42,2)
using these values in the lookup table:
Low scoreGrade
0.0% U
25.0% G
35.0% F
45.3% E
55.3% D
65.5% C
72.0% B
78.0% A
85.0% A*
The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?
I am working on a form to pull employee identifiers such as employee ID, store number as well as sales performance. I am using the below formula but the data that is being returned is not the correct data for the specific employee. C5 is the employee name (last name, first name) in a single cell. A sample of the spreadsheet I am trying to pull data from is attached. Some employees the formula pulls the right data and some it does not...
=IF(C5="","",VLOOKUP(C5,'Raw Data'!A3:N15,2))
If the item in the vlookup table Array does not exist, the vlookup returns the result of an unrelated item. Is their another formula I can use?
View 2 Replies View RelatedI have a table, let's call it Table1. Each column has the ability to filter but no filters are applied at the outset of this issue. I want to average a column labeled Column13. I also want to apply a filter to Column13 that states only show values over 50%. I apply the filter and in the cell I want the average calculated in I type the formula:
"=AVERAGE(101,Table1[Column13])"
The 101 (as far as I'm lead to believe) only applies the average to the rows *SHOWN*. However, the average I get back is 45.67%. This is obviously wrong as the filter states only show rows where the column is greater than 50%.
in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is $168 but in FEDEX that one is not correct 1kg to Japan is $169 but now show $342, the formula in "G36" should be correct, what's wrong of my formula????
View 3 Replies View RelatedThe following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
I am using this formula for long now
=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)
every month i just add a new sheet and change the month name in the formula to that eg
I changed it to
=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)
but now it is not calculating the answer is coming as #value!.
Then I tried to add up by
=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)
but it does not add all the entries only one entry is shown.
I want the total of the column G. looking up for values in C = H1 value
I would like to use IF function, to get the answer, if the mark is less than or equal to 20, the answer is AVERAGE, if the mark is between 21 and 25 the asnwer is GOOD, if the mark is between 26 and 30 the asnswe is VERY GOOD, if the mark is above 30 then the asnwer must be EXCELLENT.
View 4 Replies View RelatedI have a formula that is correct all the way up to adding the last *K9. here is an
example:
C9 a number 4 then add 1 to it which makes it 2 now * it by D9 which is the number 384 and the answer is 768 now * that by K9 which is 6 and you get 4608 I'm getting 2310 using this formula... =IF( C9=0, 0, C9+1*D9)*K9.
Is there a way to have the answer to a formula display nothing if one of the cells have nothing. Example cell A3= A1*A2 ... it will naturally show 0.00 but even if both A1 and A2 are completely blank. Is it possible for A3 to show nothing if A1 or A2 or both contain nothing. I still want it to show 0.00 but only if that’s what is in the other cells.
View 5 Replies View RelatedI 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 need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 Replies View RelatedI have the following letters in cells
D2= A
D3=G
D4 =L
These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,
Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.
I 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.
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.
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 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!
It works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".
=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))
I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero.
When added using =a1+a2 the value returns correctly.