I'm having a basic problem. I want to use IF function and then if true use formula e.g. B3-B2, if false use -(B3-B2). I thought it will be quite straighforward and I tried a formula, changed it into many different forms, but still got an error.
Please have a quick look in the attached file and see what I did wrong in this simple formula.
i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow VLOOKUP(B4:B225,'D:Salary[Data.xls]AUG'!A$1:F$65536,6,FALSE))
the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.
Data files will contain of these:
a:Employee ID
b:employee name
c:Employee salary/Overtime
Master files will contain of these:
a:Employee ID
b:Emplyee Name
c:Employee Nett Salary (that will be dragging from Data files)
is there any formula that i can used to combined with my vlookup formula?
I am using Excel 2007 on Windows XP. I have the following formula that I would like to move from the worksheet into VBA. I am having an issue with the “IF” statement within VBA throwing a compile error for “Expected: expression”. I am assuming it does not like the “IF” statement within the formula. I also provided a sample of the code that I was able to get working with a SUMIF statement but with only one condition.
View 2 Replies View RelatedMy sheet names are the dates of the month.. 07 Feb,08 Feb,09 Feb etc.. I want to sum up the values in a cell(for example L41) in every sheet from the beginning until the sheet that I am in (activesheet). The reason is I am trying to find the average value until that day of the month. What I did is;
=SUM('07 Feb:Sheetname()'!L41)/(SheetNum()-1)
But it doesnt let me use the Sheetname macro in the sum function. So what can I do to automize the function so that it will add all the values from the start(07 Feb) until the active sheet that I am in?
Is anyone aware of a LIKE style function in a formula where the formula would check some text and if it was similar to another text if would populate a chosen answer?
An example being asking the formular to look at Winterhur and Wintahur and as they are similar populating a chosen answer...
I've thought about wildcards but not sure it would work in this case
I want to evaluate Bessel functions as parts of a function in VBA. These functions are available in Excel not VBA. I tried to use range (). value to do this, i.e.
Function Bessel2(x)
Worksheets("Sheet2").Range("C1").Value = x
Bessel2 = Worksheets("Sheet2").Range("C2").Value
End Function
In cell C2, I enter the formula BESSELI(2,C1). But this did not work.
What I've got is various numbers (1-300) in column A, and what I want is a formula that will spit out a value (as assigned by me) for each number into column E. Like, I want Excel to find all the "1"s in column A, and show a "375" in column E in each row that there's a 1, and find all the "2"s and show a "376" in column E, all the "3"s, "4"s, "5"s and so on. I thought I'd be able to use the "IF" function, but apparently I can only use 64 different values in the formula, and I need 300.
Is there a formula that will do that? And, if so, how do I use it?
I have a range of cells that may contain several different abbreviations (text groups). I have used the COUNTIF function with * place in front of text to count cells that contain the listed text eg =(COUNTIF(G6:G39,"*HWD")) which has worked well and returns an accurate count of the cells that contain HWD. I have tried to do the same thing for a COUNT function that contains an IF function and no count is returned. When I remove the * from the formula a count is returned for cells that contain only the HWD text and not cells that contain HWD and other text. {=COUNT(IF(P9:P69="MCAW",IF(G9:G69="HWD",J9:J69)))}.
View 3 Replies View RelatedHow can I make the following formula work =(B3+C3+D3)*E3*F3? I need the
numbers in brackets to be multiplied by E3 and then that number multiplied by
F3,
I'm having trouble finding the correct way to use this formula. Basically, I need the formula to populate what is in another cell, minus any apostrophe's ( ' ) that are in the text. Sometimes the text contains the apostrophe and other times it does not. I just want whatever text is in the cell to populate, whether it contains an apostrophe or not.
Here is what I've been using so far, but it only produces a value if there is an apostrophe. If there isn't an apostrophe in the text, nothing populates.
=RIGHT(Input!G2,LEN(Input!G2)-SEARCH("'",Input!G2))
I'm just getting into functions, so please bear with me on this as I start to understand how they really work. I do have the VBA and Macros for MS Excel book which has been a great help!
Normally on this I would use a simple array formula to gather a total calculation from a timesheet, but in this case there can be numerous timesheets to add this up.
Hours would be recorded on a sheet with both a client and a task selected. I also have a summary sheet to show clients against tasks... adding all the hours from each timesheet.
If it were from a single Timesheet (possibly creating a single one of these for all sheets is an option?) I would be able to use the following array formula:
=SUM(IF((Timesheet1!$F$8:$F$22=C$5)*(Timesheet1!$G$8:$G$22=$A9),Timesheet1!$D$8:$D$22))
Timesheet1 Column D = hours
Timesheet1 Column F = clients
Timesheet1 Column G = tasks
Row 5 (starting Column C) = clients in summary sheet (across top)
Column A (starting Row 9) = tasks in summary sheet (down side)...........
I would like to have an IF statement which if true gives a result of a date plus 365 days but if false gives a prescribed day and month but uses a year from another cell.
I was trying something like this : IF(J5="y",E5+365,01/04/YEAR(E5)) Where E5 gives a start date of a project in a dd/mm/yy format and I would like this to give the same dd/mm/yy format.
Does anyone have any suggestions - the first part of the statement should be fine it is really the year element that I am having trouble with.
Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.
View 2 Replies View Related=StacServer|Windham! '40720'
This is the format of an external call that I am trying to run. I need to change the number (40720) based on the day of the month (i.e. if its the 5th, then use 40725.
I am attemting to use a function to do this and passing in the device name (Windham) and location (40720). Unfortunately, I have been unable to get a String to send back as a formula. My attempt was as follows :
= Location ("Windham", 40720, "D7")
Function Location(name, num, cell)
Dim psDay As Integer
Dim psLocal As String
psDay = Day(Now)
Location = (Range(cell).Formula = "=StacServer|" & name & "!'" & (num + psDay) & "'")
End Function
I need to do a series of calculations using the norminv function and some other things I'm unfamiliar with. Here is a description of what I need to do:
If you have Excel, you put in the daily odds, then get prob = 1/(1+odds), then zscore = norminv(p) and make the zscore negative if the odds were below chance. Then add up all the zscores and divide by sqrt(numdays), and convert back to probability with normdist(z).
In the formula =IF(" cell # "","") what does the "","" mean
View 4 Replies View RelatedI have a formula in a spreadsheet set up by someone else and it says. =HLOOKUP(C$30,J$2:$N$259,ROW()-ROW($2:$2)+1,). Can some one explain what it is doing in the bolded part? I understand basic vlookup and hlookups but not what this is doing. I have looked at the help but it only says that it returns the row numbers as reference but what does that mean. - and what does the "+1," and the comma at end mean.
View 2 Replies View Relatedif i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
What I need to do is basically use one sheet from my workbook as a 'template' for all the other sheets, but in a way that when I make a change to a formula on my 'Template' sheet all the other sheets based off of it follow suit. I need to do this because for 100000's of rows, spread across different sheets, I have product information populating Columns A-G, and their unique cost in Column H. I need to apply markups to each product (row), but all of these markups are standard across every product and thus every sheet. So in the perfect world I would be able to edit the formula on Sheet #1, and then have that change be reflected in every other sheet.
So let's say I have something like this on Sheet #1...
[URL] .....
Where Column C is a product of A & B...ie. "=A2*B2"
What I need to happen is on Sheet #2 is for it to somehow pull the same formula that is on Sheet #1 in Column C, but apply that formula to 'A2' & 'B2' on sheet #2. And I also want it to update whenever I change the formula in Sheet #1.
If it requires that I set up a template on a completely different workbook, that is perfectly fine as Sheet #1, my 'Template' Sheet, does not hold in actual product information.
Formula 1 : =IF(SEARCH({"spinning"},A2),"AUTOGEN")
Formula 2 : =IF(SEARCH({"typing"},A2),"Manual")
I want to combine above two formula's with "OR" function.
Cell A1 contains 012345678 ** Customer of 987654321 **
Cell B2 contains 087654321
My formula =MID(A1,FIND("of",A1)+2,10) works perfect if it has a "customer of...". How to revise my formula that when it gets to cell B2 and if there is no "** Customer of ..." it would leave it blank or zero rather than #Value!?
I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.
View 6 Replies View RelatedI have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.
in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.
So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3
Is there a Function or Formula that can store data from say cell $J$21 and store the result in L21 in the same worksheet and every time the data in $J$21 changes the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.
I am currently using the following formula:
=SUMPRODUCT(('Step 1_Data_Current Qtr'!T2:T10000=B2)*('Step 1_Data_Current Qtr'!C2:C10000<>"")*1)
...to count the number of instances in a data set where:
T2:T10000=B2 is counting records where the value in the cell is set to B2
and
C2:C10000<>"" is counting records where the value in the cell is not blank.
The current formular is working, but I want to change the formula so that the values in C2:C10000 is looking up specific names (in this case, physicians) and not just where the field is not blank. How can I change to lookup physicians in a specific list that may change, located in a separate tab from the main data set?
have this formula..
=SUMIFS(Sheet2!$D$2847:$D$3065;Sheet2!$I$2847:$I$3065;$B$6;Sheet2!$G$2847:$G$3065;$C$6;Sheet2!$B$2847:$B$3065;A14)
D2847 - D3065
includes Numbers..
lets say.. i want every cell counted which has in these range the first two numbers..
every cell has..
5052
5053
5054
4060
4050
so i count the entries which has 50.. so in total it gives a result of 3. Not summing them, just count the entries that has the first two numbers "50" in every row..
The Red Markings can be replaced, the rest should stay.. it is needed due different criteria.....
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
I need to write a formula in F35 that looks at C4 and if that cell has a # of 25 or greater it will then look at F4 and if that # is greater or equal to 95% it will return a count of 1 in F35. I know how to use the countif function but am stumped when I try to add in the function to look at c4 as a qualifier.
View 7 Replies View Related