Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel


  • Please log in to reply
2 replies to this topic

#1 looney2340

looney2340

  • Members
  • 202 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:NYC
  • Local time:04:10 AM

Posted 10 November 2009 - 11:50 PM

Hi everyone,
I have a project due for a class and its been a long time since i worked in excel. I have to write some formulas and functions in excel i can do this in vb but it has to be done in excel here is a copy of the project and what is needed can anyone help me with how to write the function and formula to get it correct. I am given the following information i also send chart with the example figures that have to be used if someone can help with the functions and formulas....



You must create a workbook with separate sheets for each week (There are 4 weeks). Each worksheet should calculate the payroll amount for each of your six employees. If sales are below $1,000, then the commission paid is 5% of the sales. If sales are between $1,000 and $3,999.99, the commission paid is 10% of the sales. If sales are $4,000 or higher, the sales person receives a 12.5% commission rate.
Sales people will be paid either their commission or hourly pay earned amount—whichever is higher. Hourly employees receive 150% of their hourly rate for any hours worked over 40 hours per week (time and a half for overtime worked).
Each worksheet should contain the following headings:
• Employee
• Sales
• Hours Worked
• Hourly Pay
• Commission Earned
• Hourly Pay Earned
• Payroll Amount

To complete this workbook, you must write specific formulas and functions. The Commission Earned, Hourly Pay Earned (for the two hourly employees), and Payroll Amount columns require you to use IF functions. Remember, the payroll amount for salespeople will be either the commission earned or hourly pay earned—whichever is greater. Do not calculate commission earned for hourly employees or overtime for sales employees (this is anyone who has a sales figure in the Sales column).


Thank you everyone in advance for your help

BC AdBot (Login to Remove)

 


#2 AdamV

AdamV

  • Members
  • 65 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Leeds, UK
  • Local time:10:10 AM

Posted 12 November 2009 - 08:44 AM

It's going to be hard to help you without actually doing this for you, but here goes.

For the commission earned you will need to do various IF functions, nested within one another. An IF function takes the form:
=IF(<test>, <result if test is TRUE>, [<result if test is FALSE>])
This is equivalent to IF (test) THEN result if true ELSE result if false ENDIF (endif is simply indicated by the closing bracket)

You need to test if sales is >=4000, calculate based on 12.5%, else (test if >=1000, calc on 10%, else (test if >0, calc on 5%, else no result returned so you don't calculate it for people who have no sales, as per the spec))
this would be equivalent to
IF test
	THEN result
ELSE
	IF test2
		THEN result2
	ELSE
		IF test3
			THEN result3
		ELSE
			Null result returned
		ENDIF
	ENDIF
ENDIF

Because of the way the commision is structured, you don't need to test (>-1000 AND <4000) for example, because to get to the inner IF statements you have already excluded the other results.

To nest one function inside another, simply replace one of the result parts with the next condition, eg:
=IF(B2>=4000, B2*.125, IF(B2>=1000,B2*.1,<etc>... ) ) )

Personally I would use VLOOKUP against a lookup table rather than nested IFs for this as it would be much more flexible and extensible, but your project says to use IF statements (either because the person setting the course does not know better or because they want you to prove you can properly nest the IFs in the right order. I hope it's the latter)

Hope this helps.

If you try and write the actual functions out and post your ideas, I'm happy to help you fix any problems, but I would like to see you have put some thought and effort into this before simply "doing it for you" (unless you want to pay my usual consulting rates, of course :thumbsup: )
Due to global warming, eskimos now have more than 20 words for water John O'Farrell

Professional geek, consultant and trainer.
MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)
My personal blog - Getting IT right

#3 looney2340

looney2340
  • Topic Starter

  • Members
  • 202 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:NYC
  • Local time:04:10 AM

Posted 13 November 2009 - 11:17 PM

Hi Adam thanks for the reply i recognize the code in the beginning i can write it in vb ok i did an if statement like this in my sheet and it worked ok

1 - =IF(AND(D11>=H3, D11<F3),D11*H2,"No bonus") and it computed the commission as it should i checked with a calculator and im going to try and test the other commission rates all in one statement to see if it works as it should.

2 - I never used nested if statements ill try that as well to see which is easier and works as it should and also satisfy the instructions given.

3 - you had said.....

You need to test if sales is >=4000, calculate based on 12.5%, else (test if >=1000, calc on 10%, else (test if >0, calc on 5%, else no result returned so you don't calculate it for people who have no sales, as per the spec))
can i test it as i am trying in an =IF(and ( or is a nested if statement easier?

Ill try to continue over weekend they way i was trying and post if it has worked or not and post my statements for advise

I work 2 jobs so its hard to get here mon - fri at night but i will post until i get it right i love to learn new things


Thanks for the advise




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users