The sales rep commission rate varies for each sales rep. In column D, Doug used
ID: 3879252 • Letter: T
Question
The sales rep commission rate varies for each sales rep. In column D, Doug used a VLOOKUP function to look up the commission rate for each sales rep, and then multiplied the commission rate by the invoice amount to calculate the commission. Although the first two rows in column D of the Excel table named Aging display the correct commission, all the other cells display #N/A. Find the problem with the formulas in the Commission column and fix it
Below are invoice table and the Commission Rates table for each sale rep. Also the Vlookup formula =VLOOKUP(C6,Commission!A6:B9,2)*F6
Invoice Nbr Company Sales Rep Sales Rep Commission Invoice Date Invoice Amount Days Past Due Current 1-30 days 31-60 days 61-90 days Over 90 daysExplanation / Answer
Screenshot:
Explanation:
Please note that I have used only the first 10 sample entries for finding the solution which has only 4 agents. Please read through the following explanation and apply the logic in your code.
The issue with the existing vlookup function was that, the reference range contained a varying row number in it. For example, the function will be "=VLOOKUP(C1,Commissions!A1:B4,2)*F1" when in cell C1 whereas when we move to the 3rd row, the function will become "=VLOOKUP(C1,Commissions!A3:B4,2)*F3" in cell C3. Notice that the reference range to look for the commission has reduced from A1:B4 to A3:B4 which results in the excel not able to find the value.
Inroder to have a fixed reference range with the row remaining fixed, we need to add a "$" symbol before the row number. So the vlookup function should be written as "=VLOOKUP(C1,Commissions!A$1:B4,2)*F1". This addition of a "$" symbol before the row number ensures that the row number remains unchanged across all the rows for calculation.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.