Combining IF Statement With Calculation?
Nov 4, 2013
I am putting together a spreadsheet that needs to perform different calculations based on the information that is plugged in to the spreadsheet. Right now, I have a simple formula for my final product, which is:
=G2-E3-F3+C3+D3
But, if the variables change, then I have another cell with this formula:
=IF(OR(C4=0,D4=0),"",SUM(G2,H3))
Now, my first problem is that I need to get the information regarding D4 out of the above formula. Someone wrote the formula below to combine the information in cell C3 & D3, so I no longer need the reference to cell D4 in the above formula. How to remove the D4 references without getting an error.
You will notice that the 2nd formula has cell C4. This cell has an IF statement in it. The statement is as follows: =SUM(IF(C3=0.875,D3-0.625,D3))
Basically, what I need to do is to combine the first & second formulas on this page. How to do that.
View 2 Replies
ADVERTISEMENT
Mar 26, 2009
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
View 4 Replies
View Related
May 2, 2006
I have a workbook with 2 tabs
The 1st tab has the following info
A B C
Level Acct Dollars
1 3AAAAA1,631,276.63
2 2BBBBB8,879,065.02
3 1BBBBB86.57
4 3 BBBBB234,986.25
The second tab has the following info:
A B C D
Acct Level 1 Level 2 Level 3
1 BBBBB
2 AAAAA
3 CCCCC
I want to combine a vlook up and an if statement to populate the above tab
to look like:
A B C D
Acct Level 1 Level 2 Level 3
1 BBBBB 86.57 8,879,065.02 234,986.25
2 AAAAA 1,631,276.63
3 CCCCC
View 14 Replies
View Related
Jan 14, 2009
I have this formula, which works
View 3 Replies
View Related
Jul 23, 2009
I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.
The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?
View 6 Replies
View Related
Nov 6, 2008
I would like to perform what seems like a simple calculation: =IF C1>0, THEN A1-C1, IF C1=0, THEN DO NOTHING. How would this look as a formula?
View 4 Replies
View Related
Jul 26, 2007
I have several columns as so:
B - Patient Surname
C - First Surname
D - Postcode
E - Date
F - Results of If Statement (Days Difference)
I am looking to comapre b, c, d with teh previous cells to make sure I am looking at the same person hcen the AND statement. If they all match then if subtracts the latest date (column E) from an ealier date for the same person. If the person is different it will return a 0. If the date is the same ti will return a 0.
I have done this so far - =IF(AND(B2=B1,C2=C1, D2=D1),(E2-E1),0)
What I am struggling to do is if the number of days (E2-E1) is 0-3 then for the result to show the text "Less then 3". If the value is 4 - 7 then to have it read "Less then 7", anything over 7 to read "OVer 7".
View 8 Replies
View Related
Oct 2, 2013
I need a If statement that can be used on 2 columns.
If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.
View 3 Replies
View Related
Feb 4, 2014
This formula is being used in a vacation time spreadsheet. So after each paycheck the if statement is applied and need to calculate the accumulation for that week based on if the person has worked 5 yrs or less. I need to stop the today's date calculation in the if statement after it is used. So that each time the spreadsheet is opened the values are a fixed number not changed based on the current date. However if the if statement is pasted into the next pay week it will calculate based on the actual date.
It is calculating is the following:
C4 = 5yrs Anniversary Date of Hire
3.08= less than 5 yrs
4.62= more than 5 yrs
=IF(($C$4)<=TODAY(),4.62,3.08)
[URL] ......
View 1 Replies
View Related
Dec 14, 2012
Is it possible to insert a formula or have a calculation performed for the range component when using the countif statement.
I am trying to take the entries in the rows in column B and divide its corresponding row in column A and then count how many meet a certain criteria.
View 1 Replies
View Related
Mar 21, 2014
I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE
=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)
When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct
IF Func Arg.jpg
When I view the cell in the spreadsheet, system displays #VALUE!
When I view Evaluate Formula window, it seems Excel doesn't like the text.
IF Form Eval.jpg
View 5 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Nov 19, 2008
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
View 2 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Feb 14, 2012
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
View 9 Replies
View Related
May 5, 2014
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
View 5 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
Mar 18, 2014
I am looking to have one formula containing two If Statements that minus.
=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)
So the result of If Statement 1 minus the result of If Statement 2.
What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.
View 2 Replies
View Related
Jun 13, 2009
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
View 3 Replies
View Related
Apr 25, 2007
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
View 9 Replies
View Related
Jul 3, 2007
I have a problem with a formula inserted with control+shift+enter
The formula is
MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
View 7 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Jan 7, 2009
I have some code where I use and statements, as follows:
View 4 Replies
View Related
Jan 14, 2007
I want to see if a goal has been met and get a "Yes" or "No" so I wrote
=If(A1>=A2),"Yes","No"
But on months not worked where goal and production both equal 0 then I am getting a return of Yes. How can I say is A1 >= to A2, but if both are 0 return N/A?
View 12 Replies
View Related
Apr 20, 2009
I am having problems combining AND and OR into a single statement. To start I have a statement =IF(AND(B978="P",R978=0,S978=0),"processed",""). If B=P and R=blank and S=blank then display PROCEED else make blank.
What I want to do is check if S is blank or "N" but when I try to add an OR I get error messages regarding missings )'s =IF(AND(B978="P",R978=0,OR(S978=0,S978="N"),"processed","").
View 3 Replies
View Related
Oct 11, 2012
I have two vba the first one records the date & time of a cell change in column a and places this in column b
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Target.Offset(0, 1) = Date & " " & Time
End If
End Sub
The second one is a function to get the User logged in to windows.
Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function
What I really could do with is a way of incorporating the user name in the first function so it records date, time and user?
View 3 Replies
View Related
Sep 20, 2006
I have two seperate columns, First Name (Column B) and Last Name (Column C). Is there a way a macro can combine the two into one cell Last Name, First Name.
View 3 Replies
View Related
Dec 17, 2012
I am trying to write a UDF function where I lookup if a value is on a range on different sheet. I would like to be able to write what I want the result to be in the function. I also hard coded that the function need 3 range and 3 results, but I know it is possible the make the number of component vary depending on what the user writes. I would like my function to do that.
Here is what I have written so far :
VB:
Function find_para2(var As String, rng As Range, result1 As String, rng2 As Range, result2 As String, rng3 As Range, result3 As String)
If WorksheetFunction.IsNA(WorksheetFunction.VLookup(var, rng.Address, 1, False)) = False Then
find_para = find_para & result1 & ", "
[Code] .....
View 6 Replies
View Related
Jan 31, 2009
I need to find a formula to show the time allowed for each person, now they recomend combining 'if' with 'lookup'. now I may be having a blonde moment but I have tried to figure it out for a while now and i am stuck.
View 14 Replies
View Related
Feb 27, 2009
Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!
View 2 Replies
View Related
