You are here:

Excel/functions, array breakdown

Advertisement


Question
Hello Bill,
I have the following formulas in my workbook and want to expand on them or use them in different sheets but no matter how much I read and look online I can't seem to figure them out. What I am looking for is somebody who can break them down for me so I can see what each function does in the overall picture. The books all use only one function at a time and as I try to break them out and use them or alter them I get nothing but errors. The codes are as follows. Any help would be greatly appreciated.

=IFERROR(INDEX(CHARTASSIGN!$C$3:$C$31,MATCH(A17,CHARTASSIGN!$B$3:$B$31,0),0),"")

=IFERROR(INDEX(CHARTASSIGN!$C$3:$C$30,SMALL(IF(LEFT(CHARTASSIGN!$C$3:$C$30,3)<>"DR.",0,100)+IF(CHARTASSIGN!$C$3:$C$30>"",0,100)+ROW(CHARTASSIGN!$S$3:$S$30)-2,ROWS(CHARTASSIGN!$C$3:$C3)),0),"")


=IFERROR(INDEX(sheet2!$D$4:$D$74,SMALL(IF(LEFT(sheet2!$D$4:$D$74,3)<>"7,7mtg",0,100)+IF(sheet2!$D$4:$D$74>"",0,100)+ROW(sheet2!$S$3:$S$30)-2,ROWS(sheet2!$D$D4$D4)),0),"")

Ctrl shift return to get the brackets each one is in.
Thank you for looking and hope you can help on any. I really want to learn and not just have someone write things for me.
John

Answer
What I could write for you to break them down would be a lot to write.  But in a nutshell, this is what's happening.

If you use functions in their simplest form then all of the arguments would be hard typed.  For example, =IF(A1=1,10,500# means that if A1 contains a 1 then put 10 in that cell. If not then put 500.  But the TRUE part, 10, may have be more logical and thus have to be calculated.  So, another function may be inserted for the 10.  For example,

=IF#A1=1,IF(A2=1,10,100),500)

The inner IF statement is now more dynamic.  In the functions you referenced, the arguments they need are having to be calculated.  You'll have to see what those functions' arguments are first of all if they were hard typed.  Then, you'll see, hopefully, how those arguments are being calculated dynamically.

Hopefully this helps.

Bill
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bill

Expertise

I can provide help with most all EXCEL questions and most all questions about writing EXCEL macros. I have been developing macros for about 10 years in EXCEL and have switched to it from Lotus 1-2-3 after about 10 years of writing macros in it. Typically, I will not write a macro for you unless it is very short because of all the details a macro has to know about to work every time and all the time are rarely provided. If I am not given ALL the details, circumstances, and situations then a "good" macro can not be written. Please understand that I do not know it all and will be the first to say so. As politely as possible, I don't write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details to have the code work the first time and every time. This typically means too many follow-up emails, and subsequent macro changes due to lack of specific details, just to get those details so that the macro would work, all of which is on my own free time. The voice of experience from responding to many questions from people who ask me to write a macro for them from this site tells me this. I don't mean to come across as unhelpful but macros are usually very specific and without ALL of the specifics the macro I would write will not address all of your needs and the layout, location, formatting, conditions, etc. of your data and any related files the macro would have to work with. What seems like a simple task to you is almost always more involved than what you think to have the macro ALWAYS work in EVERY situation. If you have a macro you have already written and have a question about it then perhaps I could help with that. I am sure and hope you can and do understand.

©2016 About.com. All rights reserved.