I have a MROUND function to round a cell value to multiples of 20 but to also return 20 if the value of the cell is less than 20. How do i combine the MROUND function with the if statement to give me that result?
View 5 RepliesLet's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
View 2 Replies View RelatedIs it possible to combine these two functions, Roundup and MRound?
Current formula:
=IF(MROUND(E5950-F5950,10)-0.01>E5950,E5950,MROUND(E5950-F5950,10)-0.01)
I attempted:
=Roundup(IF(MROUND(E5950-F5950,10))-0.01>E5950,E5950,Roundup(MROUND(E5950-F5950,10))-0.01)
how do I use mround properly in this function?
I did this =(IF(ISNA(MROUND(A20*0.75),"$0.00",(A20*0.75))) and it isn't working. I want the numbers to the nearest 5 or 10 cents.
Is there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?
I almost get my eyes killed whenever at the end of the month
Statement
A
B
C
D
[Code].....
I have issued invoices with reimbursements to Mr. A so on 30th of the month, I issue a statement to summarize my invoices and reimbursements as above example. Note : An invoice and a reimbursement are always in a file(sheet1 and sheet2) and a solo reimbursement is a file(sheet1). "Invoice"(Folder) in drive D contains Invoice00100(SI11) & Reim00111(SE55).
Samples of invoice and reimbursement
Invoice(sheet1)
A
B
C
D
E
[Code]....
Solo reimbursement is the same as above reimbursement table
code that can extract all excel file in "invoice" folder and auto create as my first above table. I would like to manual select the files which are taken to extract to the statement.
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?
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 RelatedI 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
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.
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.
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.
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?
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".
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
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.
I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.
So for example:
Worksheet 1 (January)
Column A Column H
Asset # Total Expense
5021 $3,041.52
970289 $242.08
Worksheet 13
Column A Column B Column C Column D
Asset # Asset Description January February
5021 Rivet Machine $3041.52 $345.65
So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.
I need to get 5 small subs into 1 sub. (run the whole program by pressing once.
Please see attached file with codes : ServiceDriftMaaling.rar
Ok, These are the three formula's. How do I combine them or can I.
=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)
=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)
=IF((B2="COM"),[@[Gal Billed]]/1000*3)
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies View RelatedI have two formulas that i need to combine as one, but i dont now how.
This is the other formula:
=SUMPRODUCT((Huollot!I2:I500="Ty”n alla")*(Huollot!K2:K500="Warranty")*1)
The other one:
=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with
I want to combine number in one row.
Column A
12
13
14
15
Need to be in column B. 12,13,14,15. I tried “=A1&”,”&??
Trying to write an IF query looking for multiple returns that would equal the same result.
I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.
1 or 6 = 1
2 or 7 = 2
I have been trying unsuccessfully to combine the IF and OR queries
I am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.
View 9 Replies View RelatedI am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
View 6 Replies View Relatedi m working on a spreadsheet and i need cell A+B=C..
Example
Cell A has a first name (John)
Cell B has a last name (Smith)
Cell C has last name then first name seperated with comma
Is it possible to combine three cells into one? Like for example, column A is the first name, column B is the middle name, column C is last name.
Is it possible put it on a format like this: Last, First M.
(middle name on column B is a name, not an initial.)
And can I make it only the first letters are capped. Last, First M. Or all small letters. last, first m.
I have:
- Table 1 has ~1,600 records (name of game, meta score, user score)
- Table 2 has ~3,000 records (name of game, genre, publisher)
I want:
- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)
Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.
I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).
Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that
combine two excel worksheet into one new worksheet. thing is on both of these worksheet doesnt have a unique identifier. Is that possible & how.
View 2 Replies View Related