Refuses To Calculate A Formula!
Jul 20, 2009
I have a spreadsheet that I was sent (with some resource calculations on it - nothing major or huge) and I have added a sheet to it. The issue is that, on the existing sheets, any formula I add works but on the new sheet - they don't!. I get on the new sheet is the formula written out in the cell:
=vlookup(b2,table,2,false) rather than the answer
I have
- looked at the formula and it is correct
- cut and paste all sheets (values only) into a new sheet
- had a look in the VBA window - nothing stood out
View 2 Replies
ADVERTISEMENT
Aug 23, 2008
I'm trying to clear one set of cells when a change is detected in another, but my VBA function refuses to change the cell values.
Attached is a stripped-down version of the sheet & code which manifests this problem.
The affected cells are unlocked etc, and the code is correct -- at least, it correctly gets to the proper range (or cells) on which to call (in my various attempts) ClearContents, .value="", etc. So the problem is not the algorhithm passing the variables but in the specific attempt to change a sheet's cells' values.
View 14 Replies
View Related
Apr 12, 2008
All I want to do is create a group of 3 option buttons in an excel worksheet for a user to select one of two farm programs or none. (CIG or MRP or None) When a user makes a selection either CIG or MRP or None appears in a designated cell. I even used code in the VB editor help but it only shows how to operate one button (which is absurd.) I found an example using 2 buttons, but that wouldn't work either. Found a tutorial using 3 buttons and that wouldn't work. Is this a conspiracy? Every time I try to find help to do something simple in excel lately I hit a brick wall.
View 9 Replies
View Related
Aug 23, 2008
I've posted the simple sheet elsewhere and others report that the sheet and routine works fine for them. The exact same sheet that is failing for me.
The algorhithm itself works for me too - everything is parsed correctly and the proper range successfully receives the .ClearContents command. It's just that, on my sheet, nothing happens.
I have the entire book and sheet unprotected, all the cells unlocked, and permission for all macros to run. I.e. I've removed every possible "excel-level" roadblock I can think of. I've tried restarting Excel, even rebooting -- all with no luck. The page still doesn't work.
What other possible settings might be causing the consistent failure when I try to run the sheet whereas other users (reportedly) have no such problem with my VBA routine and their identical copy of my sheet?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim clearTable As String
Dim clearTrigger As String
Dim clearRegion As String
With ActiveSheet
clearTable = Range("ClearRegions").Text ' Get clear table.................
View 9 Replies
View Related
Sep 8, 2009
I am attempting to create a Spreadsheet which, when double clicking on a cell, loads up a Userform, has some times entered, calculates the difference and submits any overtime done.
I've had no problems doing the userform and the related coding to do the calculations. However, when I want to enter a time without the date (For example 0900), no matter how I do this the date always appears at the top in the formula bar.
I have tried http://www.cpearson.com/excel/DateTimeEntry.htm
to no avail, even when I just used this code in a blank workbook it still returns the date when I enter 1700 for example. And I have made sure the cell is formatted in "hh:mm:ss".
So, what is driving me stir crazy is why on earth my Excel is not letting me just enter times without colons and returning a date, no matter the formatting.
View 9 Replies
View Related
Oct 7, 2009
i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.
View 3 Replies
View Related
May 6, 2013
Formula to calculate GST? I have a Non-capital and a Capital column, then a Claimable GST column.
I currently have '=SUM(E5/11)' in the Claimable GST column (G5).
If E5 is a zero value I need the sum of F5/11 in G5.
View 2 Replies
View Related
Feb 22, 2014
I need to calculate a TAT formula for the below case
P1 -- 1 Hour
P2 -- 2 Hour
P3 -- 4 Hour
P4 -- 8 Hour
Shift Start time : 6:30pm
Shift End Time : 3:30am
Suppose a request comes at 9:30pm and it is Priority 1 (P1) then it should be completed at 9:30pm + 1 hour = 10:30pm. I have given the below formula to execute this
=IF(A2="P1",1,IF(A2="P2",2,IF(A2="P3",4,IF(A2="P4",8,0))))
=B2+(TIME(D2,0,0))
The problem here is if the request is P4 then its 8 hr TAT and the formula will calculate the time as 5:30am. Since the shift time end at 3:30am itself the actual TAT should be the next day 8:30pm.
formula to calculate the TAT which includes the start time and end time and also excludes the weekends and holidays.
View 14 Replies
View Related
Nov 2, 2009
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0
in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).
View 3 Replies
View Related
Dec 23, 2008
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.
for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.
the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.
so i need to know how to calculate for the 35 min at say 15.24 ph.
View 14 Replies
View Related
Nov 25, 2006
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure
The FIRST £1520 should be multiplied by 0.1 (in cell C19,)
The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)
View 9 Replies
View Related
Jun 20, 2006
i am trying to build a formula to calculate a persons age.
I have tried using the DATEIF function but i keep getting a N/A
View 9 Replies
View Related
Mar 23, 2013
I am introducing a formula with VBA but it does not calculate it. When I get out of the execution of the macro, hit F2 then Enter, the formula calculates now. Obviously I don't want this because then I would have to do F2-Enter to all the cells. I need that when VBA pastes the formula and the formula sends me the result instead of sending me the #NAME? error.
The formula is: =if($A$2<>$A$3,TODAY())
I have tried the next instructions with no success:
VB : ActiveCell.Formula = "=if($A$2<>$A$3,TODAY())"
VB : ActiveCell.Value = "=if($A$2<>$A$3,TODAY())"
VB : Activecell.FormulaR1C1 = "=if($A$2<>$A$3,TODAY())"
And so on. Some of them would send me a compile error, and those that paste the formula give me the #NAME? error.
View 4 Replies
View Related
Apr 22, 2014
I am currently using the following formula which has been working great for what we needed it to do:
=IF(ISERROR(NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1),"Not Completed",NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1)
The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.
As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.
I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.
Sample.xlsx
View 4 Replies
View Related
Apr 25, 2014
how I have the data arranged... What I need to do is for each type I need to be able to calculate usage, so I will need to make sure it is subtracting say COKE usage the week of APRIL 1ST where beginning inventory on april 1ST was 99 and on April 7th the invnentory was 72, of course the usage is 99-72/7 will give me per day but I need the formula to be able to know which date will correlate to know which inventory to subtract from... So each time I add a new inventory in the sheet it will always be able to find where the data is it needs to subtract from to get the proper usage... Of course do this without manually entering the usage each week...
View 1 Replies
View Related
Aug 11, 2014
Is there a way to calculate an age based on Date of birth and current date?
View 3 Replies
View Related
Jan 9, 2014
I am fairly new to spreadsheet and need to develop a simple spreadsheet that will present a actual vs budget % for certain clients on a weekly basis.
It is currently taking me forever to remap my formula each week when i have populated my actual vs budget data.
On Sheet 1 it has my data and on Sheet 2 is where my ratios are presented with graphs.
On Sheet 2, the ratio is presented in one cell with a formula that calculates based on two cells adjacent to each other on Sheet 1.
I am currently running my report horizontally across the spreadsheet.
The problem is that when i add in another week running horizontally across my spread, the formula can not be dragged across as it does not jump by two cells.
I have attached a example : example.xlsx‎
View 6 Replies
View Related
Feb 6, 2014
I'm looking for a formula to use. I need to calculate ratings on variances in percentages:
Variance vs expected Rating
0% 3
1-5% 2.5
6-10% 2
10-15% 1.5
15%+ 1
The expected percentage is 5. In other words if a % below 5 is obtained the rating is 3. Between 6% and 10% the rating should be 2.5.
View 8 Replies
View Related
Jul 11, 2014
I cannot get a formula to transfer (copy) into a separate cell, the calculation will not display unless I select the f2 function or save. Also when I copy the formula into numerous cells below the original formula how do I stop it incrementing?
View 2 Replies
View Related
Jan 22, 2009
how to calculate the best of three numbers from 5 numbers in a row?
I want to know the exact formula for this.
View 9 Replies
View Related
Oct 27, 2009
I have attached an example ....
If both of the discounts being offered are positive then I am ok with.
In this example the first discount ofered is -25% and the second one is +50%.
If the required charge is higher than the standard charge then, in this example, the final charge should give the value as 2,500.
What would the formula be that added up the 2 discounts together and gave the correct value in cell B8?
View 10 Replies
View Related
Nov 16, 2005
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers
'chosen' at random by 500 unique people.
I have another row of numbers representing the 'frequency' that the
aforementioned number was chosen in cells A2 to Z2.
Example:
1 2 3 4 5 6 7 8 9 10 11 ...
10 4 16 9 23 20 61 13 18 43 17 ...
Is there a formula that I could use to calculate the percentage (0%-100%)
that each number was chosen?
View 14 Replies
View Related
Sep 13, 2006
I need to work out a formula to calculate the “value over limit” in the following scenario:
Cell D11 contains a total balance (input manually)
Cell D1 is the total of cells E to H
Cell A1 contains the formula to calculate the % of Cell D1 against the balance in Cell D11
Cell B1 will used to input a credit limit which will either be blank or the value of the limit
In Cell C1 I need a formula that will return 0 or value over limit based on the following-
Formula to calculate the value over limit: If cell A1 >10% of Cell D11 return the value difference between 10% of Cell D11 and the value in Cell D1
example; cell D11 = £100,000 & cell D1 = £22,000 (cellA1 = 22%): 10% would be £10000 therefore, the result would be £12,000 over limit
However, the comparison also has to take account of Cell B1. If this contains a balance then this needs to be taken into account e.g. in the example shown, if cell B1 contained a balance of £5000 then the result would be £7000 over limit or if the figure was =>£12000 the result would be 0.
If the % in Cell A1 is <10% the value is always returned as zero in Cell C1 regardless of any balance in Cell B1.
View 14 Replies
View Related
Oct 18, 2013
Calculate dates as The Number Of Days, Months Or Years Between Two Dates
The two formulas I tried below show the wrong output when trying to figure the days between 12/8/2011 to 1/2/2012. It should show 0 years, 0 months, 24 days, not 138 days.
I did notice when the day of the month on the start date is before the day of the month of the end date, the formula seems to work. Ref. the second dates on the first example.
Example 1
Start End
12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Wrong display. should show 0 years, 0 months, 24 days
Start End
12/1/2011 1/2/2012 = 0 years, 1 months, 1 days
Note that when the start date "day", (1), is before the end date "day", (2) the formula seems to work.
First formula used above:
=DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"y")&" years, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"ym")&" months, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"md")&" days"
Example 2
Start End
12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Should show 0 years, 0 months, 24 days
Second formula tried above:
=DATEDIF(B10,C10,"y") & " years, " & DATEDIF(B10,C10,"ym") & " months, " & DATEDIF(B10,C10,"md") & " days"
View 2 Replies
View Related
Dec 3, 2013
East
Item #
Cases in East
Dropped 1
Pending
Wave 1 - Qty
Dropped 2
Wave - 2 Qty
Dropped 3
[Code]...
I have above data where in I need to formula to calculate the 428 in dropped 1 should get calculated in Dropped 2 & dropped 3 as per the plan in wave 1 & wave 2
View 3 Replies
View Related
Dec 20, 2013
I need a correct formula to calculate percentage...mine is not correct
Cell J18 will hold the formula.Range C18:I18 will have values.
C18 will have a value for today,D18 will get a value tomorrow,E18 will get a value 1 day after tomorrow etc..etc..
J18 formula - =sum(C18:I18)/7
If I only has a value in J18 then it will divide that single number by 7.......can a formula only divide by entries in cells....if I have 1 entry then it divides by 1,if I have 4 entries then it divides by 4 etc..etc until thee is 7 entries.
View 4 Replies
View Related
Jan 15, 2014
MLK day is the 3rd in January. I have a cell with the current Year 2014, which becomes input in the formula.
View 3 Replies
View Related
Oct 30, 2007
I need a formula to calculate the height of a cell. Basically I will repeat this formula thoughtout the database, and each cell in column U will have its row height.
have come up with the following
Function RangeHeight(rng As Range) As Double
RangeHeight = rng.Height
End Function
Now, the problem is, that the formula does not automatically update when the row height changes, and when I open the file on another pc, all the formulas change to #NAME since it does not recognise the custom formula.
View 9 Replies
View Related
Feb 25, 2008
I am trying to calculate the number of items that took more than 5 days to complete.
The user enters the month and the year for which stats are required in G5 and G6, respectively (e.g., February and 2008). Both cells are formatted as General.
Column D contains the start dates, while column Q contains the completed dates.
I used the following formula but I am not getting the expected result:
PHP
=SUMPRODUCT(--(DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))>=DATE($G$6,CHOOSE(MATCH($G$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12),1)),--(DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))<=DATE($G$6,CHOOSE(MATCH($G$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12)+1,0)),--($D$10:$D$12<>""),--($Q$10:$Q$12<>""),--(DATE(YEAR($Q$10:$Q$12),MONTH($Q$10:$Q$12),DAY($Q$10:$Q$12))-DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))>5))
View 9 Replies
View Related
Feb 4, 2010
I have conditional statement {=SUM(IF('name of sheet'!$H$2:$H$226=H8,1,0))}
When ever I try to add a 2nd condition in using the wizard, I get incorrect results.
H8 is the cell that I want the calculation based on. How do I put on there that I want the calculation based on H8 or H9. If the answer is an easy one I apologise in advance. I'm a newbie to excel.
View 9 Replies
View Related
