Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Array Functions in Microsoft Excel
|
|
|
|
|
I need to use VLOOKUP for a spreadsheet I'm running and need to use the value in a cell as the name of the table array for VLOOKUP.
Is this possible ?
Is this possible ?
I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
{=Mode(if(and(B5:B8219=B5, and(C5:C8219=C5, and(D5:D8219=D5, and(E5:E8219=E5)))) N5:N8219), "")}
I've tried on it a hard but still unable to get solution ....
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. In attached sheet i've defined problems in a very specific manner ........
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. In attached sheet i've defined problems in a very specific manner ........
Hello all, hope you are well!
My problem:
I have a mastersheet which uses TRANSPOSE() as an array function to return values from another workbook. The transpose has an INDIRECT in it so it knows which file t look at and which sheet within that file it should look at.
The issue is that, unlike paste special > transpose, TRANSPOSE returns blank cells as 0. The data it is looking at contains both blank cells and 0s, so I want to be able to return both. I tried an IF(ISBLANK()) but couldn't get this to work in the array.
Two spreadsheets are attached. I am not tied to TRANSPOSE so anything you think would work would be great.
Thanks,
Tom
My problem:
I have a mastersheet which uses TRANSPOSE() as an array function to return values from another workbook. The transpose has an INDIRECT in it so it knows which file t look at and which sheet within that file it should look at.
The issue is that, unlike paste special > transpose, TRANSPOSE returns blank cells as 0. The data it is looking at contains both blank cells and 0s, so I want to be able to return both. I tried an IF(ISBLANK()) but couldn't get this to work in the array.
Two spreadsheets are attached. I am not tied to TRANSPOSE so anything you think would work would be great.
Thanks,
Tom
Hello forum,
I am trying to use the Find formula to test if a particular value matches one of the elements in a named array.
I have created the array in the Name Manager: "SheetLetters" = {"SU","TR","SX"}.
If I put this formula in a cell, =FIND("SU",SheetLetters,1), it returns 1.
but if I use this formula, FIND("SX",SheetLetters,1), it returns #VALUE!
What I would like is for a number to be returned if the search string matches any of the values stored in the array. Any advice would be much appreciated as always.
thanks
I am trying to use the Find formula to test if a particular value matches one of the elements in a named array.
I have created the array in the Name Manager: "SheetLetters" = {"SU","TR","SX"}.
If I put this formula in a cell, =FIND("SU",SheetLetters,1), it returns 1.
but if I use this formula, FIND("SX",SheetLetters,1), it returns #VALUE!
What I would like is for a number to be returned if the search string matches any of the values stored in the array. Any advice would be much appreciated as always.
thanks
Hi there, I'd like to know how the formula below is equal to the anwer below?
I'm not sure what the Substitute function does in the query below? I know the outer query is an array.
Problem:
Value of G2: 179736
Formula: =SUM((LEN(SUBSTITUTE(G2,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})
Answer: 237
I'm not sure what the Substitute function does in the query below? I know the outer query is an array.
Problem:
Value of G2: 179736
Formula: =SUM((LEN(SUBSTITUTE(G2,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})
Answer: 237
Is there anyway I can do the below formula without it being an array formula?
Code:
The reason I ask is because everyday I insert new rows into the 'Data' sheet so I can paste in the previous days data.
However this causes the ranges in my formulas to change, like this:-
Code:
I would be able to fix this by specifying the ranges as whole columns (e.g. A:A), but I am under the impression that this cannot be done in an array formula. Is this correct?
Any solution on how to get the ranges to stay the same or how to make the formula not an array would be much appreciated.
Thanks
Mark
Code:
=(MIN(IF('Data'!$A$5:$A$50000=$C$8,IF('Data'!$I$5:$I$50000=C9,'Data'!$AC$5:$AC$50000))))
The reason I ask is because everyday I insert new rows into the 'Data' sheet so I can paste in the previous days data.
However this causes the ranges in my formulas to change, like this:-
Code:
=(MIN(IF('Data'!$A$50:$A$50045=$C$8,IF('Data'!$I$50:$I$50045=C9,'Data'!$AC$50:$AC$50045))))
I would be able to fix this by specifying the ranges as whole columns (e.g. A:A), but I am under the impression that this cannot be done in an array formula. Is this correct?
Any solution on how to get the ranges to stay the same or how to make the formula not an array would be much appreciated.
Thanks
Mark
Hello!
I have a list with numbers and i can return the first and the next mode number with this formulas
=mode(A1:A11) and {=mode(if(A1:A11<>3;A1:A11))} as an array.(if 3 is the first mode number)
The problem is that my list needs to have a range form A11:A2000 including blank cells and the array formula above returns a zero because of the blank cells.
Is there a way to make the array formula work despite the fact of having blank cells?
I have attached a small example of my data
Thank you very much!
I have a list with numbers and i can return the first and the next mode number with this formulas
=mode(A1:A11) and {=mode(if(A1:A11<>3;A1:A11))} as an array.(if 3 is the first mode number)
The problem is that my list needs to have a range form A11:A2000 including blank cells and the array formula above returns a zero because of the blank cells.
Is there a way to make the array formula work despite the fact of having blank cells?
I have attached a small example of my data
Thank you very much!
I am not getting the expect result when I multiply two arrays
Array1
1 5 2 4
1/5 1 1/2 1/2
1/2 2 1 2
1/4 2 1/2 1
Array2
0.5115
0.0986
0.2433
0.1466
Expected Result
2.0774
0.3958
0.9894
0.5933
My result
2.0774
2.0774
2.0774
2.0774
I am sure I am entering them as array formulas but I am not getting the right answer. I am sure this is something simple. Thanks for your help.
Array1
1 5 2 4
1/5 1 1/2 1/2
1/2 2 1 2
1/4 2 1/2 1
Array2
0.5115
0.0986
0.2433
0.1466
Expected Result
2.0774
0.3958
0.9894
0.5933
My result
2.0774
2.0774
2.0774
2.0774
I am sure I am entering them as array formulas but I am not getting the right answer. I am sure this is something simple. Thanks for your help.
Hi,
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
Hope someone can help.
Thanks,
FellowExcellor
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
Hope someone can help.
Thanks,
FellowExcellor
I've declared a User Defined Type as follows in Module 1:
Please Login or Register to view this content.
Then inside a sub some array variables are declared as follows (in the same module):
Please Login or Register to view this content.
These arrays are then populated with data. I now need to pass these arrays to another function that will combine some of the relevant data from each of the arrays and return me another array of the same UDT type e.g.:
Please Login or Register to view this content.
However; There is some problem somewhere?
I get the following error message:
Compile error:
Only user-defined types defined in public modules can be coerced to or from a variant or passed to late-bound functions.
Any ideas what's wrong or how to do this?