Derya has attended:
Excel Intermediate course
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Excel Intermediate course
Excel Advanced course
Excel "If" function
Question: Create an IF function in cell G6 which will calculate a salesperson's commission based on their total sales(Column). If the person's total sales are greater than 20,000 then they receive a commission of 5.5% of their total sales; otherwise they receive no commission. Use the cells J4 and J5 as part of your formula, and remember to make them absolute ($)???
F6 Column: Total £23,442
G6 Column: Commission
Commission calculations
Total sales of £20,000+ 5.50% (J4 Column)
Total sales under £20,000 No Comm (J5 Column)
Please can you submit the if formula to pit in the Commission cell G6 and please advise the short cut to make them absolute and where to add the dollar sign?
Many thanks
Derya
RE: Excel "If" function
Hi Derya,
Thank you for using the forum to ask a question.
I take it you are referring to question 3 on page 36 of the Excel Intermediate Manual which uses the practice file ‘Commission.xls’
The “IF” functions has 3 parts =IF(Logical Test,True,False).
In this example we want to test if the first line of total sales (F6) meets the Commission Criteria
Commission calculations
Total sales of £20,000+ 5.50% (J4 Column)
Total sales under £20,000 No Comm (J5 Column)
Logical Test: if Total Sales is greater than or equal to 20000
=IF(F6>=20000,
If that is true then Total Sales multiplied by 5.5% (J4)
=IF(F6>=20000,F6*$J$4,
If that isn’t true then the Total sales must be less than 20000 and will get No Comm (J5)
=IF(F6>=20000,F6*$J$4,$J$5)
G6 should therefore be =IF(F6>=20000,F6*$J$4,$J$5)
The $ signs make it absolute so that when the formula is dragged down, it will still point to the correct Column and Row for the Commission Criteria J4 & J5. When you type the formula if you press the F4 key (along the top of the keyboard) it will automatically drop in the $ signs for you.
Kind regards
Richard Bailey
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
