סלנג עברי שמקורו בערבית – מספר דוגמאות
שמי מני פורת.
אני מורה פרטי ל: אקסל, אנגלית, מתמטיקה וערבית באזור רמת-גן .
מכין לבחינות אנגלית: IELTS, TOEFL ומלמד אנגלית גם מבוגרים: יחידים וקבוצות.
מומחה "אקסל" מטעם מיקרוסופט
מלמד "אקסל" כולל: 365, Power Query ו- VBA.
כֻּלכֶם מוזמנים להגיב, לשתף, לשאול ולענות לפוסטים בבלוג.
ברוכים הבאים, أهلا وسهلا, Welcome .
מומלץ להשתמש בדפדפן "כרום"
צור קשר: 052-5238880 או באי-מייל: PaxMundi@gmail.com
בקרו אותי בדף האוהדים ב"פייסבוק": facebook.com/meni.porat
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”
Creating a Dynamic Yearly Calendar
Using SEQUNCE,
creating a dynamic yearly calendar is a piece of cake.
What it takes is only
5 easy steps:
1. Add the year
parameter (“yyyy”) in cell K1
2.
In cell A1, write the following formula:
=TEXT(WEEKDAY(SEQUENCE(,7)),"b1ddd")
3. in cell A2, write
the following formula:
=LET(d, DATE($K$1,1,1),SEQUENCE(54,7,1-WEEKDAY(d)+d))
4. Select cells A2:G2,
go to Conditional Formatting and add the following rule:
=A2<DATE($K$1,1,1)
Then, press the Format button, and in the Format cells pane,
select Font and choose White background from the Color Dropdown
5. Select cells
A54:G55, go to Conditional Formatting and add the following rule:
=A54>DATE($K$1,12,31)
Then, press the Format button, and in the Format cells pane,
select Font and choose White background from the Color Dropdown (same
as in step 4.)
That’s all.
Now you have a perfect calendar. If the year does not begin on Sunday, then the
cells on row 2 preceding January 1st will be empty.
The same
applies to the end of the year: dates that occur after December 31st
will be empty.
If you wonder
why I used 54 weeks (instead of 53 weeks) in my solution, the reason is this:
A leap year has 366 days. So if it starts on Saturday and ends on Sunday, then we
have 54 weeks. This happened, for example, in year 2000.
3 methods to find the
n-largest/n-smallest number in one formula
Suppose you want to find the n-th largest or n-smallest number in a
list:
1st largest, 2nd largest or the 5th
smallest etc.
How can this be achieved with only one formula?
Answer: with parameters.
Parameter No.1: n (desired “location”) in cell D1
Parameter No.2: Largest (“T”) or Smallest (“B”) in cell F1
Here are three methods that apply this concept:
Method
1: LARGE & SMALL
Method
2: LARGE only
Method 3: AGGREGATE
Order of Adjectives in a
Sentence
- There are 10 categories of adjectives (see table below).
- If you want to modify a noun with more than one adjective, the
order of these categories matters…
- Of course, having too many adjectives in one sentence is not
recommended
Examples:
השימוש
ב- would
למילה would ישנם
הרבה שימושים באנגלית.
מספר דוגמאות:
1)
צורת העתיד-עבר (Future-Past) – אפשרות בעתיד שהחלה בעבר
·
He said he would finish his
work by Monday
(yesterday he said: “I’ll finish my work by Monday”)
·
I thought he would be here
by now
(An
hour ago I thought: “I believe he will be here in an hour”)
2)
פעולות בשלילה בעבר (רצון או יכולת )
·
The car
wouldn’t start this morning
(We tried to start the car, but we failed)
·
She wouldn’t get out of bed all day long
(She refused to get out of bed)
3)
סבירות גבוהה של משהו שקרה לפני זמן קצר
·
A: There was a knock at the
door. Did you hear it?
B: Yes, that would be Bran Clark. He wants
to join the club.
4)
בקשה נימוסית
·
Would
you please stop that noise?
5)
בטוי רצון חזק
· I would like to go to a restaurant tonight
6)
Would
that
(הלואי ש...)
·
A: If I won the lottery, I would travel to China
B: Would that it were so
7)
העדפת אפשרות אחת על אחרת (עם: sooner…. than/rather…than)
·
He’d (=He would) rather/sooner die than tell his wife the truth
8)
Conditional
II
(משפט תנאי – סבירות נמוכה)
·
If I won the money, I would invest it cleverly
9)
Conditional
III
(משפט תנאי – לא אפשרי/מצב בלתי-הפיך)
·
I would
have passed the exam, if I had
studied all day long
10) מצב
היפותטי (לא מותנה)
·
She would be an amazing partner
11) דעה
מנומסת
·
I would
expect them to leave since they aren’t helping us
12) שאלה
רטורית
·
Why
would I lie to you?
13) עצה
מנומסת
·
I wouldn’t
go out with her, if I were you
14) הצעה
מנומסת
·
Would
you like some more coffee?