Usually Array Formula But Function
Oct 10, 2008
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)...........
View 9 Replies
ADVERTISEMENT
Feb 4, 2014
I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.
The array formula I am entering is: [Code] .........
View 4 Replies
View Related
Jun 15, 2014
If the data looks like this:
10
7
6
10
9
9
3
What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.
View 5 Replies
View Related
May 19, 2009
Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly.
View 8 Replies
View Related
Feb 27, 2014
The following formula is all on one line. I want to do indirect for file name and tab in all instances it shows up. (I want to have the filename in one cell and sheet/tab name in another cell to reference the indirect to.)
How would I use indirect in the formula to reference the cells?
=INDEX('[Feb 2014.xlsm]TRADEDB'!$A$75:$Q$131,
SMALL(IF(('[Feb 2014.xlsm]TRADEDB'!$A$75:$A$131=$H$8)*('[Feb 2014.xlsm]TRADEDB'!$G$75:$G$131=$I$8),
ROW('[Feb 2014.xlsm]TRADEDB'!$A$75:$Q$131)),ROW('[Feb 2014.xlsm]TRADEDB'!2:2))-74,1)
View 5 Replies
View Related
Jun 18, 2007
I have dates & times in column A1:A20. In B1:B20 I have the corresponding temperatures for each date. I have set up the following dynamic ranges to refer to these ranges.
DateRange refers to A1:A20
TempRange refers to B1:B20
I have also made a user defined function that will determine if a date/time is between two times. Eg., If 21/05/06 07:30 is between "07:00" and "17:00". This function isn't concerned with the date, just if the time falls between the start and end times.
Function BetweenTimes(dDate As String, dStartTime As String, dEndTime As String) As Boolean
dDate = CDate(dDate)
dStartTime = CDate(dStartTime)
dEndTime = CDate(dEndTime)
BetweenTimes = False
'If the end time is before the start time, see if date/time falls between start and end..........
View 9 Replies
View Related
May 8, 2013
I need to find a way to find an array in a tab to use for an index/match function I have.
this is what i would normally use: =index(tab_array,match($a2, tab!$a$1:$a$1000,0),match($B$1,tab!$a$1:$zz$1,0))
This formula would usually work fine when I know that within the tab, the array in which the row match is searching doesn't change. However, that array may change in the future, to say column H, without my knowing as it is a database that gets automatically populated from an upstream system.
Is there a way to search for an array within an index/match?
View 1 Replies
View Related
Aug 29, 2013
I am looking to be able to alter my table_array section in VLOOKUP to adjust in date.
exampe: =vlookup(A4,'[Daily report - August 25.x;sx]Facilities'!A4:AY100,84,FALSE)
and I want to be able to change the August 25 -> August 26 repeating so that as I drop the next date in it will update to the correct tab.
I have the dates above so if I could somehow just the date to another cell instead that would work as well. I just do not know how.
Essentially I need to grab data from a separate workbook everyday and compile it to one master list.
View 3 Replies
View Related
Mar 3, 2014
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............
View 1 Replies
View Related
Apr 7, 2009
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.
View 6 Replies
View Related
Nov 6, 2013
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"))}
View 5 Replies
View Related
May 18, 2007
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?
View 9 Replies
View Related
Jun 30, 2006
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
View 4 Replies
View Related
Aug 8, 2012
I use a existing code and want to add the Concatenate in the code.
VB:
Sub Subtotal()
Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long
Worksheets("INVOICE_hulp").Activate
Range("Q1").Select
[Code] ....
The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?
View 9 Replies
View Related
Sep 3, 2012
I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.
In Module1:
VB:
Function func1(ByRef arrayA())
Dim arrayB(8)
For k = 0 To 7
[Code].....
How does the resultant arrayB get populated onto the spreadsheet?
View 5 Replies
View Related
May 2, 2014
I want to use the countif function for a certain array. The range is set by another cell which is made up out of a percentile of an entire row.
The problem is is that excel doesn't see the value it displays so i continuously get a value of 0 in the countif cell. If i fill in the range by hand, which is exactly the same range as the outcome of the percentile the countif cell does give the correct value.
So in short the problem is i guess that the countif cell does not recognize the value because this value is made up by a formula.
View 8 Replies
View Related
Jan 24, 2007
I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.
In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.
View 11 Replies
View Related
Oct 15, 2008
Is there anyway I can do the below formula without it being an array formula?
View 2 Replies
View Related
Feb 21, 2014
I am trying to write a code for simulating a stock price using geometric brownian motion. I have the function part down as follows:
Public Function SimStock(Initial_Stock_Price As Double, _
Expected_Return As Double, _
Volitility As Double, _
End_Time_Days As Double, _
Number_of_Steps) As Double
SimStock = Initial_Stock_Price * Exp((Expected_Return - Volitility ^ 2 / 2) * (Number_of_Steps / End_Time_Days) * NRnd2() * Sqr((Number_of_Steps / End_Time_Days)))
End Function
Now what i need to do is to apply this to an array. so that when i run the sub it will simulate prices from today up to some number of days that i specify. This number of days will be equal to "End_Time_Days" which is part of my function. Furthermore the simulation has to be such that it uses the above formula for the first entry but then replaces "Initial_Stock_Price" with the result of the preceding entry in each subsequent entry.
View 1 Replies
View Related
Oct 11, 2005
Is it possible to submit the MEDIAN() function as an array (ctrl + shift + enter)?
I.e., I would like to submit a function similar to this SUM() function:
{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
However, when I submit
{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
it does not give me the intended result.
View 6 Replies
View Related
Feb 27, 2012
For some reason my array called "PriceSum" is not getting load with values or not being summed. I am not sure if it is because I am trying to load a value with a decimal in it or not.
For logic purposes:
lastrow = 2
Stock = Banking
Cell "W2" = 5
Cell "X2" = 71.84
Cell "U2" = Bought
Cell "V2" = Banking
Code:
Private Sub Purchase_Click()Dim MyArray As Variant
Dim x As Integer
Dim t As Integer
Dim lastrow As Integer
Dim QuantityArray(0 To 1000) As Variant,
[Code] ...........
View 9 Replies
View Related
Jan 9, 2014
I have a big macro that runs whenever there is a change made. I'm not gonna link it all because I don't think that would be useful... This macro does:
1. Disables Events.
2. Removes Protection
3.Calls some macros that check a few values and change a few cells.
4. Activates events.
5. Hides or shows ranges of cells depending on the results of the called macros.
6. Reactivates protection.
As well as the cells the called macro changes, there is a column of True/False results stretching from AE16 to AE491 with several blanks in the list as well. (All of this is decided upon a different macro that isn't linked with the change.) I want a loop that will read the True/False column and if it finds a false, make 8 buttons invisible.
I tried putting this code between Step 5 and 6 of the macro.
Code:
Dim J As Integer
J = 16
Do
[Code].....
This isn't working at all... I was thinking if there was gonna be an issue then it would be the opposite where it stops the buttons appearing all together but even when there are falses and I am changing things it doesn't seem to make a difference.
View 3 Replies
View Related
Jun 8, 2007
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View 9 Replies
View Related
Feb 27, 2007
I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic:
Public Function bondValue(years As Range, q_factor As Range, z As Double)
Dim nRow As Long, nCol As Long
Dim bondPort As Range
nRow = q_factor.Rows.Count
nCol = years.Columns.Count
sumTau = 0
View 6 Replies
View Related
Jan 15, 2008
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Sub General() 'Makes general calculations
Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatırım Nakit Akımı
Dim FIRR(49) As Variant 'Yearly FIRR
Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet
End Sub
View 9 Replies
View Related
Jun 20, 2008
I have the following assignment to do, problem is I'm new to arrays and functions in VBA and nothing is working for me, i get errors no matter what i try, (for example i don't know how to feed the array to the function, and it won't let me write a range to an array)
5.A) Write a function that takes a 2D array of doubles, a single cell range, an optional cap and an optional floor.
B)Write the array on to the sheet with the single cell range parameter being the location of the top left of the array on the sheet. When writing the array to the sheet, for each value in the array where it is below the floor put the floor on the sheet, where the value is above the cap put the cap value on the sheet, otherwise display the original value. The input array should not be modified.
All code should be what you consider production quality.
Dim topleft As Range
array1 = Range("B8:C18").Value
'array1(1, 1) = 2
'array1(2, 1) = 3
'array1(1, 2) = 6
'array1(1, 3) = 9
'array1(2, 2) = 4
'array1(2, 3) = 5
View 9 Replies
View Related
Jan 25, 2013
How can I obtain the array values from a function?
For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
Redim datosColumnaIncio(Len(strSplit) - 1)
For iTemp = 1 To Len(strSplit)
datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1)
Next
End Function
So I want to know if its correct like that:
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
[Code] .....
So when I call the function:
VB : MsgBox splitText("F4")
It throws an error message :s what is wrong or how can I get the array values of a function ???
View 2 Replies
View Related
May 12, 2014
I am trying to subtract two values, which are calculated from arrays. Here is my actual code:
[Code] ......
If I remove the "-", I get the sum of both arrays as expected. If I include the "-", I get the "#VALUE!" error message.
View 4 Replies
View Related
Apr 22, 2009
Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:
View 14 Replies
View Related
Aug 7, 2009
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....
Test Data starting at cell A1:
View 14 Replies
View Related
