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?
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies View RelatedH4 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 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 want to set formula to cell S1 via vba.
This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!$A:$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$D:$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$G:$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$J:$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$M:$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$Q:$R;2;0);I7))))))
How I can find instances of text strings containing two colon characters separated by any two (arbitrary) characters?
So in other words, "xxxx:xx:xxxx"
If I use :
Code:
SheetName.Cells.Find(What:=":**:", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
...I get a return of any strings containing two colon characters (regardless of the number of characters in-between) - because obviously the asterisk(s) denote any text whatsoever.
How do I specify a finite and exact number of characters between the colons when searching?
I have a long list of data: a unique number identifier that needs to be grouped with text. Right now they are in two separate columns. I would like to have two large columns on a page, as if you were looking through a phone book. I have been trying to get this list to load into a finite number of rows before it breaks into another column on the same page. Then moves to the next page, so about 30 rows that are set 8 rows down on a page to account for company letter head.
I haven't paired the text and numbers together at all. But I haven't been able to find a way to have them move in a way that maintains their correspondence. I have gotten my numbers to move into the row column set I have wanted but not the text.
[LETTERHEAD] {1-7 rows}
1| Shims 31 |Shims 2
. .
. .
. .
30 Bearing Cover 60 |Mechanical Seals {Then it moves to the next page within a certain space. But at this point I would like it to just have this format}
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
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
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 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
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
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.
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).