Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to use an array or matrix to return text vs. numeric values
=AVERAGE(IF('Entire Org'!$C$4:$C$4505='Employee Database'!$F1,IF('Entire
Org'!$D$4:$D$4505='Employee Database'!$G1,IF('Entire Org'!$E$4:$E$4505='Employee Database'!$H1,'Entire Org'!$I$4:$I$4505)))) I am using the above array/matrix to successfully return a numeric value. Now, I would like to use a very similiar formula (fields, columns) to return non-numeric values. I have tried to substitute average with cell (and contents), vlookup, etc, but have failed miserably. Any thoughts on a formula I can use? |
#2
|
|||
|
|||
Try this *array* formula:
=INDEX('Entire Org'!$I$4:$I$4505,MATCH(1,('Entire Org'!$C$4:$C$4505='Employee Database'!$F1)*('Entire Org'!$D$4:$D$4505='Employee Database'!$G1)*('Entire Org'!$E$4:$E$4505='Employee Database'!$H1),0)) Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ingrid" wrote in message ... =AVERAGE(IF('Entire Org'!$C$4:$C$4505='Employee Database'!$F1,IF('Entire Org'!$D$4:$D$4505='Employee Database'!$G1,IF('Entire Org'!$E$4:$E$4505='Employee Database'!$H1,'Entire Org'!$I$4:$I$4505)))) I am using the above array/matrix to successfully return a numeric value. Now, I would like to use a very similiar formula (fields, columns) to return non-numeric values. I have tried to substitute average with cell (and contents), vlookup, etc, but have failed miserably. Any thoughts on a formula I can use? |
#3
|
|||
|
|||
One thought, one Comment/Question:
Thought: I think you could simplify the formula a bit by using an array formula: =AVERAGE(IF('Entire Org'!$C$4:$C$4505&'EntireOrg'!$D$4:$D$4505&'Entire Org'!$E$4:$E$4505='Employee Database'!$F1&'Employee Database'!$G1&'Employee Database'!$H1,'Entire Org'!$I$4:$I$4505)). Comment/Question: The formula that you are asking to build will actually return a 4,401 item array. The best you could hope to see would be one item in that array. So...What is it that you are really looking to see from the formula? Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find replace text or symbol with carriage return | New Users to Excel | |||
How to insert carriage return in the middle of a text formula to . | Excel Discussion (Misc queries) | |||
How do I convert text to columns when there is a carriage return? | Excel Worksheet Functions | |||
How can I make Excel return the text in column A only if there is. | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |