IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

EXCEL level 3?
Good to know about
Excel at the moment of
leave for a company?
Toolbox?
In short, this supplementary course is aimed at students whose proficiency in Excel
is already somewhat established: basic work and everything related to filters
automatic, pivot tables and other formulas such as =VLOOKUP().

In particular, the use of named areas seems, at the very least, recommended.

From experience with internship reports, and thanks to the contributions of colleagues on the list
In eco-management diffusion, you will find a fairly wide range of useful procedures here.
to the establishment of dashboards or tools for management control.

The file is based on two EXCEL spreadsheets.

Advanced TD formulas [Link] : finalized corrected version

Advanced matrix formulas [Link]: initial version

A priori, this course can be used for individual learning, provided that one is in
professional urgency and/or curious by nature and/or addicted to logic.

In fact, we will avoid waiting for revenues because everything is the result of prior analysis.
(let's say it's the intended objective).

We will forgive some puns, pathetic certainly, but whose origin would be
linked to an excessively prolonged exposure to the infernal screen/keyboard pair.
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

THEME GRANTING OF A BONUS TO STAFF 1

2. WORKS. PRELIMINARIES : 3

2.1. The shutters 3

2.2. Name the individual columns of the main table 3

2.3. Update the range covered by the data table 4

2.4. The control and assistance with data entry 5

2.4.1. Control input 5
2.4.2. Zone of the list 5
2.4.3. Format conditional formatting (based on the cell content) 6
2.4.4. Format conditional formatting (test on another cell) 6

2.5. Entering new records 8

2.6. Data imports: 8

2.6.1. Provenance ACCESS 8
2.6.2. Provenance INTERNET or management software 8

3. THE TREATMENTS 9

3.1. String processing 9

=RIGHT() =LEFT() to extract data from a code 9
=MID() or how to isolate characters in a text 9
=NBCAR() and SEARCH() to split text 9
3.1.4. =CONCATENATE() and special collage to group text 10

3.2. =YEAR() and TODAY() to calculate seniority 10

3.3. Other useful formulas on dates 11

3.4. =COUNTIF() or conditional counting 11

3.5. =SUMIF() or the conditional sum 11

3.6. =VLOOKUP() with TRUE or omitted argument 12

3.7. =INDEX() or two-way tables 13

3.8. EQUIV() et =INDEX() pour repérer dans une plage et exploiter le rang renvoyé 14

3.9. =MATCH(), INDEX() and MIN() or MAX() to distinguish the extremes 16

3.10. Additional macro to write numbers in words 17

IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

Theme: granting a bonus to staff

a) The constituent elements are:

Ancient seniority: pretext to review =VLOOKUP()

Family allowance: linked to the level and a family index for management
=INDEX()

Prime presence: to 'reward' the least absent in order to

to use =MATCH() and =INDEX().

b) In order to illustrate =SUMIF() and =COUNTIF() we will return the total number of days
absence of employees whose seniority reaches 10 years (it seems that
absenteeism would be correlated there) and the number of people absent more than
14 days.

c) To see other aspects of INDEX() and COUNTIF(), we will highlight the

the most diligent employee and the 'least present', of course while managing any potential issues
jointly, which will be an opportunity to return to =IF()

d) The highlighter becoming essential, we will practice formatting.

conditional to automatically highlight our "winners"
and those who exceed the 14-day absence limit.

e) The entry of the step and the family index will be an excuse for the implementation
of control of entries (only accept for step value an integer from 1 to
5) and list areas (to propose the values 1, 2, and 3).

f) To conclude, with a thought for the secretary who will have long had to rewrite.
the amounts of our remuneration in full (and who will have lost a
crazy weather until I found this solution to ... a year until his retirement), we
will use a supplementary macro to transform the numbers into
letters.

g) Since we're talking about letters, we will see how to handle the strings of
characters and dates.

As can be seen in the copy of the final table on the next page, the
statistics appear above the data. As far as the list of
employees are called, we hope, to lengthen, it will be a good habit to
to take
☺ The space remains free underneath in an almost infinite way (63536 lines).
☺ Naming the beaches is more practical
☺ The use of shutters allows to always keep the first 8 lines visible.

Page : 1
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

At the top the main sheet. At the bottom the tables for calculating the three sub-primes.

Page: 2
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

2. Preliminary Work:
2.1. The shutters
To keep the first 8 rows in view (and no columns):
place yourself in cell A9
WINDOW
FIGER BLINDS

2.2. Name the individual columns of the main table

As the columns of the table are expected to lengthen, we could proceed as we are going to.
to see it further in 1-3. Here, we can content ourselves with naming the whole column
concerned from line 9 to line 65536
For various END and go to K65536
SHIFT + CLICK on A8 to select all
information from the employees' table including the titles.
After selection, will go through:
INSERTION
NAME
BELIEVE
In a single operation, the following areas have been named:
Named zone Cell range
Registration =List!$A$9:$A$65536
Name =List!$B$9:$B$65536
First name =List!$C$9:$C$65536
Echelon =List!$D$9:$D$65536
Family Index =Liste!$E$9:$E$65536
NB_jours_Absence=Liste!$F$9:$F$65536
Seniority =List!$G$9:$G$65536
Prime_Ancienneté=Liste!$H$9:$H$65536
Family Prime =List!$I$9:$I$65536
Prime Presence =List!$J$9:$J$65536
Total_Prime =List!$K$9:$K$65536
All upcoming calculation formulas will be facilitated ...
Special case of cross tables: we have all complained about the tables.
crossed which the update does not take into account the extension of the initial area.
After each addition of new lines, we need to redefine the concerned area.
more often, we reserve, as above, a very large number of lines.
A recent contribution from a colleague now allows me to resize.
automatically the area named as well in terms of number of lines as of
columns. Hence the following point ...

Page: 3
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

2.3. Update the range covered by the data table

The basic idea is to announce that the table, which we will call TSALARIES,
starts at A9; it always occupies a range corresponding to the number of
entered lines and the number of columns occupied. The area will be updated in
real-time and therefore ... the pivot tables will incorporate any addition of rows or columns.
INSERTION
NAME
DEFINE
Type the name TSALARIES and enter the formula below:

Click on the Click on the Click on the

cell A8 column A Line 8

SUMMARY EXPLANATION: the range will start in A8 (top corner of the table)
without line or column offset (hence the double; 0; 0) and will extend over the
number of rows and columns that the table has at all times.
ATTENTION: to make this work, make sure not to put anything in the
column A and on row 8 use the names in the named range dynamic.
Below, a variant using the area named MATRICULE which we measure
the number of lines (with + 1 to count the title).

ALERT: "Damned!" would say Etienne, the name does not appear in the dropdown list.
to the left of the formula bar. To control the update of the named area, it
"suffira" to return by INSERTION NAME DEFINE, to choose a name and to
click on the 'Refers to' box and there... the range is outlined.
Feel free to add employee rows or new columns and
noting that all this works well and LONG LIVE the upcoming pivot tables !!!!

Page: 4
IUT GEA Aix-en-Provence PC EXCEL add-ons Version XL 2003

2.4. The control and assistance in data entry

Two procedures are implemented here: a check on the entry of the level in
column D and a list area for the family index in Column E

2.4.1. Input control

Select the area named Echelon then

DATA
VALIDATION

In the ERROR ALERT tab define

In the OPTIONS tab, set the rule
the action to be taken in case of false
apply here an integer between 1
user maneuver (here Refusal and
and 5 will be allowed for the level
simple message

2.4.2. List area

For the family index, we will propose values of 1, 2, or 3.

Be prepared on the same sheet (EXCEL will refuse if we put the list on a
different tab
Soit annoncer, c’est le cas ici, les valeurs proposées.

As above, select the range

entire family index and DATA
VALIDATION
In the ALLOW box choose LIST
then at will:
- designate the source with the mouse
taper, like here, the proposals in
separating by ;

Page : 5
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

2.4.3. Format mise en forme conditionnelle (sur le contenu de la cellule)

It's crazy how much we love color these days.

Of course, automatically, it is enough to check, we wish to sanction by
a vivid red the "least present" (as in reality TV shows let's stay
positives!), by the most diligent bright green (doubtless a candidate for a
promotion …), and by a yellow alert for those who exceed 14 days.
As usual, select the entire range named "NB_jours_Absence" and
pass through:
FORMAT
Conditional Formatting
Here we set three conditions and then chose a format ...

It should be noted that in the case of a tie at the extremes, each is in color ...

Here, we have placed the condition on the value of the cell itself. How to put in
Apply conditional formatting based on the content of another cell.

2.4.4. Conditional formatting (test on another cell)

Beyond the coloring of the extreme values of the number of days absent,
we also want to highlight the people involved:
Analyze:
If, for the person concerned, the number
days of absence corresponds to the maximum
des jours d’absence alors format en rouge (en
turn for the opposite as it should be.
Illustration: the 5 cells A27:E27 are in
red car because in F27 we meet the
maximum of the series.
Tip: we will be able to manage everything in one.
only time

Page: 6
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

Procedure:
Select employee information from A9: E48
FORMAT
Conditional Formatting

The condition is no longer about "the value

from the cell is ..." but "The formula
is » since we are testing on the number of
days of absence.

Select all the cells because, posed

thus, the same formatting
conditional applies to all.

Well write the beginning that announces the formula.

$F9 allows you to test the content of the cell in column F but remains in
relative for the line (to copy downwards $F10, $F11 etc.
Surprising: we must refer to the cell address and not to the area.
named =NB_days_absence=MAX(NB_days_absence) does not work!
The second = is the operator (one can also use: > ; < ; ; >= ; <= ; <> etc).

Page : 7
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

2.5. Entering new records

Click in the table then go through

NEW FORM DATA
who has the advantage of copying the formulas
and formatting (attention
DATA GRID with Excel 2000.

Launched after setting up the

formulas, only the data
elements are to be captured ... the
formulas are copied as soon as the
validation completed

2.6. Data imports:

2.6.1. Origin of ACCESS

Either a copy-paste of a table or query in display mode, or from

ACCESS a FILE EXPORT from a table or query with an EXCEL type (the
The created EXCEL workbook opens quite normally.

2.6.2. Provenance INTERNET or management software

A copy-paste of a table can have surprises. Line data

in a single cell.
Depending on the version of Excel (2000 or 2003), perform a special paste:

Top line: HTML (not

well on EXCEL 2003).
Below: much better with
Unicode text or text.

Decimal problem: we will denote the point for the decimal and the position to the left
in the cells No calculation possible!
Solution: You must replace the point with the comma, so: for the affected cells
REPLACE [Link], ...

The time has finally come to move on to treatments!

Page: 8
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

3. The treatments
We can start, as a review by:
Set up the automatic filter to extract employees by level.
attendance and other family elements.
Set up pivot tables, the source range of which I remind you is set to
day automatically, to study absenteeism (it will be useful beforehand to
calculate seniority to find a correlation.
Use =VLOOKUP() to return from the employee ID all the
information about an employee ...
The time will then have come to go further to meet the inevitable demands.
of the company.

3.1. String manipulation

=RIGHT() and =LEFT() to extract data from a code

Here, it is about extracting the hire year which constitutes the first 4 characters of
registration in order to calculate seniority.
=LEFT(IDENTIFICATION;4) returns the value 1998 for the ID 1998001.
=RIGHT(EMPLOYEE_ID, 3) returns 001 and, although aligned to the right like text,
can perform calculations on it.
Since we are here and to have encountered students facing the problem
When importing a list of data, let's take it a step further.

=STXT() or how to isolate characters in a text

Let’s imagine that the registration number consists of two letters at the beginning to indicate the
service AC199801 (Purchasing service, year 1998 rank 01)
To return the hiring year, it is necessary to extract from the registration number ... from
3e character ... a string of length 4 so we will write:
=MID(EmployeeID, 3, 4)

3.1.3. =NBCAR() and SEARCH() to split text

One will be able to 'play' with the words on the sheet CHARACTER GAMES
Problème : j’importe l’identité sur une seule cellule et je veux scinder nom et prénom
Analyze:
To get the name, I am looking for the rank of
the space and I take n characters to the left
with n = rank -1 (space in 6 5 characters).
For the first name: I extract to the right the number of
characters of the word (here 10) – the rank of the space.
So 10 - 6 = 4 letters of the first name.

Page: 9
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

To understand well (start by naming the identity in cell A2):

in B4 the length of the word: =LEN(identity) or ... =LEN(A2) (yuck).
In B6 the position of the space (here 6e=SEARCH(' ' ; identity)
By respecting the analysis above for the name in C2, the formula will therefore be:
=LEFT(identity,SEARCH(" ",identity)-1)
Similarly, the first name will be obtained in D2 by:

=RIGHT(identity;LEN(identity)-SEARCH(" ";identity))
Note: in a table, the identity column is set to disappear. If it
is erased, extraction becomes impossible. Perform a SPECIAL PASTE
VALUES and replace the formulas with the single result in text form, the
suppression of the source then becomes possible.

=CONCATENATE() and special paste to combine text

And the question is awaited: "I have the first name and last name in two cells how do I
associate?
The answer always comes from Latin: concatenate comes from "CUM CATENARE"
(for those who may have forgotten 'to chain with').

=CONCATENATE(names; " "; surnames) or =CONCATENATE(A9, " ", B9)

We will note the presence of the space between quotation marks to avoid sticking the two.
parts of identity. As above, a SPECIAL VALUES COLLAGE
will allow to keep only the result.
Let's get to something more serious... especially regarding the fight against absenteeism!

3.2. =YEAR() and TODAY() to calculate seniority

Reminder: the year of hiring is extracted from the employee number (see 2-1-1) by

=LEFT(ID, 4)
To calculate seniority, simply subtract this year from the current year.
=TODAY() returns today's date
=YEAR(TODAY()) extracts the year from today's date.
Seniority is calculated as follows in G9:
=YEAR(TODAY())-LEFT(REGISTER;4)
Attention: consider returning the current year and using it in a calculation by formatting
a date with 'YYYY' reveals a culpable ignorance of the treatment of
dates on EXCEL...
Attention again: since we are revisiting the formats, the addition of year(s)
(same in I3 and I6), employees in B2 or other day(s) in B5 fall under a FORMAT
CELL NUMBER CUSTOM and not an entry ... (see 1st EXCEL TD
level 1)

Page : 10
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

3.3. Other useful formulas on dates

Creating filters or pivot tables on invoice dates, for example, can
create concern. It turns out to be useful to create new columns (let's remember that our
Tables are now automatically resized (see 1-3) with:
=YEAR(invoice_date) send back ... the year (Yes!)
=MONTH(invoice_date)
DAY(invoice_date)
But also =WEEKDAY(invoiceDate;2) to return the day of the week from 1 to 7
with Monday in 1st position (omitting the argument ;2 means taking 1 for the
Sunday among the Anglo-Saxons hence weekend...

See the example on the SHEET GAMES OF

CHARACTERS, and to notice what
The history of May 26, 1993 falls well on a
Wednesday ... 3andday of the week.

3.4. =COUNTIF() or conditional counting

In B3, we want to display the number of people exceeding 14 days of absence.

=COUNTIF(Array ; "condition")
let : =COUNTIF(NB_days_Absence ; '>14')

ATTENTION: the condition must be met

in quotes! To my knowledge, not
possibility of "playing" with ET() and
from OU(). See also at the end of 2-8

Can also be written as =COUNTIF(F9:F65536, ">14")

3.5. =SUMIF() or the conditional sum

The syntax is not the same as =COUNTIF() in that the criteria can refer to
on a data other than that on which the conditional sum is based.
Thus, in B5 we want to total for employees who have reached ten years of seniority,
number of "lost" days.
First, we will write the range affected by the condition (seniority) and then the
condition itself in quotation marks (">=10") then the range on which it applies
sum (number_of_absence_days).

Page: 11
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

Let it be in B5:

SUMIF(Seniority, ">=10", Absence_Days)

And if they want to see me crack...

=SUMIF(G9:G65536, ">=10", F9:F65536)
Note: omitting the last argument means summing over the relevant range.
by the condition.
Example: we want to edit the total number of years of service for the employees of
15 years of seniority and more.
=SUMIFS(Seniority, ">=15")

3.6. =VLOOKUP() with TRUE or omitted argument

Calculation of the seniority bonus in H9 and below.
Returning to this calculation formula allows for a re-examination of its writing because
Too many people tend to want to write: 'I am looking for the bonus.'
In fact, the reasoning will be rather: I am looking for seniority in the first column.
from the table named "tableprimeanciennete" and I return the amount of the bonus
appearing in the 2ecolumn of this table.
One will remember that the TRUE argument allows one to rely on seniority.
directly inferior but then requires to be sorted in ascending order on this column.

Illustration: we are looking for

vertically the seniority 25
in the 1st column of
table prime seniority
Beach 'table prime seniority: Not finding this value,
B5 :C9 we retain 21 (value directly
1st column sorted in ascending order lower) and we return the
amounting to €700.00 appearing on the
2ecolumn

Page : 12
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

From where the seniority bonus calculation formula in H9:

=VLOOKUP(Seniority;tablebonusSeniority;2;TRUE)
This formula avoids the multiplication of nested IF() statements ...
A little reminder: the TRUE argument is optional; however, FALSE will be
necessary if one wishes to use the ID number to send information about the
employee. There is no obligation to sort the IDs because, thanks to the FALSE argument, if a
registration not found, EXCEL returns the error message #N/A.

3.7. =INDEX() or two-way tables

Calculation of the family allowance in I9 and below

Analyze:
The first employee, who is actually a
employee, is at level 3 and 2 for
the family index.
The beach "tableprimefamiliale" refers to,
Beach "tableprimefamiliale": in its row 3 and its column
C17 :E21 from rank 2, the bonus of 200.00 €
The 2andemployee with couple 5.3 will have
well 600.00 €

INDEX() takes arguments in this order: range; row number; column number
Thus in H9:
=INDEX(family_prime_table; Level; Family_Index)
As we will see later, in 3-9, a single argument is sufficient if we work on a
matrix of a row or a column.
You would have been disappointed not to find the other way to write the formula (which me
gives me nausea just thinking about it) :
=INDEX(Detail premiums'!$C$17:$E$21 ,D9 ,E9)

Upon closer inspection, the task is greatly simplified because the levels (1 to 5) and
The family index (1 to 3) corresponds to the position in the double-entry table.
What happens if the rank in the range is not explicit, as for the so-called bonus?
Presence?
We will have to combine =INDEX() and MATCH() ...

Page: 13
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

3.8. EQUIV() and =INDEX() to locate in a range and make use of the returned rank

The formula will need to be written in J9, but beforehand, it seems appropriate to examine the
calculation table for the 'attendance' bonus:
Seniority
0 year(s) 6 year(s) 13 years 21 year(s)
0 day(s) 150,00 € 220.00 € 300,00 € 400,00 €
4 day(s) 100,00 € 140,00 € 200,00 € 275,00 €
9 day(s) 70,00 € 100,00 € 150,00 € 200,00 €
15 day(s) 40,00 € 70,00 € 100,00 € 150,00 €
22 day(s) - € - € - € - €

Attention: It has been a long time... the display in the last row of the table of - €
the number 0 is acceptable in accounting. The same goes for the day(s) and the
but I am repeating myself (see above in case you missed it).
That said, what follows deserves analysis and clarity from me and sustained attention from the
reader. Don't hesitate to take a break.

Absent for 21 days and with 25 years of seniority, our "friend" Aurioli will have a
bonus of 150.00 €
Let's check if you understood: the 3efrom the list 12 days and 25 year(s) will have €200.00.
It is imperative to dissect the mechanism to understand:
a) on the column of days (in turquoise; in black and white photocopy?) 25
does not exist, I rely on the lower value which turns out to be in position
5.
This is where EQUIV() comes in after naming the cells 'echelleabsence'.
concerned.
=MATCH(NB_days_Absent;echelleabsences;1)
To remember: EQUIV() returns the position (the rank) in a matrix of a row or
column. Argument 1 allows you to go back to the directly lower value if you are
folded croissant (this is indeed the case here).

Argument 0, discussed further on, returns the position only if the value exists.
By default, the argument is 1.
Try with argument 0 as 25 does not exist error message: #N/A !
b) on the top line (yellow), well named "seniority scale", 25 year(s)
will be brought back to 21 and therefore a return of position (rank) 4.

=MATCH(Seniority;seniority_scale;1)
At this stage, we have position 5, 4 which, thanks to the timely use of
INDEX() on 'tableprimepresence' will return €150.00.

Page: 14
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

Verification for the 3andemployee: 12 day(s) returns to 9 day(s) and therefore position 3
25 years returns to 21 years hence position 5
The couple 3, 5 in the table indeed gives 200.00 €.
Procedure:
Three beaches to name for the calculation of the bonus:

scale of absences Left outer column ='Detail premiums'!$B$30:$B$34

seniority scale Upper line ='Detail premiums'!$C$29:$F$29
family prime table Central part ='Detail bonuses'!$C$17:$E$21

To write in cell J9 of the main sheet:

=INDEX(tableprimepresence;MATCH(NB_jours_Absence;echelleabsences;1);
EQUIV(Seniority; seniority_scale; 1))

Certainly, this may seem "somewhat" obscure, but we find the syntax
=INDEX(range ; row_number ; column_number) with MATCH() used 2 times, precisely for
send back these 2 positions.
And ... at the general request and in total hilarity but ... certified to be functioning and
left in J10 for fun!
=INDEX('Detail premiums'!$C$30:$F$34;MATCH(List!$F$9:$F$65536;'Detail premiums
!$B$30:$B$34;1);MATCH(List!$G$9:$G$65536;'Detail premiums '!$C$29:$F$29;1))
Copy two or three lines down from J10 and note that the gas plant
works.
In simpler terms, but on the same basis, we will take care of the table.
of honor of absences.

3.9.

Page : 15
IUT GEA Aix-en-Provence PC Excel Add-ins Version XL 2003

=MATCH(), INDEX() and MIN() or MAX() to distinguish the extremes

What is the highest revenue achieved and in which month?

How much is the most beautiful sale and who made it?
This type of question is essential, and being able to answer it will give you an aura.
incomparable ... with the nuance that in case of a tie, only the 1ermet is cited.
Hence the relevance of the message in column J since two employees have the wonderful
record of only 1 day of absence. (Adeline Cuban will still have the
satisfaction of appearing in bright green).
It should be noted that our attention in this theme focuses on highlighting the
extreme in terms of the number of days absent and the identity of the concerned employee
As has been the case throughout this course, let us first conduct an analysis.
before writing.
Line 3: the most absent
In F3, it is appropriate to return the position in the table of the one who reaches the
maximum of the series, in this case the cell in red is indeed in
19andposition. MAX(NB_jours_Absence) gives us the maximum of 31 and
EQUIV() allows you to obtain the rank of this maximum within the range.
NB_jours_Absence :
=MATCH(MAX(Absence_Days), Absence_Days, 0)
As explained in 2-8, we will indeed take the argument 0 from the formula because
It is indeed the position of the maximum that needs to be returned and that the range is not sorted.
croissant.
Immediately name the cell F3 "RANGMAX" and F6 "RANGMIN."
In G3, H3, and I3 we use INDEX() to write the last name, first name, and number.
days of absence of the element positioned at the rank of the maximum either in
our case in position 19 (hence the name "RANGMAX" for cell F3).
In G3: =INDEX(Name;maxrank)

In H3: =INDEX(FirstName;maxRank)

In I3: =INDEX(NB_days_Absence;rankmax)

Working on a single column, a single argument is enough for INDEX()

Page : 16
IUT GEA Aix-en-Provence PC EXCEL Add-ins Version XL 2003

The message in J3 pertains to a classic IF() statement whose condition focuses on the
number of records constituting the maximum. If there are ties,
number of values is greater than 1 and the message is displayed. Otherwise we
does not display anything. The yellow color is obtained through a CONDITIONAL FORMAT
reviewed on 1-4-3.

IF(COUNTIF(Absence_days, MAX(Absence_days)) > 1, "attention tie", "")

As only one person is at most, the message does not appear.
Important: It should be noted that the condition of the COUNTIF() regarding a formula, the
Quotation marks should not be written. This amends and completes section 2-4.
Line 6: the most present or the one with the least absences
We simply take up the formulas from the previous point but everything refers to
to MIN() instead of MAX() and of course the position of the most diligent is given by the
cell F6 that we have already named 'rankmin'.
In F6: the rank of the least absent in the list is 35
=MATCH(MIN(Days_of_Absence), Days_of_Absence, 0)
Returns of Name, first name and number of days of absence:
in G6: =INDEX(Name;minrank)
in H6 : =INDEX(FirstName;minRank)
in I6: =INDEX(NB_days_Absence; rangmin)
and finally, the message in J6 in the case of a tie which is the case here:
IF(COUNTIF(NB_jours_Absence, MIN(NB_jours_Absence)) > 1, "attention tied", "")

3.10. Additional macro to write numbers in words

Many administrative services are required to rewrite the calculated amounts.
in full words to prevent fraud.

A Belgian colleague created a supplementary macro that works on EXCEL but

also WORD and ACCESS. You can download it and find the instructions at
the following address: [Link]
Copy the macro [Link] into the MACROLIB folder of EXCEL
(search for .XLA on your hard drive to find the access path).
Install on EXCEL via TOOLS MACROS ADD-INS.
Once installed, the CHLETTRES() formula can be used like any other formula.
but ... does not support (to the jubilation of some) the use of the zones
nominated individuals I have pestered you about throughout the file!!!
En L9 on écrira donc =chlettres(K9;"F";"Eur") avec « F » pour une écriture francisée
and 'Eur' to add Euro and cents. Limit: 12 digits and 2 decimals.

Page : 17