Define specific cells as per the maximum value

Microsoft Excel
You want to display the content of one
result cell in another which is to the
left of the relevant maximum value of
a specifi c data fi eld in the table sheet.



This task can be carried out using
a combination of three simple functions.
The functions are used in one single
formula. Just imagine: The fi elds “B1” to
“B4” contain four numbers. If the largest
number is in “B3”, the result cell should
return the value from “A3”. For this, enter
the formula given here:
=INDEX(A1:A4,
MATCH(MAX(B1:B4),B1:B4,0),1)
“MAX(B1:B4) fi rst identifi es the largest
value from the data set. This value serves
as a search criterion for the comparison
that is extended to the search matrix
“B1:B4”. With the comparison type “0”,
the function identifi es the position of the
fi rst value from the search matrix that is
identical to the search criteria.
The matrix variant of the “INDEX”
function then uses the searched value
from the value matrix “A1:A4” that is
located in the fi rst column of the row
defi ned by the comparison.

Read More

Display the month or day for a specific date

Excel 2003, 2007, 2010

You want to define the month or the

day for a specific date automatically in

a work sheet.

The “TEXT()” formula converts dates and times in

Excel cells into different text outputs.

Use the Excel function “TEXT()”.
It converts numeric values into text
outputs. Excel internally saves a date as
a numeric value. You can infl uence the
text output using the formatting sign and
can for instance determine the week day
for a particular date. The function has
two parameters: the numeric value to be
converted and the formatting sign with
inverted commas on both sides.

An example explains the process:
the “A1” cell has a date. You now want to display the week day for this date
in the adjoining cell “B1”. For this, enter
the following formula in the “B1” cell: -
TEXT(A1, “tttt”)
The formatting sign “tttt” coverts the
specifi ed date into a week day. If you only
require an abbreviation of the week day,
you can use the “ttt” format, for instance.
The time can be displayed in the 12-hour
format with an identifi er for mornings
(am) and afternoons (pm). For this, add
the desired identifi er “AM/PM”, “am/
pm”, “A/P” or “a/p” after the time format,
separated by a space.
These diff erent formatting signs
can also be combined. If, for instance,
you want to display the exact time
without the seconds, you can use
=TEXT(A1,“hh:mm”).
If the time is in the 24-hour format,
and Excel needs to display it in a 12-hour
format, then you can use =TEXT(A1, "hh.
mm am/pm"). The table (above) lists
the possible formatting signs and their
functions for date and time output.
Pay attention to the upper and lower
cases here.

Read More