This is a complicated thing I'm trying to accomplish and I only have a rudimentary grasp of Excel formulas and logical statements.
I need to check BE2 if it's blank. If it's not blank, leave it alone. If it is blank, I need to check S2. If S2 contains the name of 1 of 11 counties (ex.: "Marion","Pinellas","Orange","Osceola", etc.), I need BE2 to say "TBD". If it does not say one of those 11 counties, it needs to say "N/A".
I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.
ABCDEF
1S1S2
2REPORTDIST.CONT.CODEF1F2
31005S1B
410010S2A
510012F1B
610014C
710016F2A
82002B
92004S1B
1020010F2B
In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).
I'd like the following functionality in a spreadsheet I'm working on. Can a function/code/whatever be written to accomplish this? I know that the format below won't work; I simply wrote it that way to illustrate what I want.
If G6 = S, THEN B3+H6 and display total in S3; If G6 =V, THEN C3+H6 and display total in T3; If G6 = P, THEN D3+H6 and display total in U3. I have no idea how to do this myself (even after researching various sites) and I do not have the ability to write create VBA myself.
I have two columns of data, and a third column for which a value will be input as a reuslt of this macro. I seem to be having a problem with "Garage Attached" in AI2:AI10. The field in this column shows this value: Driveway Concrete Garage Attached 2 car. I want the macro to return the cell value on if there is the exact wording of "Garage Attached" in the AI2 field. This macro gives me that value, but also give the same value if nothing in present in the AI2 field.
Range("v2:v10").Select
For Each CELL In Selection
If Range("AI2").Value = "Garage Attached" And _
Range("W2").Value = "2" Then _
CELL.Value = "2"
Next CELL
I'm doing a spreadsheet analyzing cell phone costs. Basically what I would like is the following conditions to be met in a organized if statement.
Plan A -Cheapest up to 615 Minutes
Plan B- Cheapess from 616 minutes to 1599 minutes
Plan C- Cheapest from 1600 minutes to 2266 minutes
Plan D- Cheapest from 2266 minutes onward.
The test to determine the number of minutes is an average.
On my spreadsheet it is (k24+p24)/2.
Depending on that test, I need plan A, B, C, or D to show up.
Is it possible for an if statement to contain everything I just described?
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down
FA5 = option 1
FA6 = option 2
FA7 = option 3
FA8 = option 4
FA9 = option 5
FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
I'm currently using the formula in cell I43:
=AVERAGE(IF(I10:I420,I10:I42,""))
If any of these cells are blank I get the #DIV/0! error. If any are filled in then I get an average which is what I want. The problem is that I am trying to average mutiple sheets of these cells (I43) and if any sheet has this has the #div! error it won't calculate.
I have divided my data into 2 sheets. They are called 1) Sheet1 2)Sheet2
Sheet1 -> Sheet with all my Data
Sheet2 -> Sheet with calculations (formula's)
Know what I need to do is the following
1) As soon as someone starts enters a value in Sheet1 (eg. as soon as data is entered in Column A in Sheet1) VBA captures that line # and then in Sheet2 it copies the formula from the ROW ABOVE itself and then inserts a new ROW (corresponding to same row where value was entered in Sheet1) and copies that formula into that row (FORMULA are from COLUMNS B to J.
I need is Sheet2 to update itself and perform calculations when new line of data is started in Sheet1 and to INSERT a new Row so ALL other calculations BELOW it are updated as well. (Eg. Averages, Additional Calculations)
I have a lot of calculations in Sheet2 that correspond to Sheet1. The new ROW in Sheet2 has to look at where the data in Sheet1 was entered because I have a bunch of Averages at the bottom of Sheet2. I need all that to automatically update. So VBA has to insert a NEW row and then copy the previous row's formula in order for that to work.
I am having some problems converting a working formula in excel into VBA code. The working formula is as follows;
=SUMPRODUCT(--(ReasonList=$B51),--(AgeList=0),--(CreatedList>=$D$47),--(CreatedList
I am creating a spreadsheet where I am able to track the events that our clients have been invited to AND in a separate column, I need to track who actually attended in order to get a total of how much we have allocated on each client, AND how much we actually spent, to date, on the client.
Through the help of some ExcelForum experts, I was told a pivot table would be my best bet.
Please see attached for an example of the columns on my current spreadsheet.
I need to give each event a value, so if “Jim” Says “Yes” to Golf, “yes” will equal “$400.00”. But if Jim says Yes to the Golf ($400.00), Spring Event ($100), and Executive retreat ($1400.00)” The “Yes” in those three columns will be calculated in a side column ($400+$100+$1400). Which means that the “Yes” must have a different value assigned to it, depending on which column it falls under.
The other important functions that my spreadsheet should have are as follows:
1) I need to be able to manually enter an amount, if need be, instead of a “yes’, and have the total be calculated all the same, in the total column.
2) As mentioned, I need to be able to have one column that keeps the total of how much we have allocated on that specific client (if we invite them to 4 events, and the total of the four =$2000.00, that would be our allocated amount)… but I need a separate column that holds the amount spent on the client to date (If we have “Yes” next to Golf, spa, and trip, meaning we have invited him for the three events and are prepared to spend $1000 on him, but he only accepted to the Golf, I need one column to keep the total $1000.00 and a side column to now read “250”, for example. And if in a few months he accepts to the Trip, I need to be able to track that he accepted, and the “Total Spent” should now be “750” while the “total Allocated amount” should still be $1000.00.
Is it possible to create a pivot table that meets my needs?
I built a excel file to make some calculations for forex. I started with four prices for each day (open, high, low, close). I am calculating for four price ranges for the next day. That is ok. Another part of the sheet compares results to the predictions. From the four prices that come in everyday I calculate average over 8 weeks, over the last week, and individually for each day of the week. Here it is: TESTBook1.xlsx
I wanted to make a surface graph, but I have no table. I have no formula to use to make a table because the calculations are all over the sheet. I tried tracking through the cell where my "total" is put out, but it is a very long and involved trip. Even if I started a new sheet just to make a table, I can't get my head around the calculations.
I have two cells where I put in my adjustment numbers. In the first cell I put in a number from 0.00000 to at most 0.30000. The second cell I put in a number from 0.00000 to at most -0.30000. I then look at a third cell for the highest output. I got tired of typing in numbers, so I put sliders which works better.
So my table would have 0.00000 to 0.30000 on one axis and 0.00000 to -0.30000 on the other. The formula = ????
I'm in the process of decoding a mass amount of data that will be incoming shortly. The data will be formatted as shown below. What I would like to do is have a macro that between two full seconds (for example 10:11:48:0 to 10:11:49:0) would find the mode of the RSSI values, paste that with a time value that is in the general excel format. (I.E. 10:11:48 = 0.42484). When all is said and done, what I have access to should look like the second string of posted data. I can get the time cleaned up on my own, but I'm having trouble with the loop macro that will find which cells to use for the mode.
View 12 Replies View RelatedI'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.
The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.
For clarification Work Center would be the abbreviation (letter code) about the dates.
i have a spreadsheet that has 300+rows,and the details below
b2 is the description and date, b4 to b29 are the item name
c3 is total sales volume between c4 to c29,
then another section starts from row 30 till row 57, row 58 till row 85 etc till the end (27 rows for each section)
i need to search the minimum value in each section and highlight it as red.
i know i can search the minimum value by using MIN(b2:B29), but how can i repeat it for each section?
I am trying to do a vlookup that returns the last and last but one value in a row.
If it were simple my vlookup would be
=VLOOKUP(A11,Comments!A1:F22,XX,0)
=VLOOKUP(A11,Comments!A1:F22,XX-1,0)
Where XX is the last filled column on the row.
I have attempted the following but can't figure it out.
Option Explicit
Public Function lastcomment(cust As String)
Dim Loc As Range
Dim ComDate As String
Dim Comm As String
Set Loc = Sheets("comments").Range("a1:a10000").Find(cust)
ComDate = Loc.End(xlToRight).Offset(, -1).Value
Comm = Loc.End(xlToRight).Value
lastcomment = ComDate & " : " & Comm
End Function
What have I done wrong?
Currently I have developed a Time sheet for employees however I am having a lot of trouble with the shift differentials right now.
First, I used an If function to say If(B7="E",B6,0) E would be the evening shift and of course this works fine if everyone worked an perfect Evening shift within the time limits however, they dont.
I'm wonderin if it is possible to have one, or multiple formulas that can do the following:
First shift differential is from 14:00-23:00
Second shift differential is from 22:00-07:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:00-23:00.
Third shift differential is a weekend one which I have figured out it's rather simple to just have a IF function for that.
Start time is B3
End time is B4
Breaks is B5 (However, Breaks is subtracted from B6)
Hours worked for the day is B6
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
Find the attached excel sheet : Example Statistic.xlsx
I am trying to calculate the crew hours from a database I created .
I entered the sumifs formula but i entered many argument .
The block time should be calculated based on the month and to calculate only for specific name for each crew .
The formula should be involve in the colorful cells only ...
I am trying to automise a workbook entry that Allocates Unique Invoice numbers depending on the job type and so that each job is only allocated one Invoice Number. Column A contains the Unique identifier, the customer name; Column C contains a drop down box with the 3 options for invoice type; "Monthly", "One off" and "Job"; Column D contains the Invoice number.
Assuming data has been entered up to row 10 I am trying to write an IF formula in column D, with the 3 conditions:
IF C11="Monthly" then D11= Vlookup for Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice Number in column D that belongs to the same customer name, and D11 is left blank
2nd condition
IF C11="Job" then D11= Vlookup the Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice number in column D that belongs to the same customer Name, and D11 is left blank
3rd condition
IF C11="One off" then D11= one value above the maximum (D1:D10)
Example:
Name (unique ID)DateInvoice TypeInvoice Number (IN)Explanation
White 1/2/14One off100 Initial (IN)
Brown 2/2/14Monthly101 Highest IN + 1
Smith 3/2/14Job 102 Highest IN + 1
Smith 4/2/14Job Blank, as it is the same Job as on 3/2/14
Brown 5/2/14Monthly Blank, as only gets invoiced once a month, with one IN
Peters 6/2/14One off103 Highest IN +1
Brown 8/2/14Monthly Blank, as only gets invoiced once a month, with one IN
I have attached the dataset. I need the data table to show the total numbers per year with the % change year on year, A column in this table of a year end projection figure for 08/09 and the % change. And finally add a line graph to show each complete year and the 08/09 projection as a comparator.
View 5 Replies View RelatedI am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
Example.xlsx‎
So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:
[CODE]
=IF(A15="","",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,
(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)
=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))
[CODE]
I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.
In column B I have a list of blank cells and cells that contain values. I want a macro to search for 'Jobs' and if it finds this word in column B then delete the entire row where it is find and also delete the row below it if column B is blank and also delete the row below again if column B is blank (up to 8 rows below where the word is find at most will be blank)
When it eventually finds a cell that is not blank, then exit sub.
H4 is a date/time stamp I have saved as a macro. Returns 12/28/2011 10:47:00 AM.
I4 is the same macro and returns 12/28/2011 10:48:00 AM
J4 calculates the difference between the two (I4-J4), but only recognizes business hours and business days (Monday-Friday, 8:00 am to 5:00 pm)
I only want J4 to calculate if I4 is NOT BLANK.
These are in a table so J4 is trying to calculate when there is data in H4, but not I4, and returning a large number like 981583.22
When I try to apply IF(ISBLANK) logic to the formula in J4, I get an error that it exceeds 255 characters, even though it works fine if I am not trying to put the IF(ISBLANK) logic in.
Here is the formula in J4. I want it to automatically calculate if there is data in I4. Otherwise, I want it to return 0.
=IF(AND(INT(H4)=INT(I4),NOT(ISNA(MATCH(INT(H4),HolidayList,0)))),0,ABS(IF(INT(H4)=INT(I4),ROUND(24*(I4-H4),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(H4+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(I4))-
(H4-INT(H4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(I4-INT(I4)))-24*DayStart)+
(24*DayEnd-(24*(H4-INT(H4)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
I want a formula to do this... (x1*z1+x2*z2+x3*z3+....+xn*zn) where n is finite.
x1=a1
x2=a1+a2
x3=a1+a2+a3
xn=a1+a2+a3+...+an
z1 = 1.05^1
z2 = 1.05^2
z3 = 1.05 ^3
zn = 1.05^n
is this possible?
here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
I have been trying to create a formula that will save me DAYS of messing around at work.
What I am trying to achieve is to have a sequence of numbers as follows:
BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.
This needs to be repeated for all letters to BNA01I09.
Then this sequence needs to be repeated to BNA12.
The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09
I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.
So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.
If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)
The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"
Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)
apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.
I have a very complicated table that has a bunch of links to other cells on the sheet. I would like to duplicate this table onto another sheet in my workbook. However, when I try to do that, the links also change when I paste it on the new sheet. Is there a way I can keep all of the links to the original page after I have pasted the table on the new sheet?
View 4 Replies View Related
