Most Difficult MS Excel questions

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

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?
 
MickeyD said:
djirre, that's mos simple! Wiff this, of coarse...

images
Click to expand...

:D
 
LaraC said:
I’ve asked Google and the answer was given. Cool trick Venomous. :)
Click to expand...

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.
 
Last edited:
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.
 
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
 
Last edited:
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 :D (easy stuff actually)
 
Last edited:
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.
 
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.
Click to expand...

This can be done easily using VBA. On a sheet will be... different
 
Ask him to make use of lookups based on spreadsheet tab names.
 
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
Click to expand...
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...
 
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.
 
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...
Click to expand...

Going to copy/paste this and see what happens :p
 
Rkootknir said:
And if he can do it, ask him why that is an extremely bad idea when it comes to checking \ auditing time.
Click to expand...

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.
 
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.
Click to expand...
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.
 
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
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X