איך פותרים חידה מתמטית בעזרת אקסל?
הסבר בסרטון היוטיוב:
https://www.youtube.com/watch?v=FuIajvlpQ0E
שמי מני פורת.
אני מורה פרטי ל: אקסל, אנגלית, מתמטיקה וערבית באזור רמת-גן .
מכין לבחינות אנגלית: IELTS, TOEFL ומלמד אנגלית גם מבוגרים: יחידים וקבוצות.
מומחה "אקסל" מטעם מיקרוסופט
מלמד "אקסל" כולל: 365, Power Query ו- VBA.
כֻּלכֶם מוזמנים להגיב, לשתף, לשאול ולענות לפוסטים בבלוג.
ברוכים הבאים, أهلا وسهلا, Welcome .
מומלץ להשתמש בדפדפן "כרום"
צור קשר: 052-5238880 או באי-מייל: PaxMundi@gmail.com
בקרו אותי בדף האוהדים ב"פייסבוק": facebook.com/meni.porat
20 CTRL+SHIFT keyboard shortcuts you are probably not
familiar with
1)
CTRL+SHIFT+8 => Selects the entire table,
including the header row and the total row (if present)
2)
CTRL+SHIFT+T => (a toggle shortcut):
Adds/removes the total row of a table
3)
CTRL+SHIFT+U
=> Expands/contracts the Formula Bar (toggle)
4)
CTRL+SHIFT+9
=> unhides hidden rows within selection
5)
CTRL+SHIFT+L
=> activates/deactivates filtering (toggle)
6)
CTRL+SHIFT+F6
=> toggles to the previous workbook (when two or more workbooks are open)
7)
CTRL+SHIFT+F4
=> Repeats the last search (like: Find Next)
- but backwards (upwards)
8)
CTRL+SHIFT+<
=> copies values from cell above (same as: CTRL+D) but without selecting the
cell above (works on more than one cell)
9)
CTRL+SHIFT+>
=> copies values from cell to the left (same as: CTRL+R) but without selecting
the cell to the left (works on more than one cell)
10) CTRL+SHIFT+SPACEBAR => same as CTRL+A
11) CTRL+SHIFT+O => selects all cells containing comments
(notes in 365)
12) CTRL+SHIFT+PAGEDOWN => Selects the
current and the next worksheet (creation of a Group)
13) CTRL+SHIFT+PAGEUP => Selects the
current and the previous worksheet (creation of a Group)
14) CTRL+SHIFT+[ => Selects all
Precedents (Direct & Indirect)
15) CTRL+SHIFT+] => Selects all Dependents
(Direct & Indirect)
16) CTRL+SHIFT+7 => Applies border (single,
black, thin) to selection (only outer border is applied)
17) CTRL+SHIFT+- => removes border (single,
black, thin) from selection (removes all borders within the selection)
18) CTRL+SHIFT+A => (when the insertion point is next to the
function's name entered) Displays the function’s arguments (in parentheses)
19) CTRL+SHIFT+' => Copies the value
from the cell above the active cell into the active cell. If the cell contains
a formula, it is converted into value.
20) CTRL+SHIFT+. => Copies the contents
of the adjacent cell (to the left) into the active cell. If the adjacent cell
contains a formula it copies the formula with relative references.
Milliseconds in Excel
We all know
how to calculate difference in time: in hours, minutes and seconds.
But did you
know that you can compute time with milliseconds in Excel as well?
In order to
calculate milliseconds, you first need to format the number properly.
This won’t do:
Pic 1: we cannot subtract times with milliseconds…
So how do we
do it?
First, we need to convert the data structure.
The last (third)
colon :
must be converted into a dot .
This can
easily be accomplished with the SUBSTITUTE function, as can be seen in cells D2
& E2.
Pic 2: changing the data
Next, we have to format the Start time, End time and the Difference as can be seen in this screenshot:
Pic 3: changing the number
format
And, now in
cell G2 we get the correct result, in hours, minutes, seconds and milliseconds:
Pic 4: the desired result with milliseconds
Getting rid of all Instances of duplicate Values
There are many methods in Excel to get rid of duplicate
values: you have 2 instances of the same value and you want to keep only one and
get rid of the second, superfluous value.
But what if you want to remove both instances of duplicate
values?
So, here’s trick I
invented which allows you to accomplish this in one formula.
In column N we have a spilled array of dates, some of
which are duplicate (marked in yellow: 13/04/2021, 15/04/2021, 19/04/2021).
The formula in cell P2 creates an array after removing
the above mentioned duplicates. The trick here is to mark each duplicate (found
with the COUNTIF function) with an intended “error” so that the TOCOL function
will ignore it.
This post demonstrates a case where we have two duplicate instances but of course the same method can easily be applied where there are more than two such instances.
That’s all.
Sum by Currency Code
Did you know
that you can sum by currency code?
When you format sums with the Accounting Number Format, Excel
automatically adds the currency code that you chose.
Pic 1: Sums with currency codes (added by the Accounting number format)
However, This
code is inseparable from the number, which means that you cannot extract it from
the number, as it is, so to speak, “built-in”.
So, actually, so far we haven’t been able to sum by currency codes.
However, I
found a method by which you can pinpoint which currency code “resides” in any “Accounting”-formatted
cell and thus sum these sums by their currency codes.
Step
1
Press CTRL+F3
to open the name manager
Add a new name
by pressing New
In the Name
Textbox write: Curr (or any other valid
name that you prefer)
In the Refers
to Textbox write: GET.CELL(7, Sheet1!A2)
Pic 2: Defining The Curr formula
Step 2
Select cells
B2:B21 and type =Curr in B2
Pic 3: Before entering the Curr formula in cells B2:B21
Step 3
Press Ctrl+Enter
This is what you are going to see in cells B2:B21.
Don’t panic 😊
Pic 4: After entering the Curr formula in cells B2:B21
If you look
closely at these cells (B2:B21) you’ll be able to notice that each cell displays
the number format of the adjacent cell on its left (A2:A21), including its
currency code.
Now we can
easily extract the currency code of each and every cell of the original dataset
in cell A2:A21
We hide
column B and use a simple formula to accomplish this.
Pic 5: Extracting the currency codes of cells A2:A21
Now we have
the currency codes, but alas, Excel does not let us sum up these numbers by the
codes in column C.
So I found two
bypassing methods to achieve the desired result: Summing up by currency codes.
Method
1
Excel does
not let us execute the SUMIF on cells C2:C21, but we can use the
adjacent cell to select only the currency code we need.
Column B is
hidden, but we don’t need it in order to know the currency codes it “hides”.
For each currency we want to sum, we can use the cell in (hidden) column B which
is exactly to the left of the code we want.
For example,
if want to sum only the $ currency code (which appears in C3 and elsewhere), we can select B3 as the second argument of SUMIF:
=SUMIF(B2:B21,B3,A2:A21)
Which yields
the correct result 😊
Pic 6: Sum only cells A2:A21 with currency code $
Another example,
summing only the Euro currency code €
Pic 7: Sum only cells A2:A21 with currency code €
Or summing only the Yen currency code ¥
Pic 8: Sum only cells A2:A21 with currency code ¥
Method
2
An alternative
method is using a Validation List, as follows:
Pic 9: A validation List in cell J1
It is
interesting to see that although the list contains 20 values, the Validation
list in cell J1, displays only unique values.
Now we can
use J1 as the second argument to the SUMIF formula:
=SUMIF(A2:A21, J1, B2:B21)
It is quite
easy to identify the currency code in each item of the list, so if the select
the first item (¥) the formula will yield
the sum of only Yen currency code numbers.
Pic 10: Using the validation List in cell J1 to sum only ¥
Or, if we want
to sum only the British Sterling Pound (£)
Pic 11: Using the validation List in cell J1 to sum only £
A-m-a-z-i-n-g!!!
Be sure to save the file an .xlsm (macro) file and not as an .xlsx file, since
the GET.CELL command (used in the Named Range) is a Macro 4.0 function.
Wish you a happy, healthy,
and prosperous year
This is C-r-a-z-y: SQRT
instead of MATCH
I reckon you're all familiar with
INDEX-MATCH. There's a myriad of video clips on Youtube with good, detailed
explanations.
What I'm going to talk about is the:
INDEX-MATCH-MATCH combination, where your search is two-dimensional: row
(vertical) and column (horizontal).
So, actually you're looking for a
solution which fulfills 2 conditions (criteria),
as demonstrated in the following pictures.
Example 1: How many shifts did David
have in the month of December?
Pic 1: INDEX-MATCH-MATCH - Example 1
Example 2: What subject does Jim
teach in the sixth Grade?
Pic 2:
INDEX-MATCH-MATCH - Example 2
Example 3: What's Lara's score in
Sports?
Pic 3:
INDEX-MATCH-MATCH - Example 3
Now, let's move to a more complicated challenge. Here, instead of 2 conditions, one vertical (row) and one horizontal (column) we have 2 horizontal conditions.
Consider the following dataset:
We are looking for the SKU by Song and composer. Actually, we are looking for
the row in which these two meet.
But here the INDEX-MATCH-MATCH
doesn't work.
Pic 4:
INDEX-MATCH-MATCH - does not work
One possible solution is using the
filter function, as can be seen in the picture below:
Pic 5: Using
FILTER
But, a small change to the original
INDEX-MATCH-MATCH will make it operative and functioning: We add the SQRT
function, and we also multiply both MATCH parts of the formula:
Pic 6:
Adding SQRT to INDEX-MATCH-MATCH – solves the problem
Amazing, but how does it work? Can
you please explain?
How does it work?
Since we're looking
for an SKU where the Song and the Composer are on the same row, then each of the two MATCH-es will yield the
same number (i.e., row number).
So, the
multiplication of a number by itself gives us its square value.
What is left for us to do is to SQRT this number in order to find the row
So the
solution is: INDEX(SQRT(MATCH(..)*MATCH(..)),)
Pic 7: Adding SQRT to INDEX-MATCH-MATCH -
Explanation
Now, let's
take a more complex situation where a song appears more than once.
With FILTER,
the solution is very simple.
Pic 8: A more complex scenario - solved by FILTER
Can this scenario be
also solved with the previous method of
SQRT(MATCH(…)*MATCH(…))?
Well, not exactly, but basically: yes.
As I said, each
composer and each song might appear more than once.
For example,
"Garden" appears twice: in row 5 and in row 6,
so A3:A10=E3, will
return the first occurrence (5) where we need the sixth occurrence (Garden with
Keith Jarret, not: Garden with Shania Twain)
So we have to adapt our solution to the new situation.
And here's the
solution to the more complex situation, still using
SQRT(MATCH(...)*MATCH(…))
Pic 9: A more complex scenario - solved by SQRT
Now we have to cater for cases of
error (“not found”).
So, again, here are the three solutions.
2 “traditional” ones: FILTER & INDEX-MATCH
and the third one is the new
SQRT….
Pic 10: FILTER - catering for “not found”
Pic 11: INDEX-MATCH - catering for “not found”
Pic 12: INDEX-SQRT - catering for “not found”