Excel Array Formula Series #1: Basics of Array Formulas

[jamiezzj]

thumb upppp thanks mark!

[ExcelIsFun]

@jamiezzj , You are welcome!

[velcrots]

hey guys, here's a tough one...i'm making a daily nutritional intake chart. say column 1 says "oatmeal", column 2 has the # of calories, 3 has # of carbs, 4 has # of fat...etc. how can i make the word "oatmeal", say in dropdown list on another worksheet, represent all of those columns of data? and once selected, that data shows up in seperated columns in a results table, to sum with other data from other dropdowns? have i described it so it makes sense? is it even possible? thanks so much!

[ExcelIsFun]

@velcrots , pots Q to:

mrexcel [dot] com/forum

[TheUkrgrl]

THANK YOU!! I've been trying to figure out this array name thing for hours, and halfway into your video, I got it!

[ExcelIsFun]

@TheUkrgrl , cool! I have a playlist og videos that goes into even more detail:

youtube.com/user/ExcelIsFun#gr­id/user/007E7E9CA63304D3

[sdamoney]

thanks for being upbeat or else I'd be bored!

[ExcelIsFun]

@sdamoney , u r welcome!

[browncone]

very helpful thank you for helping me to understand a better way to array formulas in excel!

[ExcelIsFun]

You are welcome!

[senorblahke]

"we use that word all the time in english, array, it just means a bunch of things, but in excel, it means a bunch of things in cells like chevy, ford, and gmc, and then the units shown here"

-made me LOL. cheezy video but hilarious, entertaining, AND informational.

[RockstarKanth]

I've been suffering to get some good excel lessons, and now I am doing this since 2 months, trust me, now i can doing almost anything from your lessons. :) .. Priceless!

Thanks buddy!

[ExcelIsFun]

You are welcome!!

[sooperkhaan1]

@ExcelIsFun

hi your lessons have been of pretty much value for me. i have a question that i have a series of Dates as data and i want a formula to count the dates in series and also not to increment the count for the dates appearing more than one, but for the rest dates i want the series to be keep going the same way as for example (result shud be) 1,2,2,2,3,4............

That is the date at second row has appeared thrice and teh series continues. Hope to hear from you soon.

[ExcelIsFun]

I do not understand your question. Are you trying to count unique dates? If that is the case a formula like this would work:

=SUMPRODUCT(--(FREQUENCY(Dates­,Dates)>0))

Or do you wnat a formula, that when it is copied down, increments dates? Or something else?

[sooperkhaan1]

@ExcelIsFun Your recent reply has worked good for me thnx.

[ExcelIsFun]

Cool!

[ExcelIsFun]

I still do not understand what you are trying to do when you wrote this: @ExcelIsFun Thanks for your quick reply. Example: A B 1 01/07/2010 2 02/07/2010 2 02/07/2010 3 04/07/2010 3 04/07/2010 3 04/07/2010 4 05/07/2010 5 06/07/2010 as in this example i want the column A to be filled the same way through a formula.

[ExcelIsFun]

Can you restate your question?

[Gbell3587]

ctrl + shift + enter does nothing.......had tried several times with both excel and open office, why wont it work?! is my keyboard retarded?!!

[ExcelIsFun]

Did you hold all three keys down at same time?

Otherwise, I realy am not sure what could be causing the problem.

[RevealUsYourLove]

mike, last time you taught us how to lock in functions into a round number, I cant find that video, can you tell me what that shortcut key is?

let's say I put in =sum(b2+b3) = 4, I want to lock in number 4 and forget about that function showing on the function box. What shortcut key is that?

Please get back to me soon. Thanks

[ExcelIsFun]

I do not understand your question. I do not know what you mean when you say "lock in functions into a round number".

Do you mean that you want to convert the formula =sum(b2+b3) to the number 4 - so that there is no more formula? If so, hit F2, then F9 keys, then Enter.

[RevealUsYourLove]

@ExcelIsFun That's it! Thank you so very much!

[Raeb222]

Thank you. Very helpful.

[ExcelIsFun]

You are welcome!

[GodblessHunt]

Great job!

[ExcelIsFun]

I am glad that you link it!

[sljee]

Very helpfull for learning; i have the dutch version and there is a diiference in the formula: =sumproduct: namely the matrices have to be seperated by ";" and not by a comma

=SOMPRODUCT(B13:B19;C13:C19)

[ExcelIsFun]

Thanks for that useful tip!!

[wiseclock20]

Your are Awesome!!!!

[porejemplo73]

SUBTIT IN SPANISH PLISS

[jefframras]

WOW!! 17 videos on arrays. I'm excited to get through these. Thanks so much for putting out this series. I thought you might make 1 video!

[ExcelIsFun]

Dear jefframras,

17 videos is not enough to portray Array formulas, but it's a good start!

Keep watching and rating!!!

Wait till you get to the last few, they are not so easy (but that's what makes them fun!!).

--ExcelIsFun