My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View 2 RepliesOn column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View 4 Replies View RelatedIs there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
function that add to any cell that doesn't reach the disired amount.
If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it.
For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
I have an excel sheet which has been exported from an 'Employee Attendance Software',
http://www.flickr.com/photos/34247990@N06/4299288997/
In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.
Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion
http://www.flickr.com/photos/34247990@N06/4299288959/
I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....
View 9 Replies View RelatedI have a system that generates 4 databases with different types of statistical data. Observed defects, Calculated Defect, Capability and Number of points. I have set up a system for telling me what ranges each section is and I am manually changing the ranges to get my expected results.
Is there a formula that I can use that could adjust my ranges, within my formulas?
I have the following letters in cells
D2= A
D3=G
D4 =L
These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,
Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.
Trying to look for formula for searching through Range and search through Data to achieve expected res
Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555
Below is the query i used for manual search however need formula to see through Range and achieve output expected
=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))
I've am array formula that I use to provide highest values:
Code:
{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))
=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)),
MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128,
MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-
MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}
I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:
Code:
ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(
MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")
The two formulas work well in separate sheets but crash when put together, how could I possibly insert the second address formula into the first one?
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange
[Code] .........
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
I need a macro using worksheet activate event that will look a cell a10
and select if it doesn't have a formula
is there a way to create a formula that caps at a certain amount?
i am trying to create a spreadsheet for a group of doctors and their insurance coverage. i want to display that a certain amount of coverage caps at a certain price over the course of a number of years.
so basically, can i create a formula that automatically adds the data and max's out at a certain number?
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.
I m writing this formula in a cell but i dont receive the result i want...
Formula used... ="Program "&""&G2
(G2 is a Date)
This is te result i get...Program 40210
This is the result i want...Program 1-Feb-2010
I'm calculating a variance for a set of actual numbers vs. variance numbers. When there are no actuals, I don't want the formula to calculate a variance. How can I do this?
Spreadsheet attached : Book1.xlsx
im 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.
I have a very basic formula, and I'd like it to ignore a blank cell. The sheet has cells A1, B1, and C1. I want the formula to subtract A1 from B1, and display the difference in C1. B1-A1=C1
Currently, if A1 has a value (it always has a value in that column) and B1 does not, C1 shows the negative value of A1. Ex: A1= 100, B1= blank. [blank cell] - 100 = -100
I want C1 to show nothing unless there's a value in B1.
How do I construct the formula so that it doesn't do the calculation for blanks?
I have this formula that I'm trying to insert into cell AE2, then copy down the entire column. When I execute it, none of the cells are populated.
Range("AE2").Select
ActiveCell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$2/365)/P2)"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE3495")
Range("AE2:AE3495").Select
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View 5 Replies View Relatedmy data will look like table below:
amount
(A1)
threshold(10)
(B1)
threshold(15)
(C1)
threshold(25)
(D1)
9.80 0.00 0.00 0.00 0.26 0.00 0.00 0.00 0.53 0.53 0.00 0.00 0.40 0.40 0.00 0.00 0.77 0.77 0.00 0.00 1.20 1.20 0.00 0.00 0.33 0.33 0.00 0.00 3.40 3.40 0.00 0.00 5.67 5.67 5.67 0.00 4.00 4.00 4.00 0.00 1.20 1.20 1.20 1.20 3.10 3.10 3.10 3.10 0.53 0.53 0.53 0.53 1.73 1.73 1.73 1.73
i need a formula to zerorize the amount that is within the threshold value &
just display the same amount as input if exceed.
column A is my input, colum B,C,D is my working area where there are few threshold value.
for example,
the B column give the threshold value = 10,
thus cell B2 & B3 were zerorize. ( not B2 only)
Need count formula to count records with amounts in either columns E, F, or G. For example
Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns):
__|____E___|____F___|___G___
_7 | 1200.62 | 1500.53 | -0-
_8 | 1000.00 |________|_1620.00
_9 | 7000.00 |________|________
10 |________|________|________
10 | 2000.00 | 3000.00|________
11 | 8000.00 |________|________
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok
NAME
rob
dave
jim
darren
adam
lee
i need a formula that will tell me there is 6 rows used in that column (not including the title).
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column.
NAME
rob
dave
jim
darren
adam
lee
i need a formula that will tell me there is 6 rows used in that column (not including the title).
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I am using a VLOOKUP formula, but when I drag it down it doesn't keep the same array selection. Is there a way so that I can drag it and it relates to the cell to the left, but the array selected stays the same. I don't really want to type the same thig out for every cell as there are 6000. I am using: =VLOOKUP(D2,Working!A2:C23,2,FALSE) and want D2 to change accordingly but for working!A2:C23 to stay the same.
View 2 Replies View Related I'm coping and pasting data (html) into a worksheet. When I try to format some of the columns as accounting$, number, ect. They won't format. It looks as if there are spaces before and after the data. So I tried the trim and clean function but it doesn't work, so I guess the blank "spaces" are not really spaces.
It gets better...
When I do the same exact copy and paste (as html) on another persons computer, they are able to format just fine, and the "spaces" are removed automatically.
Why is this working on one computer but not the other? Is it a setting that my computer has enabled or disabled? If so, what is it?