excel functions

Question 1

concatenate

Answer 1

joins two or more text strings in one cell
=CONCATENATE(B17,” “,C17,” “,D17)

Question 2

VLookup

Answer 2

VLookup is a popular method used to match and reference information that resides in a table (array of
cells).
- exact match vlookup includes (false)

Question 3

if function

Answer 3

=IF(J2>=90,”A”,”Not A”)
The “IF” function can be used to perform complex logical considerations to perform
analytics.

Question 4

combining IF OR

Answer 4

=IF(OR(G59>5%,D59>200000),G59*D59,0)

Question 5

combining IF and

Answer 5

-(multiple conditions; both should be met)
‘=IF(AND(E47>=F47,D47>150000),D47*G47,0)

Question 6

‘=IF(NOT(G71=5%),G71*D71,0)

Answer 6

result will be displayed if the criteria is not met

Question 7

IF ERROR

Answer 7

=IFERROR(AVERAGEIFS($D$19:$D$132,$E$19:$E$132,G28,$B$19:$B$132,2010),0)

Question 8

PivotTables

Answer 8

allow a user to summarize large amounts of data using multiple views in a tabular format.
Data views can easily been manipulated for multiple perspectivesto facilitate data analysis. Users can
create custom aggregations; calculate fields, totals, and subtotals. Users can drill down