OverviewStep 1 creating a seriesStep 2 adjust row heightsStep 3 add column totalsStep 4 add a names columnStep 5 add column headersStep 6 add currency formattingStep 7 freeze the column headingsStep 8 add a Salary columnStep 9 add a conditional formulaStep 10 add a COUNTIF formulaStep 11 utilising binary cellsStep 12 applying Conditional FormattingStep 13 adding text cellsStep 14 adding an AVERAGEStep 15 adding calculationsStep 16 adding a FilterStep 17 add a Pie Chart graphicStep 18 using the IF and CONCAT functionsADDITIONAL Work with timesADDITIONAL PrintingAdvanced Topic VLOOKUPsAdvanced Topics Pivot Tables
Excel training
Written: Mar-2024

IF and CONCAT

On this step we want to use the two Excel functions IF(a,b,c) and CONCAT(a,b,c) function. The arguments for these are as follows:

=IF(a,b,c)Test a and if it's true then return b, otherwise return c, e.g. =IF("a"="b", 1,0) will check if "a" = "b" and as they are different then it will return "0"
=CONCAT(a,b)Concatenate the text strings a and b, and any number of other strings if you include them, e.g. =CONCAT("a","b","c","d") will return "abcd"

Steps

  1. add another new worksheet (Sheet3)

  2. on cell A1 we are going to build up a multi-point formula starting with the text =IF(. Then while still focused on the Formula Bar input field, click on the Sheet1 sheet from the worksheets tabs at the bottom of the workbook, then select cell E2. Then continue typing in the Formula Bar to add ...=1,CONCAT( then click on cell B2 (resulting in =IF(Sheet1!E2=1,CONCAT(Sheet1!B2). Continue this with ," ", to concatenate a space after the B2 name, and then click on cell D2 to make =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2. Finish this concatenation function with a ). This completes the CONCAT function which was the positive action of this IF function, but we also need to add in the negative action, so finish off the formula with ,"na") to make the final formula : =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")

    To review this IF we should understand the following parts of the formula: =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")...if
    =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")...test
    =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")...positive result
    =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")...negative result
    Press return on the formula bar to commit this

  3. Drag this new cell value down from A1 to A20

  4. To improve this further - we can add in a format instruction onto the salary part of the CONCAT - using the format £#,###0 as the second argument to the function "TEXT(a,b)", e.g. =IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",TEXT(Sheet1!D2,"£#,###0")),"-")
square

Also see

articles/Excel101square

Comments

New Comment

NOTE: (Put code blocks in [[[ and ]]] markup to be formatted.)

(Posted comments will be checked by the administrator before being published)



PrivacyCookies