Hey, welcome back it’s another MrExcel netcast, I’m Bill Jelen. Today’s question was sent in by Rob from England.
Rob is trying to do an array formula, and these are tricky when you’re new to array formulas. Rob has a series of values here and for each value, he wants to divide it by the exchange rate.
Now, he has a formula here that’s not an array formula. It’s kind of ugly, dividing each one B5/B1+C5/C1 and so on, and here is Rob’s attempt at doing an array formula and I press control-shift enter here which is correct. He said, “Hey, let’s take all of these values and divided by all of these values.” And well, that’s kind of the right path, what you’re going to get from an array if you do it correctly is you’re going to get a series of values and so you always have to put that in some sort of a wrapper function and in this case a great wrapper function be a SUM.
So, I’m going to come here and say, “=SUM” and we’ll get rid of that parenthesis, B5 to J5 divided by and here because I’m assuming we don’t want to copy this down I’m going to put some dollar signs, and so we get rid of that open parenthesis B dollar sign one column J dollar sign, and that closing parenthesis closes out the sum.
Now, if you just press enter here you’re not going to get the right result, you’re going to get a value error, but the trick is to hold down control and shift and press enter, and what Excel is going to do is it’s going to go those nine divisions and then sum the whole thing up, and I want to have that, grab the fill handle and copy down, and we have a very nice simple formula that does all the divisions and sums them up certainly faster than this.
So, Rob hey, great idea using the array formula just a little bit weird, how we have to do it there. Make sure to use this Sum function as a wrapper. \
Alright, I want to thank you for stopping well, see you next time for another netcast from MrExcel.
Transcription by:
Scribe4you Transcription Services