יום שלישי, 17 באפריל 2018

אקסל – פתרון יעיל יותר



אקסל – פתרון יעיל יותר

מייק גרבין (Mike Girvin) הוא אחד ממומחי "אקסל" הגדולים ביותר בעולם.

בערוץ היוטיוב שלו ישנם יותר מ-3000 סרטונים וכחצי מיליון מנויים.

לכן אני שמח במיוחד כשהוא מודה שהפתרון שלי יעיל יותר מהפתרון שלו.

עשה לי את היום J J J

צפו בסרטון המרתק הבא, במיוחד החל מדקה 6:39









יום שני, 26 במרץ 2018

קורס אנגלית לאקדמאים - מכתבי הערכה

קורס אנגלית לאקדמאים - מכתבי הערכה

סיימתי לאחרונה הדרכה בקורס לאנגלית לאקדמאים

20 מפגשים בני 5 שעות כ"א.

מצורף "מקבץ" משובים שקבלתי בסיום הקורס.


















מכתב תודה - 2017.12.03


מכתב תודה – 03.12.2017

מכתב תודה שקבלתי מתלמידה שהכנתי למבחן IELTS

זהו מבחן ידע באנגלית הנדרש במספר מדינות דוברות אנגלית (ארה"ב, אוסטרליה, בריטניה, קנדה, ניו-זילנד ועוד) כתנאי קבלה למוסדות אקדמיים.      

במספר מדינות מבחן זה הוא גם תנאי מוקדם להגירה לאותה ארץ.







יום שישי, 23 בפברואר 2018

Sum only even numbers in a range with SUMPRODUCT




Sum only even numbers in a range with SUMPRODUCT

Suppose we want to sum only the even numbers in the range:
F3:F21









There are, at least, four possible solutions:

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)/2*2)

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)*1)

=SUMPRODUCT(F3:F21,--(MOD(F3:F21,2)=0))

=SUMPRODUCT(F3:F21,N(MOD(F3:F21,2)=0))


First Version:

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)/2*2)

Explanation:

SUMPRODUCT multiplies 2 arrays (or more).
Each element in the first array is multiplied by its corresponding element in the second array, and the final result of the formula is the summation of all these multiplications (SUM of that PRODUCT = SUMPRODUCT).


The first array F3:F21 is the numbers in F3:F21.
The second array calculates only the even numbers in that range.

The expression: MOD(F3:F21,0)=0 checks each and every number in that range to see whether it is odd or even. The answer for each number is either TRUE or FALSE. But Excel cannot multiply numbers by a Boolean expression, so we need to convert the series of: FALSEs and TRUEs into 0’s and 1’s.
This can be done by multiplying each:
FALSE or TRUE with (2/2 = 1).

Multiplication of FALSE * 1 will yield: 0
Multiplication of TRUE * 1 will yield: 1

So, the second array becomes an array of alternate: 0 and 1.
By multiplying the first array (F3:F21) by the second array, only the second array’s elements which contain even numbers will be 1s. Thus, we’re multiplying and the summing only the even numbers and get the desired result

Second version:
 Multiplying the second array by 1:
=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)*1)

Third Version:
Adding -- before the second array to convert it to numbers:
Instead of multiplying the second array by 1 (or by: 2/2) we can 
simply add -- [two hyphens] before that array, in order to convert the Boolean values (TRUE or FALSE) into numbers (1 or 0):
=SUMPRODUCT(F3:F21,--(MOD(F3:F21,2)=0))

Fourth Version:
 Multiplying the second array by N (a function in Excel that converts Boolean values into number):
=SUMPRODUCT(F3:F21,N(MOD(F3:F21,2)=0))



יום שלישי, 13 בפברואר 2018

Excel - A simple trick to sum only even numbers in a range


A simple trick to sum only the even numbers in a range


  An Array Formula to sum only the even numbers in a Range


Here’s a trick I invented to sum only the even numbers in a range.
Let’s take, for example, the range F3:F21, in which there are both odd and even numbers (see attached picture).

The formula is:
{=SUM(IF(MOD(F3:F21,2)=0,F3:F21,0))}

The formula, which is an array formula, consists of three functions:

MOD(F3:F21, 2)
The MOD function in Excel calculates the remainder of an arithmetic division. There are two arguments to this Function: the first is the number(s) we want to divide [the dividend], and the second is the divisor.
For example: MOD(1,2), i.e. dividing 1 by 2, will return a remainder of 1.
Our array formula checks each and every number in the range after division by 2, which means: it checks whether the dividend is even.

IF(MOD(F3:F21,2)=0,F3:F21,0))
The IF function “wraps” the MOD function and checks the condition (for each number in the array): if it is even, it takes the number itself, otherwise – it replaces it by a zero.

SUM(IF(MOD(F3:F21,2)=0,F3:F21,0))
Finally, the SUM function “wraps” the previous functions and sums the whole array: since the IF “changed” all original odd numbers into zero, then we get as a result the summation of the even numbers only.

Since this is an Array Formula: After typing it do not press ENTER, but CTRL+SHIFT+ENTER, instead. Excel then adds the curly braces to indicate that this is an array formula: a formula operating on an array/range of cells, as opposed to a regular formula which operates on a single cell.


BTW:
A slight modification in the formula will SUM all the odd numbers in that range:
Just change the =0 into =1 if you want to sum only the odd numbers:

SUM(IF(MOD(F3:F21,2)=1,F3:F21,0))




יום שבת, 30 בדצמבר 2017

Excel: the fastest method to fill an arithmetic series in a column




Excel: A faster method to fill an arithmetic series in a column


The following method (which I believe is my own invention :-)) shows how to fill a series of numbers (in a predefined range) without telling Excel what is the stop (last cell's) result.

Explanation:
Suppose you want to fill the series: 7, 10, 13…. in cells: M3:M200

Usually you would use the following (“traditional”) method:

1.   Enter 7 in cell M3 and press: CTRL+ENTER (to stay in cell M3)
2.   Click the Home Tab
3.   Click the Editing Group
4.   Select the Fill icon
5.   Select the Series option
6.   In the Series dialog box select: Columns in the: Series radio button
7.   In the Series dialog box select: linear in the: Type radio button
8.   In the Step value text box: type 3
9.   Now you need to calculate the Stop value:
The formula is:  7 + (2003)*3 = 598, where: 200 is the last cell’s row and 3 is the first cell’s row.
10.  In the Stop value text box type the result of step 9.: 598
11.  Press OK in the Series dialog box and Excel will display the requested series in the defined range (M3:M200)


Now, to my method:
I propose a new method, which is faster: You don’t need to
precalculate the stop value, i.e. the value at the range’s last cell.

1.   Enter 7 in cell M3 and press: CTRL+ENTER (to stay in cell M3)
2.   Press F5 to display the Go To dialog box
3.   Enter M3:M200 in the Reference Text Box
4.   Press OK
5.   Press F2 to edit cell M3
6.   Press CTRL+ENTER to propagate the number in cell M3 (7) to the entire range
7.   In cell M4 Enter 10 (the second number of the series)
8.   Select cells M3:M4 and double-click the Fill Handle of cell M4 (at the lower right corner of that cell)
9.   Et voilà, Cells M3:M200 now contain the desired series…

 Picture No.1: The “traditional” method




Picture No.2: My method