IF Statement - Calculate Due Dates Based On Assigned Rank
Aug 1, 2014
I am using the following formula to calculate due dates based on the rank i assign an issue.
Note: a similar formula is used for the due date of section one, but if it is not complete, i do not want this formula to kick in a determine the due date for section 2. The formula itself is working fine, but is giving a 'due date' of 1/30/1900. I was wondering if there is a way to not display anything if it has no number to calculate from.
=IF(ISNUMBER(SEARCH("S",D11)),F11+30,IF(ISNUMBER(SEARCH("B",D11)),F11+30,
IF(ISNUMBER(SEARCH("A",D11)),F11+30,IF(ISNUMBER(SEARCH("QNM",D11)),F11+30,""))))
View 4 Replies
ADVERTISEMENT
May 20, 2007
I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.
Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?
The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).
View 9 Replies
View Related
Jul 5, 2014
I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A
B
C
D
E
1
0
0
20
5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
View 1 Replies
View Related
Sep 25, 2008
location solved? issue employee room 1 ok issue 1 employee 1 room 2 issue 2 employee 2 room 3 issue 3 employee 2 room 4 ok issue 4 employee 1 room 5 ok issue 5 employee 1 room 6 issue 6 employee 1 room 7 ok issue 7 employee 2 unsolved issues room 1= solved issues room 1= unsolved issues employee 1= solved issues employee 1=
what formula could I use to calculate the solved/unsolved issues assigned to a room or to an employee? I use an autofilter on this date, so that I can only show unsolved issues.
View 2 Replies
View Related
Jun 29, 2009
I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..
View 9 Replies
View Related
Sep 11, 2013
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
View 1 Replies
View Related
May 12, 2009
I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
View 5 Replies
View Related
May 15, 2009
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.
So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View 6 Replies
View Related
Aug 6, 2014
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
Link: [URL] ....
View 1 Replies
View Related
Jun 10, 2014
I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.
But I have now experienced several times, that my buttons don't keep their assigned macro.
View 5 Replies
View Related
Nov 26, 2007
I am facing a problem with WorksheetFunction.Correl
I got information, that matching class properties of Worksheetfunction is impossible.
This is my first experience with macro's and I hardly know how to deal with this problem.I got some excel file from my friend and tried to do this in the same way.
Macro was supposed to calculate the Pearson's coefficient of correlation for orders from 1 to 100 of autocorrelation in order to calculate Q Box-Pierce statistic.Data is prices of stocks for some bank and correlation coefficient is calculated for simple returns on stocks.
View 9 Replies
View Related
Jun 21, 2007
I have a spreadsheet with a row for a company name, their offer price underneath that, and a 1,2, or 3 underneath that. In a seperate tab I have the offer prices in order of rank with the company name next to the corresponding price using the Index function. I also need to be able to color code the background of the offer price based upon the 1,2 or 3 associated with each name. I am new to the Index stuff so I need some help putting this one together.
View 3 Replies
View Related
Mar 22, 2014
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
View 9 Replies
View Related
Dec 20, 2011
I have folders with different assigned folder icons based on the content in the folders.
I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.
How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?
View 9 Replies
View Related
Nov 8, 2006
I need to know how to calculate totals from a spreadsheet in conjunction with an IF statement.
I have a columns A & B below, i would like to calculate the numbers in coulmn B only if they match a specific criteria in column A.
For example: I want calculate the total of the numbers in coulmn 'B' only if column A is a '1'.
AB
24
33
12
21
35
13
22
View 10 Replies
View Related
Dec 7, 2009
I have a situation where an asset-based fee charged to a client will be reduced (20% discount -
e.g., pay 80% of the fee) if below a set amount (250000000,(+B6-250000000)*0.00075+287500,IF(B6>100000000,(B6-100000000)*0.001+137500,IF(B6>50000000,(+B6-50000000)*0.00125+75000,+B6*0.0015))))>24000,IF(,(+B6-500000000)*0.0005+475000,IF(B6>250000000,(+B6-250000000)*0.00075+287500,IF(B6>100000000,(B6-100000000)*0.001+137500,IF(B6>50000000,(+B6-50000000)*0.00125+75000,+B6*0.0015)))),24000)*0.8
How can I rewrite this formula to capture.
View 9 Replies
View Related
Feb 23, 2010
I have a workbook with two tabs: Results (containing exam results) and Percentile (containing percentile rankings based on scores). There are different rankings for different exams.
I need the percentile ranking returned (on the Results tab) based on the exam result for each student. e.g. for exam M111 a score of 36 would return a ranking of 95, a score of 18 would return a ranking of 35, etc. I've attached a sample workbook.
View 4 Replies
View Related
Oct 14, 2011
Sheet1 *BMNO1GenderAge GroupTotalRank2Male6-72103*3Male6-71437*4Female6-71294*
I will have 200+ records similar to the above. I would like a formula that ranks the totals. The Males in the 6-7 Age Group would be in 1 Ranking while the Females in the 6-7 Age Group would be in another.
I prefer a formula over a macro. My original thought was =rank(n2,offset(?,?,?,?,?),1) but alas I'm missing something.
View 9 Replies
View Related
Oct 6, 2009
i have a invoice number column and date column and profit column for each job that they do. they may do several jobs in one day. what i want to know is how much they make total for the week. so in a separate column i want a weekending "such and such date" profit total
then i can have this for each person then i can just add up each persons week ending column to see how much i have made for this week. but i only what it to add up the numbers in a set range of dates.
for instance in the week ending 9/4/09 column it should only add profits that go from dates aug 30 to sept 4. and in weekending column 9/11/09 it should only add up profits corresponding to dates between 9/7/09 and 9/11/09
View 4 Replies
View Related
Mar 6, 2012
I am trying to write an IF statement. The IF statement should calculate BONUS for an employee GREATER THAN 50 YEARS OLD.
View 4 Replies
View Related
Dec 12, 2013
how to rank values in one column based on criteria in another column?
For example: How do I rank Cells in Column A that only contain the text "AL" in column B?
COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL
View 5 Replies
View Related
Feb 12, 2009
See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.
View 2 Replies
View Related
Oct 25, 2007
I need to return the project codes for the project with the top 5 sales volumes within each Region for each product. If say, I have the following relevant columns (Project, ProdCode, Region, Sales) in columns A:D
View 9 Replies
View Related
Aug 10, 2014
I was wondering if this can be done we have a monthly award program so if a date was entered for that drive on this month it would = no if blank =yes but needs to reset each month?
=IF(AND('Driver'!A51:A1000)="",IF('Driver'!A51:A1000>='Driver Bonus'!A3,"YES","NO")) This brings back a #value! error
=IF(AND('Driver'!A51<=A3,'Driver'!A51>=A3),"NO","YES") this works but only for that cell i need to also recognize cells A51:A1000
=IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<A3+1,IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<>A3,"YES","NO")) this also works but only for 1 day
View 4 Replies
View Related
Apr 5, 2013
I have different dates in column A, let's say:
10/26/12
11/1/12
11/15/12
2/12/13
I want to say that if the date is <= 12/31/12, put "LEASED IN 12", othwerwise put "LEASED IN 13".
My formula:
=IF($A1<="12/31/12",'LEASED IN 12","LEASED IN 13")
I get only 'LEASED IN 12".
View 11 Replies
View Related
Feb 18, 2014
I have data, which has an oil well that has had sections of its pipe perforated.
This has all happened on different days throughout the wells life.
I need an equation that says TRUE if the Perforation is the Most Recent for that well.
But I also need it to say True at different Top and Bottom Depth intervals throughout a specific well.
(for example: the last row in this data. Says TRUE even though there are more recent dates in this well.
This needs to say true because this is the most recent perforation in the top and bottom depth interval 9526 to 9536.
The other most recent perforations happened in shallower intervals, ie. 9456 to 9480.
So I need something in the equation that will say true to the most recent start date, if that top and bottom depth interval has not already been selected as true.
I need to have a column that says True IF the start date is the newest (most recent) for each individual well.
Easy enough.. But. I also need this to say True multiple times at different dates for each well.
For example below. it says true on 7/22/2001 three times and then True again on the last row on 10/14/1992.
It has to say true there because this is the most recent perforation that Top_Depth and Bottom_Depth interval.
WELLNAME
TOP_DEPTH
BOTTOM_DEPTH
START DATE
IF most recent Perf
RA-0001
9380
9395
10/14/1992
FALSE
[Code] ......
View 9 Replies
View Related
Feb 4, 2009
IF statement?
The error keeps stopping at the "6 for the date 6/30/2008....
View 9 Replies
View Related
Mar 12, 2014
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
View 3 Replies
View Related
Oct 29, 2009
I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.
View 2 Replies
View Related
Jan 24, 2014
I have date data in Column O, which appears like. At any given point in time, i would have such date data for 5 to 10 days, and I want to just assign 1 to first date, and next date to 2 and so on.
What formula (can i use RANK formula, and how) I can use to get the desired result. SEcond part of the query may require macro, so will ask later.
Basic Data Desired Output
Column O Column P
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
22-Jan-13 3
22-Jan-13 3
22-Jan-13 3
22-Jan-13 3
26-Jan-13 4
26-Jan-13 4
26-Jan-13 4
26-Jan-13 4
View 1 Replies
View Related
