The challenge this week is as follows: based on the below data extract of populations for a selection of countries for the four years from 2010-2013 inclusive, a single formula in A1 to identify the *Region* which had the largest average yearly percentage increase in population over that period.

The answer here would be **Sub-Saharan Africa**. For readers wishing to corroborate their results, the average yearly percentage increase in population for that region was 2.9840070521% to 10 d.p.

Note that this is a *shortest formula challenge*, which means that readers should attempt to find not only a correct solution to the problem but also one which has the least number of characters as possible.

Any ranges must include both a row and column reference: A:F or 3:12, for example, are not acceptable. Named Ranges are also not permitted.

You can download the workbook here

Solution next week. Best of luck!

### Like this:

Like Loading...

*Related*

Feeling distinctly sluggish today but here is a start. (Terribly clunky I know)

Sorry, didn’t tidy up removing all $ and 0… (still bad but at least not as long) 🙂

Hi again,

It seems my initial post has disappeared …

Thanks, Chris. A few points:

1) The range is B

3:B12, not B4:B12.2) I need the average

yearlychange. What you have based your calculations on is the 2010 and 2013 figures only. Whilst I agree that, in general, theRegionwith the largest proportional increase in its population figure from 2010 to 2013 isalmost certainto be the sameRegionwhich has the highest average yearly change, can you assert that this willalwaysbe the case?For example, as unlikely a scenario as it is (or perhaps not?), let’s assume that the total population for a given

Regionwas:2010:

8,500,0002011:

10,000,0002012:

10,000,0002013:

8,500,000Based on your calculations, the ratio assigned to this region would be 1, since 8,500,000/8,500,000=1. Or, in other words, a 0% increase.

However, if we look at the year-on-year % changes, we get:

2010-11:

17.65%2011-12:

0.00%2012-13:

-15.00%And so the average change would be

0.88%.Note that this figure is NOT zero, since, as a proportion of the

starting value, going from 8,500,000 to 10,000,000 is a larger jump than going from 10,000,000 to 8,500,000; hence the fact that we see 17.65% and -15.00%, and not figures which “cancel each other out”, i.e. 17.65% and -17.65%, or 15.00% and -15.00%,3) Do you really want to be using absolute references in a Shortest Formula Challenge? 🙂

EDIT: just seen your updated post, so ignore!Regards

For readers wishing to corroborate their results, the average yearly percentage increase in population for that region was 2.9840070521% to 10 d.p.

What does d.p. mean?

@GreasySpotGood point – thanks. It’s an abbreviation for “decimal points”.

Cheers

Ok call me an idiot if you must, but can I get the mathematical equation for the % growth from year to year. I am using (b-a)/a*100 which comes out with the correct answers using your explanation to ChrisBM. However I don’t come up with 2.9840070521% as you state above in the original problem set. I come up with 3.1761202070% for Sub-Saharan Africa.

XOR LX – Thanks about the range, sorry, it seems like I’ve been very sloppy recently.

On your comments about the average, it depends what you mean by average:

I agree conceptually that if you are talking about arithmetic average then the correct answer in your example is non-zero, but in reality it has not grown at all. I was considering the geometric average growth rate.

In essence, my formula should give the area with the highest average growth rate where the growth rate is the rate at which it would have to have grown from time 0 to time t+3. I feel it certainly is the most fair reflection of average growth rate.

Wikipedia discussion on the subject

http://en.wikipedia.org/wiki/Inequality_of_arithmetic_and_geometric_means

Chris

Hi again!

This is my solution (not require CSE):

Many characters… because repeat MMULT twice.

Blessings!

WOW! Talk about a brain spasm. Reading through that material will definitely give you one.

@GreasySpotFor Sub-Saharan Africa:

2010 Total: 21,558,045

2011 Total: 22,210,006

2012 Total: 22,872,070

2013 Total: 23,546,083

2010-2011: = (22,210,006-21,558,045)/21,558,045 = 3.02421207488898%

2011-2012: = (22,872,070-22,210,006)/22,210,006 = 2.98092670483745%

2012-2013: = (23,546,083-22,872,070)/22,872,070 = 2.94688237662791%

Average change = (3.02421207488898%+2.98092670483745%+2.94688237662791%)/3 = 2.98400705211811%

What am I missing?

Regards

@ChrisBMI completely agree with you that this may well not be the best statistical measure here. I confess that I did not give too much consideration to this matter, though I do feel (hope) that my preamble made it clear what type of average I was seeking (whether, practically-speaking, that may be an inferior measure or not to the one you outline).

In future I will be certain to give more credence to my challenges. For the time being, however, and whether or not you disagree with the statistical merits of such a result, the challenge will remain as it is. The idea was constructed more to challenge various Excel skills (as always here), rather than to begin a debate on the various forms of statistical representation. Having said that, I take your point, and will certainly, as I said, make future challenges more “realistic”.

Regards

Well for one your original population numbers are different.

Using the numbers in the last post you made I concur with your results but that was not the numbers you posted in the picture or the file.

I Think that even if the following formula is not the percentage calculated as such, it could shorten a little to find the region as follows:

Blessings!

I will move forward knowing I have the math correct and you will sort out the data set. Cheers.

@John Jairo VI get “East Asia & Pacific” using that formula (?)

Also, this part:

resolves to the array:

Is this what you intended?

Regards

@GreasySpotDifferent to?See above posts.

I upload my sheet, and here can you see the formula working.

Originally, I made the formula in an excel spanish version.

https://mega.co.nz/#!UpIg1SIB!ZdSYkMx5pdakSW3xV3bX04ye6fIp0mDgZknFLMFWWR8

Comment if it’s works! Blessings!

You are of course right, I should not be trying to change the challenge. I apologise if it seemed rude.

Modifying my already ugly solution to an even longer more clunky one:

@ALLSincere apologies to everyone who has been posting today. I’ve had a few issues with comment posting and so not all may have appeared in the correct chronological order, which has obviously been extremely confusing for everyone. Sorted now, so hopefully won’t happen again. Sorry!

@GreasySpotSincere apologies. I did have a slightly different dataset when I first created this post: I retracted the post almost immediately in order to edit the data, and I (wrongly) presumed that no-one could have read it in that short time. However, evidently you did.

Very sorry for that.

Regards

@ChrisBMNo! Please – keep those sorts of comments coming! Despite the fact that I don’t want to now change the Excel challenge, that doesn’t mean that the points you make aren’t perfectly relevant, even instructive.

Besides, you’re evidently of a more statistical persuasion than I am (I knew I shouldn’t have used such a scenario for a formula challenge – I was never very strong in that field!). So thanks for the education in this respect.

Regards

@John Jairo VBit confused – the formula you posted is quite different to the one in that file.

It appears to be the OFFSET construction which differs: in your post here, this function is using a

single cellas thereferenceparameter, e.g. D3, and there is also aheightparameter of 10; in the version in your file, however, the OFFSETs are using arangeas thereferenceparameter, e.g. D3:D12, and this time with noheightparameter.Can you clarify? And perhaps, when you’ve decided which version is the correct one, post it in a comment here?

Thanks a lot

@JamesYes – very sorry. Your post appears to have been caught in a backlog, even though you made it some time ago. Apologies.

Am I wrong, or – like ChrisBM – are you too making your own judgment on how this average should be measured here?

That’s fair enough, in real terms at least, and perhaps you could explain your reasoning for this construction with ^(1/4)? I for one would certainly be interested as I’ve clearly not grasped any of the statistical concepts/methods involved in such a question.

However, as I said to ChrisBM, despite my lack of understanding in this field (and so most likely a terrible choice of means with which to determine such an average), I would like it if people had a go at obtaining the results using the logic that I have, if possible.

Many thanks.

@XOR LX

I post 3 version’s, all works for me. I don’t know if I make a mistake in the translation of the formula. Then I post the formula in spanish version:

Option 1:

Option 2:

Option 3:

And I explain:

Option 1 and Option 2 has the same characters. Of those two , I prefer the second (looks shorter).

The third option has 8 characters less… even is not the percentage calculated as such… it calculates the region with the max average.

The final choice is Option 3. Please comment! Blessings!

@John Jairo VThanks. Think I understand now. Let me have a look at each of your solutions and get back to you.

Regards

Jajajajaja! Sorry! I made a mistake in the references in the second SUMIF, for the translation.

Here the formulas in English:

Option 1 (length: 244)

Option 2: (length: 240)

Option 3: (length: 232)

my apologies! Blessings!

Hi

My humble contribution.

@XLarium

While this gets the right result with this data set I think the calculation is slightly wrong. I think you are looking for the maximum single row growth as opposed to region.

The maximum number your formula finds is 3.1761% (which is the growth rate for Angola – row 6) as opposed to the 2.984% that it should find. It then returns the region for this row – Sub-Saharan Africa

@XOR LX

Mainly in Finance, there is the need to work out the annual average growth rate over a given number of years … ( technically it is called the Compound Annual Growth Rate : CAGR)

The generic formula is: (F3/C3:)^(1/4)-1 or (FinalAmount/InitialAmount)^(1/nbyears)-1

Is this explanation clear enough ?

Hello Chris

Your are right. I misunderstood.

I can confirm that, so far, two readers have offered correct solutions:

John Jairo VandChris BM.The leading contender for shortest solution is currently John, at

232characters.Regards

@JamesMany thanks for the explanation. Again, I’m sure that’s a much better statistical representation of what I was wanting to show than that which I used, and I apologize for my ignorance in this field, in which you clearly know what you’re talking about!

Regards

@XLariumDespite it not being quite right for this challenge, I have to say that I like this alternative to the standard OFFSET construction that you employ using INDIRECT and ROW. Thanks for sharing it – one to remember.

Regards

@ XOR LX

@XLarium

I was away from the office yesterday afternoon so therefore not able to participate in the further discussions, but I believe XLarium’s code doesn’t meet the criteria for ranges.

“

Any ranges must include both a row and column reference: A:F or 3:12, for example, are not acceptable.”@GreasySpotGood spot! Forgetting my own rules!@GreasySpot

Well, technically they are not ranges but individual cell references.

🙂

Here is my formula for Google Sheets:

Formula

Length 128

Here I use the QUERY function to basically type out the formula for the average, grouped by region (column B). I SORT it by the second column so that the highest number is at the top, then I use INDEX to select the region name that is at the top after the sorting. I got greedy and did not put a space between the 3 and group so it looks like 3group, but the formula still works.

Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1DPu928lTdAltz64gkkm03WWSmpvSGFEtZE8XbLpY310/edit#gid=1183884303

Well I was able to shave off 6 characters by replacing “sum(” with the number 1 and then using the substitute function to replace it back

Length 122

It makes it much harder to understand at first glance though XD

I like John Jairo V’s (non-CSE) option, which perhaps could be reduced further? (131 chars)

Or maybe for more flexibility only referring to complete ranges (with CSE)? (186 chars)

Note this is just a tweak to his original idea – credit goes to him.

i accidentally left off the OFFSET height argument (10) in the first formula which would make it 137 chars.

@LoriAbsolutely wonderful.

Until now I had not known that there existed such an alternative (and elegant) construction to the standard (and lengthy) MATCH(MAX([

long_formula]),[long_formula],0), etc.1/FREQUENCY(0,… with a reciprocation of the

bins_arrayis sublime.P.S. We may be able to shave off an extra character (at a slight loss of readability) by making the

lookup_value0 (i.e. omitted) and reciprocating the FREQUENCY construction with 0 rather than 1, i.e.:Yes, =FREQUENCY(-LargeNum,-Array) can be used to find the maximum in an Array, or when data is positive =FREQUENCY(0,1/Array).

Also, my original formula does actually seem to work without the height argument since =SUMIF(B3:B12,B3:B12,C3) automatically expands the last argument In a similar way you can also replace B3:B12 by B3:B4 since LOOKUP expands the range like SUMIF (but note that if only B3 is used in the last argument LOOKUP expands the range horizontally not vertically.)

So, as you say, using 0/FREQUENCY allows one to remove the leading 1 and also changing B12 to B4 would save 2 further characters i.e. 129. Still not quite up to Isai’s Google Sheets solution but only 7 characters less.

@Lori

Your “tweak” is awesome! Thanks for show us this marvelous option. I didn’t remember that the last argument for SUMIF expands with the dimension of the second argument. Elegant, short, and great! Congratulations.