How to use nested function!

[ashleigh13]

Hi I am working on a question that I am really stuck on, I get the idea of what I am supposed to do, but the formula does not translate to all the cells I need to copy it to.
Here is the question:

On the Sales worksheet, enter a nested function in cell J8 of the Bonus column to display the bonus amount. If the employee sold $200,000 or more (cell J2) AND is International (cell I4), he or she earns a 5% (cell J4) bonus on his or her total annual sales; otherwise, the representative earns 3% (cell J3) of his or her total annual sales. Use relative and mixed (or absolute) references correctly in the nested function. Copy the function from J8 to the range J9:J20.

[samba_ravi]

Hi, ashleigh13
Welcome to the forum


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[adhawan06]

Hi There,

You didn't mention where the annual sales is mentioned however refer to following formula and enter it in J8..

=IF(AND(J$2>=200000,I$4="International"),$J$4,$J$3)

Let me know if this solves your purpose.

Thanks,
Anil Dhawan

[Alf]

Perhaps this link could also be of interest?

http://mcgimpsey.com/excel/variablerate.html

Alf

[ashleigh13]

The annual sales is located in cell H8 - where would that fit into the formula?

[ashleigh13]

Here is the data:

Screen Shot 2017-11-07 at 11.42.05 AM.png

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.