I have a simple formula subtracting one cell from another using =SUM(XX,-XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either.
View 10 RepliesI'm working on someone else's spreadsheet, when I type in a formula the cell shows the actual formula as typed, rather than the expected result of the formula, how do Ii correct this?
View 2 Replies View Related=K18+Sheet117!K18
K18 contains the number 54.00, and on Sheet117 cell K18 contains the number 404.00.
Cell A1 displays the formula rather than the result, which should be 458.00.
Other similar formulas I have in this workbook return a result of 0 when I know there should be a significantly higher number. Then, the same formula used to refer to different cells will return what appears to be a proper result.
Often I write the formula in the cell of an excel spreadsheet in vain, it does not work.
Excel only displays the formula in the cell such like = if(B2>2;2;0), and not the result.
Formula is correct, so I don't know why it happens. Most often this occurs when the formula is between worksheets or different excel files on the corporate server.
=CONCATENATE(B8,C8,D8) insread showing result just stays as text?
cant figure out whats wrong
My VLOOKUP formula is displaying in the cell instead of the result. The formula is VLOOKUP(A1, income_codes, 2, FALSE) and in the formula editor the result (00017) is calculated correctly.
However the cell displays =VLOOKUP(A1, income_codes, 2, FALSE) instead of the answer 00017.
My view options are not set to display formulas. The codes are formatted as text in the range income_codes as they start with zeros.
I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?
BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326
Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
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 RelatedI have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,
View 9 Replies View RelatedI need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.
Column A
Date
1/2/2007 0:00
1/3/2007 0:00
1/10/2007 0:00
2/10/2007 0:00
2/10/2007 0:00
2/15/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
Column B
Invoice #
CM-0003881
CM-0003882
CM-0003883
CM-0003888
CM-0003928
CM-0003932
CM-0003933
CM-0003985
CM-0004007
CM-0004008
CM-0004009
CM-0004065
I have this formula in cell F3 that will add the dates through the end of January:
=SUMPRODUCT((A1:A1000>=DATEVALUE("1/1/2007"))*(A1:A1000
I am having a difficult time with a calculation. I took over someone else's duty who is no longer with the company. The calculation used did not account for entire columns & the data I need to pull fromhas grown larger than the calculation. When I hit "F2" to manually change the data array, everything works fine until I hit enter and then the result displays as 0.0%. When I hit the insert function button, the result shows as the correct number. I tried copying the format only from an adjacent cell, but this did not work. When I tried to undo my changes, the cell still displays as 0.0%
The original calc is:
=SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998=9,1,IF(cData!$N$2:$N$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998<>"",1,0),""))
The new calc is:
=SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536=9,1,IF(cData!$N$2:$N$65536=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536<>"",1,0),""))
The calc from the cell below the cell I am working in:
=SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998=9,1,IF(cData!$O$2:$O$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998<>"",1,0),""))
I have a macro that computes the unique values in a column. I'd like to create a message box that displays the result of the macro.
View 9 Replies View RelatedWhen I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
Code: =VLOOKUP((LEFT(C4,6)),'Data from 7500'!$B$16:$G$195,6,TRUE)
And it works great, except that the data returned is off by one row. For example, the correct value for the sample name in B107 is located in G107, but the formula returns the value in cell G106. I've tried changing the TRUE to FALSE and that returns #N/A.
In the attached file I used =MATCH(TRUE,INDEX($B$2:$B$10="",0)) to retrieve the location of the first empty cell.
When using the "Evaluate Formula" tool, it is clear that the position in the array created by the INDEX function is the 6th. Nevertheless, the final outcome is 9, being the last cell in range.
I have a worksheet called "Raw Data" where in columns J, I and G contain values. I would like to write a formula whereby if all 3 conditions are met, it will count the number of values found in column C.
I've tried to write an IF statement but it just returns FALSE, despite the result being true. Need the right combination of IF/AND statements that would do this?
=IF(AND('Raw Data'!$J:$J="Maintenance",'Raw Data'!$I:$I="Open",'Raw Data'!$G:$G="1-2008"),COUNT('Raw Data'!$C:$C)). I've tried to attach the workbook, but there's a problem with uploading attachments I think. Sorry if my explanation is unclear.
I have this COUNTIFS expression buried in a larger formula:
COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1")
and it always returns a value of zero or some positive integer.
I'm now looking for a little "function" that I can wrap around this expression that will:
A) indicate any non-zero result as 1 or TRUE
and
B) indicate any zero result as 0 or FALSE
Something like...
AREYOUNONZERO( (COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1") )
I'm sure there's a fairly efficient way to do this... but I'm totally stumped at the moment!
I have a three or four letter abbreviation of a last name in A1. Column C contains a list of Last Names; column D contains a list of corresponding First Names; and column E contains a formula concantenating the First Names and Last Names from columns C & D.
I have the following formula in B1:
=VLOOKUP(A1,C1:E44,3,1)
I am attempting to match up the three or four letter abbreviation with the concatenated list to return the full name (First and Last) in cell B1, however, I'm not getting the desire result. I keep getting the name in the row just above the name I want.
I have discovered a strange thing about excel and it is driving me mad and ruining my project.
Basically, If i enter the number 97.4 into cell A1 and then 97.165 into cell B1 and then enter the formula =A1-B1 into cell C1 i get the answer 0.235. Which is correct.
However, when you increase the number of decimal places to 30 the answer that is returned is 0.234999999999999000000000000000.
This calculation result seem to fluctuate wildly depending on the primary numbers in cells a1 and b1. Very rarely have i seen the full 30 decimal places without a bizarre result like this.
Maybe it has been too many years since i was at school and that i am confused with my numbers and that there is a real easy answer to why this result is returned.
As i said it is ruining my project that i am working on. Can anyone help? Why does excel do this? Is there a way to stop this happening? My project relies on this answer to be correct. Is there a way to force excel to stop doing this?
I'm compiling a list of names on a separate tab from a column that only lists the names sporadically. Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should.
=IF(ROWS(F$5:F5)
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
I'm using the following formula, and keep getting a zero when the cells in D4 are blank when I simply want exactly whatever is in D4 to be shown...
=IF($B$2="Alness",Alness!D4,IF($B$2="Amsterdam",Amsterdam!D4,IF($B$2
="Atlanta",Atlanta!D4,IF($B$2="Budapest",Budapest!D4,IF($B$2
="Dalian",Dalian!D4,IF($B$2="Debrecen",Debrecen!D4,IF($B$2
="Durban",Durban!D4,IF($B$2="Edinburgh",Edinburgh!D4,IF($B$2
[Code] ..........
I have a VLOOKUP formula in a cell that refers to another cell that has a formula in it.
When I type the actual numerical result (rather than the formula) in the formula cell, the vlookup works. When the formula (that equals the same number) is in there, the vlookup cell is returning #n/a.
I have tried a simple =CONCATENATE(A1+B1) in a cell and it shows the formula not the value. I have tried
1. Formatting cell to various formats other than text
2. Deleting the Row/Column and adding a new one and trying the same formula
3. Ctrl + ` to show/hide formulas
I dont know why it keeps displaying the formula. Anyone know of a way to fix this?
Is there a formula in Excel that returns the active cell address (ie dynamically). Excel updates the activecell address in the Name Box dynamically as you make a selection but I cannot find a standard formula to access it. I know I can achieve this with code using the selection-change event but this action then disallows use of the Undo button - which I specifically want to avoid. Perhaps there is an add-in available?
View 9 Replies View RelatedI have a list of names in a column that will be changing on a weekly basis (with some names repeating). I need to create a Named Range that only counts unique values from that list of names (dynamically changing every week).
View 11 Replies View RelatedI wrote an IF AND formular to display "Yes" for a cell with zero. but when the cell is also empty, it still returns a "Yes" instead of empty.
this is the formular:
=IF(J2=3,"Yes",IF(AND(E2="",I2=0),"Yes","No"))
when I2 = 0 it returns Yes
Also with I2 = "", it still returns "Yes" instead of "No"
I am looking for a formula that returns the cell address of the last cell > 0 in a range.
in the example: A1CLA23 8
4546557528
the result should be A7.