Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 11-22-2012, 12:51 PM   #1
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default Excel question

My staff are paid based on a wage scale. Lets say :

Sales Pay
$1 - $10 $5
$11 - $20 $12
$21 - $30 $15
$31 - $40 $22

What I want to do is enter the $ amount of sales in one cell and have the adjacent cell give me the rate of pay. Thanks very much in advance!

KG
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 12:59 PM   #2
Bean
Scoring Winger
 
Join Date: May 2004
Exp:
Default

I'm far from an expert but if you had "if" statements where x is between whatever range to return the value you want, would that suffice?
Bean is offline   Reply With Quote
Old 11-22-2012, 01:01 PM   #3
Komskies
Franchise Player
 
Komskies's Avatar
 
Join Date: Mar 2009
Location: Calgary
Exp:
Default

If you're dealing in integer values, you could make a simple lookup table and use =vlookup to pull the Pay value.
Komskies is offline   Reply With Quote
Old 11-22-2012, 01:02 PM   #4
Sinny Darino
Farm Team Player
 
Join Date: Jan 2011
Exp:
Default

I am not an excel whiz or anything, but I think what you are wanting to do can be accomplished with a VLOOKUP...

Look online how to do this as it is a somewhat complicated step involving sheets in another tab and the list...
Sinny Darino is offline   Reply With Quote
Old 11-22-2012, 01:03 PM   #5
Super Nintendo Chalmers
First Line Centre
 
Super Nintendo Chalmers's Avatar
 
Join Date: Dec 2009
Exp:
Default

I'd use some of the database functions in excel if the pay scale has a lot of tiers and doesn't scale linearly but I don't have it installed on the PC I'm on to work it out. Basically, I'd have a table with the tiers and do a find based on the employees' sales.
__________________
FU, Jim Benning
Quote:
GMs around the campfire tell a story that if you say Sbisa 5 times in the mirror, he appears on your team with a 3.6 million cap hit.
Super Nintendo Chalmers is offline   Reply With Quote
Old 11-22-2012, 01:09 PM   #6
Senator Clay Davis
Franchise Player
 
Senator Clay Davis's Avatar
 
Join Date: Apr 2012
Location: Maryland State House, Annapolis
Exp:
Default

IF statements or VLOOKUP would probably both work here.
__________________
"Think I'm gonna be the scapegoat for the whole damn machine? Sheeee......."
Senator Clay Davis is offline   Reply With Quote
Old 11-22-2012, 01:16 PM   #7
Bean
Scoring Winger
 
Join Date: May 2004
Exp:
Default

I'll change my suggestion to VLOOKUP as well. And remember to use $A$1 so your formula values don't shift if you copy/paste/move things around.
Bean is offline   Reply With Quote
Old 11-22-2012, 01:23 PM   #8
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

Cool, thanks guys, I'll check out VLOOKUP.
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 01:27 PM   #9
Komskies
Franchise Player
 
Komskies's Avatar
 
Join Date: Mar 2009
Location: Calgary
Exp:
Default

If you want to use an If statement, you could set it up like this:



The 25 at the very end of the formula was the return value I used for any sale over $40.
Komskies is offline   Reply With Quote
Old 11-22-2012, 01:31 PM   #10
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

Ohhhh nice Komskies! You just earned yourself a beer!*





*Can be redeemed at any future CP Summit.
KevanGuy is offline   Reply With Quote
The Following User Says Thank You to KevanGuy For This Useful Post:
Old 11-22-2012, 01:33 PM   #11
Komskies
Franchise Player
 
Komskies's Avatar
 
Join Date: Mar 2009
Location: Calgary
Exp:
Default

vlookup example:

Komskies is offline   Reply With Quote
Old 11-22-2012, 02:23 PM   #12
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

OK so I got the whole formula input and it all looks right, except at the point of the error highlighted below it stops pointing to the I2 cell for some reason.



Help?
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 02:59 PM   #13
Komskies
Franchise Player
 
Komskies's Avatar
 
Join Date: Mar 2009
Location: Calgary
Exp:
Default

Interesting error there. It's hard to tell what's causing it. At first I thought it might be conflicting logic, but that doesn't seem to be the case.

If it helps you can separate the formula into lines in the formula bar with ALT+Enter. You could give each If statement it's own line to help pinpoint the error possibly.
Komskies is offline   Reply With Quote
Old 11-22-2012, 03:01 PM   #14
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

OK, I'll give it a shot. It thought there might be a limit to the number of IFs in there. If I can't get it to work I'll try the VLOOKUP, thanks. I'll let you know how it goes.
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 03:03 PM   #15
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

Separating the formula didnt work. I'll give VLOOKUP a shot tomorrow, thanks again for your help.
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 03:03 PM   #16
Komskies
Franchise Player
 
Komskies's Avatar
 
Join Date: Mar 2009
Location: Calgary
Exp:
Default

That's Excel 2003, right? Looks like you're limited to nesting 7 functions within a formula. They increased it to 64 in 2010. That's probably your problem right there.

http://office.microsoft.com/en-us/ex...005199291.aspx

The good news is, you probably coded your formula correctly after all!
Komskies is offline   Reply With Quote
Old 11-22-2012, 03:05 PM   #17
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

Yeah, I keep asking to get upgraded from 03 here but they haven't done that yet. Maybe this will finally get me upgraded, thanks. And yay for me for doing the formula right!
KevanGuy is offline   Reply With Quote
Old 11-22-2012, 03:08 PM   #18
Regorium
First Line Centre
 
Join Date: Apr 2006
Location: Calgary
Exp:
Default

Excel has a limitation of 7 nested ifs - that is, an "if statement inside of another if statement".

You can see that your equation fails at the 8th if.

As you can see with your massive equation, it is absolutely NOT an efficient way of using Excel.

Komskies VLOOKUP is far more efficient. There are even better advanced techniques, but a VLOOKUP should be more than sufficient for what you need.

I made this lookup table based on your massive IF statement. It should work perfectly for you.



The left side you just enter those values in based on your pay scale (use the minimum of the range: ie. 0-28.57, I just put 0, and have 28.57 in the next row).

Last edited by Regorium; 11-22-2012 at 03:13 PM.
Regorium is offline   Reply With Quote
Old 11-22-2012, 03:11 PM   #19
KevanGuy
Franchise Player
 
KevanGuy's Avatar
 
Join Date: Sep 2002
Location: Estonia
Exp:
Default

Nice, thanks. I was pretty sure there was a more efficient way to do it. Guess I killed two birds with one stone, learned something helpful in Excel and will be finally getting the 10 upgrade!
KevanGuy is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 10:37 PM.

Calgary Flames
2023-24




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2021