Most Difficult MS Excel questions

Venomous · May 27, 2015

Fudzy said:
Ctrl - ` will show you the cell formulas but how do you highlight the wrong formulas?

does that show it cell by cell?

how to show the wrong formula?
often with extended sheets the formulas form repeated patterns, any interuption in said pattern stands out like a sore thumb

1+2+3
51
2+6+3

at a glance which does not fit the pattern?

Venomous · May 27, 2015

MickeyD said:
djirre, that's mos simple! Wiff this, of coarse...

Venomous · May 27, 2015

LaraC said:
I’ve asked Google and the answer was given. Cool trick Venomous.

You will not find that in any MS text book currently available, or at least none that I have ever come across.

And is a question the OP should ask "on the spot", not in an email. It (email) allows for google skills, like now, and removes the element of surprise.

creeper · May 28, 2015

Ask him to do a reverse pivot table.

PilgrimToHyperion · May 28, 2015

Here, ask him this: http://mybroadband.co.za/vb/showthread.php/702522-Excel-Boffins-Help!-Rota-Shift-Roster and help me too!

Njabulod · May 28, 2015

Have him make an excel doc that accepts the user's birth date and then calculates a person's age in years, months, weeks, days, minutes, and seconds. Should be simple.

konfab · May 28, 2015

What are unnecessarily difficult are array functions.
Tell him to make a square grid of random numbers, then find the matrix inverse of them. It is something that should be simple, but it is not.

If that doesn't stop him, then ask him to implement k-means clustering without using macros if he is such a guru.
http://www.ijcaonline.org/volume11/number7/pxc3872144.pdf

SirFooK'nG · May 28, 2015

Tell him to define a couple of named tables, vlookup values in said names using concatenations, based on a nested "if" statement with conditional formatting then pivot table the results

(easy stuff actually)

garyc · May 28, 2015

For a really innocent and noob sounding question ask him one related Excel's unary bug / feature.

Something like the installation on your computer does not follow the sequence of calculation you learned at school and why is this? That's the sequence of firstly exponents and roots, then multiplication and division and finally addition and subtraction.

HumanShield · May 28, 2015

Ask him to create an SA ID Validation sheet. Got this from http://geekswithblogs.net/willemf/archive/2005/10/30/58561.aspx

Format:
{YYMMDD}{G}{SSS}{C}{A}{Z}
YYMMDD : Date of birth.
G : Gender. 0-4 Female; 5-9 Male.
SSS : Sequence No. for DOB/G combination.
C : Citizenship. 0 SA; 1 Other.
A : Usually 8, or 9 [can be other values]
Z : Control digit calculated in the following section:

Formula to calculate the check digit for a 13 digit identity number:

According to the provisions of the Identification Amendment Act, 2000 (Act No. 28 of 2000,
which was promulgated on 13 October 2000) all forms of identity documents other than the
green bar-coded identity document are invalid. [my observation: the following algorithm appears to work for the older 'blue'-book id numbers as well]. In accordance with the legislation,
the control figure which is the 13th digit of all identity numbers which have 08 and 09 is
calculated as follows using ID Number 800101 5009 087 as an example:

Add all the digits in the odd positions (excluding last digit).
8 + 0 + 0 + 5 + 0 + 0 = 13...................[1]
Move the even positions into a field and multiply the number by 2.
011098 x 2 = 22196
Add the digits of the result in b).
2 + 2 + 1 + 9 + 6 = 20.........................[2]
Add the answer in [2] to the answer in [1].
13 + 20 = 33
Subtract the second digit (i.e. 3) from 10. The number must tally with the last number in the ID Number. If the result is 2 digits, the last digit is used to compare against the last number in the ID Number. If the answer differs, the ID number is invalid.

This can be done easily using VBA. On a sheet will be... different

Coasti · May 28, 2015

Ask him to make use of lookups based on spreadsheet tab names.

Rkootknir · May 28, 2015

HumanShield said:
Ask him to create an SA ID Validation sheet. Got this from http://geekswithblogs.net/willemf/archive/2005/10/30/58561.aspx

This can be done easily using VBA. On a sheet will be... different

I would not attempt to actually use this in a production worksheet

, but:

Enter a 13 ID number in cell A1 (the last character can be any number or letter) and enter the following formula:

=10-VALUE(RIGHT((VALUE(MID(A1,1,1))+VALUE(MID(A1,3,1))+VALUE(MID(A1,5,1))+VALUE(MID(A1,7,1))+VALUE(MID(A1,9,1))+VALUE(MID(A1,11,1)))+(VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,1,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,2,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,3,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,4,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,5,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,6,1))),1))

Should spit out the value of the check digit. I'm sure there's a more practical way using bitmasks but that turns into a problem again when you have to explain to somebody else what a bitmask is...

Rkootknir · May 28, 2015

Coasti said:
Ask him to make use of lookups based on spreadsheet tab names.

And if he can do it, ask him why that is an extremely bad idea when it comes to checking \ auditing time.

ElecEng · May 28, 2015

I just solved a problem today: sumproduct with horizontal and vertical data. Hint, you need to use transpose and qualify the entire formula as an array Ctrl, shift, enter.

HumanShield · May 28, 2015

Rkootknir said:
I would not attempt to actually use this in a production worksheet , but:

Enter a 13 ID number in cell A1 (the last character can be any number or letter) and enter the following formula:

=10-VALUE(RIGHT((VALUE(MID(A1,1,1))+VALUE(MID(A1,3,1))+VALUE(MID(A1,5,1))+VALUE(MID(A1,7,1))+VALUE(MID(A1,9,1))+VALUE(MID(A1,11,1)))+(VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,1,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,2,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,3,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,4,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,5,1))+VALUE(MID(REPT("0",6-LEN(VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2))&VALUE(VALUE(MID(A1,2,1))&VALUE(MID(A1,4,1))&VALUE(MID(A1,6,1))&VALUE(MID(A1,8,1))&VALUE(MID(A1,10,1))&VALUE(MID(A1,12,1)))*2,6,1))),1))

Should spit out the value of the check digit. I'm sure there's a more practical way using bitmasks but that turns into a problem again when you have to explain to somebody else what a bitmask is...

Going to copy/paste this and see what happens

Toitjie · May 28, 2015

is $A$1 too easy to explain?

creeper · May 28, 2015

Toitjie said:
is $A$1 too easy to explain?

That great shortcut key F4. Adding the $ in anyway you want to.

(Note, I haven't figured out the shortcut for Mac Office 2011 to do the same. Anyone?)

Coasti · May 28, 2015

Rkootknir said:
And if he can do it, ask him why that is an extremely bad idea when it comes to checking \ auditing time.

Why is it?
Its useful if you have a book for example where every day's data is in separate tabs named say 1-31 and then have a summary tab pulling data from the other 31 tabs.

Rkootknir · May 28, 2015

Coasti said:
Why is it?
Its useful if you have a book for example where every day's data is in separate tabs named say 1-31 and then have a summary tab pulling data from the other 31 tabs.

Ja, I probably should not have said that as an absolute.

If it's a workbook for personal use then it's fine - you know how it works. The problem comes when somebody else (5 years from now) starts using it and doesn't have the first clue about how INDIRECT works (more than 99.9% of people that attempt to use Excel in my experience

). Another problem is auditors - a large number of them don't know how volatile formulas like INDIRECT work so you have to explain it to them first, negating most of the time you saved constructing the summary sheet.

Maybe I'm just cynical, but I've learned to keep Excel formulas as simple as possible when used in a shared environment - it reduces that chance of screwups in future.

Baxteen · May 28, 2015

get him to use the same forumla's that he created in the version of excel in an older version of excel.

because if you go back to the right version he will be in a position where all formula delimiters (like the separator in a vlookup) goes from a ; to a ,

if you really want to troll him let him create a vlookup onto a sheet sitting on a different file server (with a coma in the name(new versions of excel that can happen)
then move the forumla's over to a differnt machine using an older version of excel where the forula's are coma delimited and wathc his face when the forumla no longer works.

I actually went through that when handing over a report.
I am on Win 7 with excel 2010 (coma delimited formula)
new guy is on win 8.1 with office 2013 (semicolon delimited formula)
both of us are 64 bit.
just you know, if you want to explore before testing

Most Difficult MS Excel questions

Honorary Master

Honorary Master

Honorary Master

Executive Member

Expert Member

Well-Known Member

Honorary Master

Executive Member

Expert Member

Senior Member

Expert Member

Expert Member

Expert Member

Senior Member

Senior Member

Expert Member

Executive Member

Expert Member

Expert Member

Honorary Master