איך פותרים חידה מתמטית בעזרת אקסל

 

איך פותרים חידה מתמטית בעזרת אקסל?

 

הסבר בסרטון היוטיוב:

https://www.youtube.com/watch?v=FuIajvlpQ0E

 

20 CTRL+SHIFT keyboard shortcuts you are probably not familiar with

 

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.

 

 

Excel - How to calculate milliseconds

 

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

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.

Pic 1: Remove all instances of duplicate values

סלנג עברי שמקורו בערבית – מספר דוגמאות

 

סלנג עברי שמקורו בערבית – מספר דוגמאות