Formula Does Not Work On Some Rows
Aug 11, 2014I have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
I have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedI am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
I have a spreadsheet that only contains 1500 rows but excel acts like it is is using the full number of rows available. I have a print area defined and I have tried deleting the unwanted rows but nothing seems to work.
View 2 Replies View RelatedGoal: Hide rows based on value in Drop Down Box
Problem: The code works fine if i manually change the $D$10. If I make changes to the Drop Down Box the cell link updates, but the rows are not hidden.
[Code] .....
I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.
Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:
If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................
This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
=IF(E8>0,"$0.00",IF(E9199,(E9*0.01),IF(E10>50,"$50.00",0))))
See the attached workbook.
Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.
So cell M16 should not be a BINGO because cell G16 is not less than 51.
Attached Files : Book1.xlsx‎
Combing these two formulas. I have a work schedule spreadsheet. If the employee is Off I want the value to = 0 (zero hours). But if the cell has a start time I need it to calculate those hours. I know both of the formulas work individually. But I need them to work together.
Formula for when cell says OFF:
=IF(C11="Off",0)
And the second
Formula to calculate hours (based on start time/finish time is the same for all employees)
=($X$4-C11+($X$4<C11))*24
=SUMPRODUCT(--($C$1:$C$17="S"),--($C$2:$C$18"S"))
how does this formula work?
It counts the number of instances in a list but not sure how
does the ranges have to be differant for this to work? ie C1:C17 C2:C18
I need to work out a formula which will work out 48 permutations.
There are three headings: Impact, Probability, Risk Category:
ImpactProbabilityRisk Category
VHM = 1
VHH = 1
VHVH = 1
HH = 1
HVH = 1
VHL = 2
HM = 2
MH = 2
MVH = 2
HL = 3
LVH = 3
MM = 4
LH = 4
ML = 5
LL = 5
LM = 5
The formula I need is: if Impact is "L" and probability is "M", then return answer = "5".
The thing is - there are 48 possible answers - and im not sure what formula you could type into 1 cell - which would answer this.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!
However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?
To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?
I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using:
[Code]....
Before I add the Vlookup, I get a number of 50. I should get at least 40+, but after adding the
[Code] .....
I get "0."
I'm wondering if I need to use VLookup differently in this case ????
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
On the attached form, for row 3 information is manually inputed into C3 and E3 to J3; K3 then looks at the max value inputed in E3 to J3 and alters L3 accordingly.
Row 4 is my attempt to automate the process. I am trying to alter the sheet so that the information added into C4 updates E4 to J4 automatically and then K4 looks at the max value and alters L4 accordingly.
Since I added the formula to H4 to look for info in C4, the max formula in K4 no longer works, as such L4 will not update.
I currently have a sheet of data with names and scores (1-10) on e.g.
NameScoreJohn10Smith5Steve4John9
and what i am wanting is a formula that will read these columns and tell me the amount of 1's, 2's, 3's, 4's, 5's, 6's, 7's, 8's, 9's and 10's the corosponding name has gotten.
for example:
JohnSmithSteve123415167891101
I have been playing around with COUNTIF formulas but cannot seem to work this out.
I'd like a calculation that gives me a total of 7.50 hours instead of 8:00 hours (I'm hoping to add up the time worked into a meaningful hour:minute format)
View 2 Replies View Relatedim trying to use below formula using SUM and two IF conditions and 1 left formula
Cond 1
Cond 2
Value
22
m9
10
23
m9
10
[code]....
the following formula works to capture the sum of combination 22 and m9:
{=SUM(IF((A2:A6)=A8,IF(B2:B6=B8,C2:C6,)))}
22
m9
20
24
m8
10
however, in my original table, the 22, 23 etc are values with 6 numbers as shown below
Cond 1
Cond 2
Value
2201
m9
10
2301
m9
10
[code]....
following formula didnt work;
{=SUM(IF(LEFT(E2:E6,2)=E8,IF(F2:F6=F8,G2:G6,)))}
22
m9
0
23
m9
0
why the secund formula didnt capture anything when i added left to the first one.
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255
End If
Next i
My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.
Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
I am trying to add a number to a cell for work breaks. Below is what I am trying to do:
If F3 is < 4 then 0
If F3 is > 4 but < 6 then .25
If F3 is < 6 then .50
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
I have a VLOOKUP formula, so when the cell above (C5) has a value, it returns the value from a defined list. Simple so far and it works great when I manually input the value in the cell (C5).
The problem occurs when I change C5 to contain an AVERAGE formula? Any ideas why it will only work when there is a plain old value in there, but not a formula?
This is the VLOOKUP formula I'm using:
I am having trouble getting the double lookup formula to work with a table. See attached sample.
View 2 Replies View Relatedis there a a way to get an Array formula to work in a shared workbook? when this was put in the workbook It worked perfectly but I didn't think about the fact the workbook was going to be shared !
The formula is:
Code:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(S$3:S$40,SMALL(IF(COUNTIF(L$3:L$38,S$3:S$40)=0,ROW(S$3:S$40)),ROWS(T$3:T3))-ROW(S$3)+1)))
I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example:
If price is $0-$74.99 than add $50
If price is $75-124.99 than add $75
If price is $125-174.99 than add $100
and so on til $7000
I am trying to get a formula to work where it will search a single cell (on a sheet called "Calls") to see if a particular code is in there, if so return a value using HLOOKUP from a table on a sheet called "Values". The problem I am having is how to use the wild card etc because everything I have tried returns an error...
The code I am trying (Without wild cards, because they all failed) is...
Code: ....
Want to work out formula for working hours If Mr Smith is booked to work I must pay him a min of 8 hours pay at rate A then upto 10 hours still on rate A but after that he gets rate B I need to enter his data on a daily spreadsheet
View 9 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
complicated formula for working out prices including applying discounts.
I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19
I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.