I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View 9 RepliesI have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.
The same formula could be another place in my worksheet and work fine.
I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.
I tried doing Ctrl + ~, but it did not work
View 14 Replies View RelatedI’m trying to resolve this issue and would appreciate some help.
Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
When 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.
I created a formula in a cell and it worked fine but then I created the same formula in the cell just below it and I changed some of the numbers and when I hit the enter button the formula showed up in the cell and not the final result which would be the answer to the formula. Does anyone know why it would do this? I have an example below....
View 6 Replies View RelatedI have a formula when C15 is empty it shows -18 value. I need if the value is blank then show blank?
Code:
=IF((Data!C15-32)*5/9="","",(Data!C15-32)*5/9)
I was given this Formula to use in a cell to calculate the total hours from 2 different cells, from inputting date and time from and to dates..as an example from 4/1/14 0:00 thru 4/1/14 12:00.....The problem I am encountering is that when the spreadsheet is 1st opened "+1" is visible in the cell where this formula is. I am asking how the formula can be altered to have the "+1" not visible in the cell before any date and time is entered.
= INT(F13-D13)*24+(((F13-D13)-INT(F13-D13))/0.04166666)+1
I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
View 2 Replies View RelatedWhen i put a "0" in a currency formatted cell, the result is a blank cell. If i put any other number in there other than "0" it shows a dollar amount of that value (even .00001 works). I have also noticed that before i put a zero in that cell, i can go to the format of that cell, select currency, and i see a sample in the sample box ($0.00). But if i go to that cell, put a "0" in it and return to the format of the cell, the sample box is now blank and it doesn't even show a $ sign. I know the "0" is in the cell because i can see it in the formula bar. But on the sheet itself it does not display it.
View 3 Replies View RelatedI have a value in Cell A1. Now, I need to be able to enter a number into Cell A3 and upon hitting Enter (or clicking elsewhere), the number I just entered into Cell A3 needs to be replaced with the number I just entered multiplied by the number in Cell A1. For Example: Cell A1 contains the number 1.05. I type 2.50 into Cell A3 and upon hitting Enter Cell A3 reads 2.625.
View 4 Replies View RelatedI have the formula in O62 cell :
[Code]....
In formula bar when I sellect complete formula and press F9 key, I can see:
[Code] .........
results.
How can I write this visible result to "O62" cell or, " another cell example"P62" as a text value.(without to copy & paste).
I want to make this with a formula ( if impossible by macro).
I need to use the result of a simple calculation in one cell on Sheet1 (=A1+2) as the row in a cell reference on a different sheet. eg =Sheet2!A"n" where "n" is the result of formula.
View 4 Replies View RelatedI'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.
I have created a visual schedule for my team using Excel (2003, SP3 if that matters). Essentially, the user puts in pre determined 1-3 character codes in individual cells, and the macros I have act like a complex Conditional Formatting to keep the formatting neat and consistent throughout the sheet. The actual values are inputted directly into the cells though (this is pertinent to my question), and are things like "A" "M" "$" and "TR"
I have a sheet for every day in a week. Since there are multiple team members on any given day, I have recently made another sheet which pulls a single Team Member's schedule Sunday through Saturday and displays their schedule for the entire week. I have used formulas (specifically VLOOKUP) to do this.
The problem that I am having though, is that the macros that I made to format the days of the week sheets, do not seem to recognize the result of the formulas in the individual Team Member's sheet, and thus do not format them as desired.
My macros are written to evaluate a cell's value via [ Range("example").value ] and will act upon it accordingly with more code. I am assuming that a [ Range("example").value ] would see a cells value as the text of the inputted formula, and not the result of that formula. Is there any way around this? or do I need to avoid the formulas all together and write in code to just copy over what I need?
(I hope this makes sense)
Sub startup
Dim numwords As Integer
numwords = WorksheetFunction. CountA("H5:H64")
Range("H1").Select
Selection.FormulaR1C1 = numwords
I have a quick question regarding this simple code thats been drviing me nuts. There x distinct pieces of Data in the range speciifed, but when i run this code, the reply posted in cell H1 is 1, instead of x.
Eventually the code will be built to check if the result is odd or even and then add an entry into the first blank cell if the result is odd.
How is this possible,
in cell A3, (''if'' cell A1 has text "transfers'')
then cell A2=7
Why is it that when I edit some cell's formulas and press enter the result is not the changed formula but the formula itself complete with the '=' sign infront of the fuormula. The work around for me is to cut the formula and paste it into a new cell then drag the old cell over the previous one I tried to edit.
View 3 Replies View RelatedI'm typing the formula straight into the cell and funny thing happens: the cell displays the formula, but not the result...
SO now I can see =A2+B2 and no values.
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 Replies View RelatedOften 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.
I am trying to find a way to display text that is the product of a concatenate function in a defined cell, but not to have the function itself in that cell. Basically, I want to have the below function in cell A1. I want to add a command to it to take the result and display it in cell A2.
=CONCATENATE(C5,"_",'Attachment 3-A'!C9:E9,"_",IF(C5="","",IF(C6="MPL",IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Lease","Lease_Contributable",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Pre-Lease","Managed_Pre-Lease",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Non-Contributable","Managed_Non-Contributable","Error"))),
IF(VLOOKUP(C5,Sale!B:F,5,FALSE)="Assignable","Lease_Assignable","Managed_Non-Assignable"))))
The reason for this is that I need the cell to be selectable (it is generating a file name that needs to be selected and copied), but I don't want the code behind it to be seen. I can't find a way to make the cell selectable AND hide the formula from being seen when the cell is selected. This is because our people keep copying the formula rather than the resulting text.
I was hoping for something like a DISPLAYIN(target) function, but it doesn't seem to exist.
=CONCATENATE(B8,C8,D8) insread showing result just stays as text?
cant figure out whats wrong
Can cell comments be returned together with the results of Index / Match function. If not is there any way to link the coments ?
View 3 Replies View RelatedI have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I'm having the following problem:
A1 & B1 = variable inputs from a calculator
Value C1 is calculated as follows: IF(A1<=0,"",(B1*G1))................(where G1 is a fixed value with 6 digits)
MY PROBLEM: The result in C1 should always be a ROUNDUP value.
I just don't know how to combine ROUNDUP with IF(A1<=0,"",(B1*G1))
I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum.
This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.
how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.
View 2 Replies View RelatedI have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.