Frédéric LE GUEN

Author's details

Name: Frédéric LE GUEN
Date registered: 25/12/2012

Latest posts

  1. Convert Latitude and Longitude — 07/01/2018
  2. Keep your last data update — 31/12/2017
  3. What is a logical test in Excel — 19/12/2017
  4. The correct value of the week number — 15/12/2017
  5. Add days without the weekend — 15/12/2017

Most commented posts

  1. How to make automatic calendar in Excel — 6 comments
  2. Weekends or weekdays in Excel — 2 comments
  3. Function COUNTIF — 2 comments
  4. Population Pyramid — 2 comments
  5. NOW & TODAY — 2 comments

Author's posts listings

Convert Latitude and Longitude

Nowadays, GPS localization is in common usage. Some apps use decimal format (48.85833), others return the coordinates in degrees, minutes and seconds (48°51’29.99”). In this article I will show you how to convert one format to the other and vice-versa. How to write a coordinate in Excel GPS coordinates are generally written using the symbols ° (degrees), ‘ …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/convert-latitude-longitude/

Keep your last data update

Too many data entries for the same contact Let’s start from a file where we store client information. Sometimes, we have many different data entries for the same customer. We want to keep only the newest information for each customer based on the column Update. Remove Duplicates not applicable With a such file, you can’t …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/keep-last-update-data/

What is a logical test in Excel

When should you create a logical test? Creating a logical test is THE starting point for these 3 important functions in Excel: IF COUNTIFS SUMIFS But it is also used for conditional formatting. Yes, you can automatically change the color of your cells according the result of a test. What it a logical test Logical …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/logical-test-excel/

The correct value of the week number

The function WEEKNUM In Excel, to return the week number of a date you have the function WEEKNUM. =WEEKNUM(Date) Easy ? Sure ? Well in fact, it’s not so simple. It depends if you are in USA or in another country. The rule of calculation for the week is different between the USA and the …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/correct-value-week-number/

Add days without the weekend

Common error when adding days When you build a schedule for your team, you mustn’t include the weekend in your result. So you can’t write a formula like this =B2+C2 Look at the first results! 🧐🧐🧐 Even if the first result looks correct, it isn’t. The result is Monday but it should be Wednesday. If …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/add-days-without-weekend/

Mixed reference

Mixed reference A mixed reference is a reference that is fixed only on part of the reference: either the row or the column Before showing you an example of a calculation using mixed references, we will detail the use of the $ symbol in a reference. An absolute reference has two $. There is one …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/mixed-reference/

How to use the contain of cells in Excel?

In Excel, it’s very easy to use the contain of cells. You just have to call the reference of a cell to reuse its value in as data or in a formula. What is the reference of a cell? In Excel, a cell is often called by its references. In other word, the intersection of a …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/use-contain-cells-excel/

Function XOR

Function XOR is a new logical function that corresponds to the exclusive OR What is the XOR function? In logic, there are two ways to specify an OR context The inclusive OR. One or more of its parameters are true and the result of the test is TRUE, it is the function OR The exclusive OR. One and …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/function-xor/

Compare 2 columns

How to compare 2 columns in Excel?  It’s very simple 😄😎👍 I will show you in this post how to do this with the functions VLOOKUP for the research ISNA for the test IF to customize the final result 😉 But first, let’s start with a live demo 💛❤💙💜💚 Live demo In this worksheet (yes, it’s …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/compare-2-columns/

Function LEN

Presentation of the LEN function The LEN function returns the number of characters in a cell (LEN for length 😉) =LEN(string) And that’s all? 🤔🤨😲 Yes that’s all! But it is a very useful function for the tests or in conditional formatting 😉 Remarks If you have a string with coma, point, dash, space, … the LEN …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/function-len/

Older posts «