I need help with making a Excell spreadsheet

[foolanger]

Hello... Let me explain my current situation. I currently have a cellular phone store. Our employees gets paid comission based on phone pricing and also the plan that they choose. I need to set up a system where i can put in a 3 things.. a phone price, which phone, and which plan.. and after i put this stuff in i need for it to spit me a number. For Example:

John Smith sold a Nokia 3390 for $0 on the Tmobile $39.99 plan.

I want to put in.. $0 , have a pull down bar if possible for which plan they chose.. or something along those lines.. for which plan then phone.. then spit out a number at the end.. If we can do that that would save workes hours of time. Please provide any suggestions. thank you very much in advance for all your help..

 

[Electric Amish]

Pull downs are pretty easy. Read the help.

It basically just references a defined area of cells that you input the things you want in the pull-down.

amish

 

[HomeBrewerDude]

formulas please.

 

[foolanger]

well it gets a bit complicated.. there are numerous plans and for each plan the compensation is different... There are even more phones available.. Here is the gist of it all..

these are all hypothetical..

$39.99 and higher pays $100 comission
$29.99 pays $70 comission
$19.99 pays $40 comission

Phones:
Nokia 3390 Costs Us $100
Motorola V66 costs us $150
Motorola V60 costs us $200


Here employees are allowed to discount up to $100 per phone without hurting there compesation listed above. However if they discount the phone less then $100 they get 50% of what they did not discount it by. So if they sold a Nokia 3390 for $50 they would get $25 on top of whatever comission they would be paid. If they give the Motorola V66 for free then they loose $50 off their comission. And again there are a bunch of phones and a lot of plans for 4 carriers. thank you again for all your help..

 

[bcterps]

It would probably be easier to code something in Visual Basic.

 

[Rogue]

Depending on the number of phones and records you intend on managing, it sounds like a good job for MS Access to me. Access has a learning curve to it, but there are some templates available in it that you could probably adapt with a little bit of reading of help files and maybe a "For Dummies" book or two.

 

[foolanger]

someone mentioned using macro.. does anyone know anything about that?

 

[minendo]

Originally posted by: foolanger
someone mentioned using macro.. does anyone know anything about that?

An excel macro is basically a visual basic program within a spreadsheet.

 

[foolanger]

ok.. my next question is what is visual basics?

 

[minendo]

Originally posted by: foolanger
ok.. my next question is what is visual basics?

Visual basic is a Microsoft Programming language usually sold with the Visual Studio package.

 

[HomeBrewerDude]

you don't need macros. and Excel is the more appropriate option i think.

just set up your spreadsheet so that you calculate intermediate values. Use lots of columns. Once you calculate intermediate values, you can refer to that cell in your final formula.


label your columns in your first row. Make it simple at first and just worry about 1 employee....you could set up sheets so that every employee has their own worksheet (just copy and past formulas once you perfect it)

Date Plan PlanBASE PlanPRICE Phone PHoneBASE PHONEPRICE etc.....


Create new fields, as you determine they are needed. Soon you will be able to use the intermediate values to calculate an overall Commission.

 

[foolanger]

yamaha... that sounds like what i was gonna do.. but for a 39.99 rate plan is there a way of putting in a pull down or something.. or for the comptuer to just recognize a 39.99 rate plan as a pre specified value.. or do i have to put in $100 instead?

 

[HomeBrewerDude]

Originally posted by: foolanger
yamaha... that sounds like what i was gonna do.. but for a 39.99 rate plan is there a way of putting in a pull down or something.. or for the comptuer to just recognize a 39.99 rate plan as a pre specified value.. or do i have to put in $100 instead?

There are several ways of doing this.

First, how to minimize the amount of typing when entering inthe plan name. You can go Tools>Options>Custom List and define the names for a custoom list. To bring up the list you just right click the cell and select "pick from list"

To match the plan value to the the plan type you can use the Lookup function

example:

=HLOOKUP(I11,L$11$13,2) matches the value in cell I11 to the first row of the group of cells between L11 and O13, the value returned is the value in that column next row down.

 

[foolanger]

ty for your help all.. i haven't started this project of mine yet.. but when i do you will definitely be hearnig for me.. thanks again for all your help..

 

[jw791]

I'm not sure I understood your commission formulas correctly, but it was fairly simple

Right Click - Save Target As


Cellphone.xls (Excel 2000 format)

 

[WarmAndSCSI]

<smartass>Do you mean "Excel"?</smartass>

 

[foolanger]

 

[foolanger]

JW791, how did u do that?