MS Excel Question - I doubt you know - Chicagoland Sportbikes
Chicagoland Sportbikes
 
Open Forum This forum is for all off-topic discussion.

 
LinkBack Thread Tools Display Modes
post #1 of 22 (permalink) Old 05-20-2008, 04:22 PM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
MS Excel Question - I doubt you know

EDIT - I found the solution...see the second page.

Is there a function or way to do the following (aside from writing an excessive amount of IF formulas)?



You've got a large set of numbers (say 300 random numbers in one column, each with their own cell). You know that some combination of numbers contained in the 300 add up to X (could be 2 numbers, could be 299 of the 300). You want to identify the subset that equals X and don't feel like sorting by value and doing the trial by error method...

So? Can it be done using excel?



With my interns at $100/hour and staff at $160+ (internally) this would be very helpful in my quest to keep this project on budget!!!

Thanks...

A superior rider uses superior judgment to avoid situations that require superior skill.

Last edited by DefyInertia; 05-21-2008 at 12:29 AM.
DefyInertia is offline  
Sponsored Links
Advertisement
 
post #2 of 22 (permalink) Old 05-20-2008, 04:27 PM
Registered User
 
Join Date: May 2008
Location: Illinois
Posts: 8,696
Location: Illinois
Sportbike: ninja
Years Riding: since I was a kid
How you found us: K.I.T.T.
           
you looking for more interns?


I don't know all the functions of excel. But you would need a function similiar to a sorting loop. Which would add two numbers together while going through the entire list.
taledarkside is offline  
post #3 of 22 (permalink) Old 05-20-2008, 04:32 PM
Veni, Vidi, Vici...
 
virtuaframax's Avatar
 
Join Date: Oct 2007
Location: Lincoln Park IL
Posts: 266
Location: Lincoln Park IL
Sportbike: '05 R6
Years Riding: 3rd season in USA, 2yrs in Italy
How you found us: kblack
           
can you use a sumif + conditional formatting in the function?

Francesco
'05 Yamaha R6 Raven

"Di notte Leoni, la mattina Coglioni"

Quote:
Originally Posted by Underdog View Post
You're in your 20s, living at home. How do you bang chicks?
You're not going to bang them on the back of your 500cc rent check that's for sure.
Move out - so you can throw wild parties and do drugs. Sell the leftover drugs to make the money for a bike.
There. Economics 101.
virtuaframax is offline  
post #4 of 22 (permalink) Old 05-20-2008, 04:33 PM
I can smell ur flapjacks
 
daywalker's Avatar
 
Join Date: May 2008
Location: Plainfield, IL
Posts: 51
Location: Plainfield, IL
Sportbike: Oh Seven Kwak zx6r
Years Riding: 1.5 dog years
How you found us: fate
           
yeah, you are right - I don't know. but I do know that if I had that kind of payroll, I would get one of them to figure it out and get my $$$ worth, lol

...hit up some searches on excel formulas. bound to get something...

"Kill your doubt with the coldest of weapons - Confidence."
daywalker is offline  
post #5 of 22 (permalink) Old 05-20-2008, 04:56 PM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
Anyone familiar with GoalSeek? Tools --> GoalSeek Someone in my office threw it out there...

How about the Solver add-in?

Quote:
Originally Posted by daywalker View Post
but I do know that if I had that kind of payroll, I would get one of them to figure it out and get my $$$ worth, lol
lol...that makes no sense! "per hour" I'm trying to keep their worklaod DOWN...looking ahead.

A superior rider uses superior judgment to avoid situations that require superior skill.
DefyInertia is offline  
post #6 of 22 (permalink) Old 05-20-2008, 05:04 PM
pfft.
 
ill_ag's Avatar
 
Join Date: Nov 2003
Location: Houston
Posts: 11,644
Location: Houston
Sportbike: 01 F4i (Well, I HAD one, anyway)
Years Riding: 7
How you found us: I google myself regularly
           
I use the goal seek function all the time...and all other sorts of magical excel tricks.... I think I could figure this out for you; unfortunately I couldn't get to it until friday. I am slammed and I have to teach a class tomorrow and thursday that takes me away from my office.
ill_ag is offline  
post #7 of 22 (permalink) Old 05-20-2008, 05:07 PM
Registered User
 
Join Date: May 2008
Location: Illinois
Posts: 8,696
Location: Illinois
Sportbike: ninja
Years Riding: since I was a kid
How you found us: K.I.T.T.
           
you might end up coding it in VBA. I don't see any built in functions of excel to do what you described.
taledarkside is offline  
post #8 of 22 (permalink) Old 05-20-2008, 05:14 PM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
ill_ag, I don't think I can afford your fees If you come up with anything, let me know. I will owe you one for sure.

Quote:
Originally Posted by taledarkside View Post
you might end up coding it in VBA. I don't see any built in functions of excel to do what you described.
That's what I am starting to think as well. I may have to create a file that I use as a tool...a single cell based function would be much less cumbersome.

EDIT - by the way, we are always hiring in the tax department but you'd need to be on your way to obtaining your CPA. And the interns only get paid ~25/hour...100 is just their billing rate (i.e., internal).

A superior rider uses superior judgment to avoid situations that require superior skill.

Last edited by DefyInertia; 05-20-2008 at 05:16 PM.
DefyInertia is offline  
post #9 of 22 (permalink) Old 05-20-2008, 05:21 PM
I can smell ur flapjacks
 
daywalker's Avatar
 
Join Date: May 2008
Location: Plainfield, IL
Posts: 51
Location: Plainfield, IL
Sportbike: Oh Seven Kwak zx6r
Years Riding: 1.5 dog years
How you found us: fate
           
Quote:
Originally Posted by DefyInertia View Post
lol...that makes no sense! "per hour" I'm trying to keep their worklaod DOWN...looking ahead.
what doesn't make sense is paying a c-note per hour for interns. that and coming on a MCY board asking programming questions... oh, and further limiting your results by prefacing said question with "I doubt you know".

I have been in IT for 10 years and even if I knew the answer, I wouldn't volunteer it with that approach.

"Kill your doubt with the coldest of weapons - Confidence."
daywalker is offline  
post #10 of 22 (permalink) Old 05-20-2008, 05:21 PM
pfft.
 
ill_ag's Avatar
 
Join Date: Nov 2003
Location: Houston
Posts: 11,644
Location: Houston
Sportbike: 01 F4i (Well, I HAD one, anyway)
Years Riding: 7
How you found us: I google myself regularly
           
You can generate random numbers in excel, and you can do a goal seek.....the problem is that you can't put wildcards into functions. I agree that you'd probably need to code this in VB. I poked around in excel for a minute just out of curiousity and I hit a wall...and I'm pretty imaginative in excel. I used to generate my expense reports with a random number generator to max out my perdiem without looking like I was trying to max out my perdiem. LOL
ill_ag is offline  
post #11 of 22 (permalink) Old 05-20-2008, 05:37 PM
[Online]
 
Fracker's Avatar
 
Join Date: Mar 2005
Location: Chicago
Posts: 3,698
Location: Chicago
Sportbike: Multistrada 1100S; CBR600RR
Years Riding: Since 2004
How you found us: CRR
           
Interesting problem.. Not sure if you can do it using built-in Excel functions.

- Stas

Thou shalt not disfigure the soul.
Fracker is offline  
post #12 of 22 (permalink) Old 05-20-2008, 05:40 PM
Registered User
 
Join Date: May 2008
Location: Illinois
Posts: 8,696
Location: Illinois
Sportbike: ninja
Years Riding: since I was a kid
How you found us: K.I.T.T.
           
Quote:
Originally Posted by daywalker View Post
what doesn't make sense is paying a c-note per hour for interns. that and coming on a MCY board asking programming questions... oh, and further limiting your results by prefacing said question with "I doubt you know".

I have been in IT for 10 years and even if I knew the answer, I wouldn't volunteer it with that approach.
every intern job I looked for was either volunteer or paid at most $15 an hour. I agree with you that if they are getting paid way too much for not knowing anything.
taledarkside is offline  
post #13 of 22 (permalink) Old 05-20-2008, 05:40 PM
[Online]
 
Fracker's Avatar
 
Join Date: Mar 2005
Location: Chicago
Posts: 3,698
Location: Chicago
Sportbike: Multistrada 1100S; CBR600RR
Years Riding: Since 2004
How you found us: CRR
           
Question - do you know for a fact that a subset of numbers adds up to X or you want to get the best approximation? Also - are those integers or floating points?

- Stas

Thou shalt not disfigure the soul.
Fracker is offline  
post #14 of 22 (permalink) Old 05-20-2008, 05:46 PM
[Online]
 
Fracker's Avatar
 
Join Date: Mar 2005
Location: Chicago
Posts: 3,698
Location: Chicago
Sportbike: Multistrada 1100S; CBR600RR
Years Riding: Since 2004
How you found us: CRR
           
You might want to look for solutions here: http://en.wikipedia.org/wiki/Knapsack_problem

- Stas

Thou shalt not disfigure the soul.
Fracker is offline  
post #15 of 22 (permalink) Old 05-20-2008, 06:04 PM
pfft.
 
ill_ag's Avatar
 
Join Date: Nov 2003
Location: Houston
Posts: 11,644
Location: Houston
Sportbike: 01 F4i (Well, I HAD one, anyway)
Years Riding: 7
How you found us: I google myself regularly
           
Quote:
Originally Posted by daywalker View Post
what doesn't make sense is paying a c-note per hour for interns.
That's a loaded rate paid by the client. Not what the intern makes.
ill_ag is offline  
post #16 of 22 (permalink) Old 05-20-2008, 06:21 PM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
Quote:
Originally Posted by Fracker View Post
Question - do you know for a fact that a subset of numbers adds up to X or you want to get the best approximation? Also - are those integers or floating points?
Generally we would know for a fact. The number should tie exactly. Basically you're looking at a set of numbers and trying to group them into predefined subtotals. It's a really basic exercise theory but isn't easy to execute, apparently.

Integers...I'm dealing with account balances that may or may not include pennies.

Thanks for the link.

Quote:
Originally Posted by daywalker View Post
what doesn't make sense is paying a c-note per hour for interns. that and coming on a MCY board asking programming questions... oh, and further limiting your results by prefacing said question with "I doubt you know".

I have been in IT for 10 years and even if I knew the answer, I wouldn't volunteer it with that approach.
I'm not sure why you got all butt-hurt by my post; I used a smiley...lol.

As I stated above, $100/hour is their billing rate, not their pay rate. It's used to analyze profitability and to a certain extent, generate bills....this is a concept I'm sure you're familiar with.

This is the Open Forum and there are a lot of smart people on this board...many of whom consult and/or work with computers. If I had to have the answer, I'd get it. This is more of a "it'd be nice to know" type of question and isn't presenting me with a roadblock or preventing me or my staff from moving forward.

"I doubt you know" - I would perceive this as a challenge. I guess you give up easy.

If you knew the answer you wouldn't volunteer it? Man, that's not very nice/cool/friendly/considerate/etc.

Quote:
Originally Posted by taledarkside View Post
every intern job I looked for was either volunteer or paid at most $15 an hour. I agree with you that if they are getting paid way too much for not knowing anything.
My interns are the shit, know a lot, and get paid market rates. Not sure where you're coming from with all that. Are you familiar with how much law school interns make? It certainly varies from industry to industry...I also came across many finance internships back in the day that offered "experience only".

A superior rider uses superior judgment to avoid situations that require superior skill.

Last edited by DefyInertia; 05-20-2008 at 11:58 PM.
DefyInertia is offline  
post #17 of 22 (permalink) Old 05-20-2008, 06:30 PM
Registered User
 
Join Date: May 2008
Location: Illinois
Posts: 8,696
Location: Illinois
Sportbike: ninja
Years Riding: since I was a kid
How you found us: K.I.T.T.
           
Quote:
Originally Posted by DefyInertia View Post
My interns are the shit, know a lot, and get paid market rates. Not sure where you're coming from with all that. Are you familiar with how much law school interns make? It certainly varies from industry to industry...I also came across many finance internships back in the day that offered "experience only".
The intern wage is what I have found when searching for a job during school. 0-15. Wages higher are given to people that are actual programmers. You made it out as if your interns didn't know what they were doing and you needed help with the excel code because your interns couldn't figure it out.
taledarkside is offline  
post #18 of 22 (permalink) Old 05-20-2008, 06:46 PM
pfft.
 
ill_ag's Avatar
 
Join Date: Nov 2003
Location: Houston
Posts: 11,644
Location: Houston
Sportbike: 01 F4i (Well, I HAD one, anyway)
Years Riding: 7
How you found us: I google myself regularly
           
I thought it sounded more like a "food for thought" post and that rather than spin wheels at these people's rates he'd try his hand at throwing it out to a bunch of folks who are, admittedly, typically bored during the workday and posting on clsb.
ill_ag is offline  
post #19 of 22 (permalink) Old 05-20-2008, 06:58 PM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
yeah...ill_ag got it...I was eating a late lunch and thought I'd start a thread.

I don't need an answer...this is something I've often thought about but never really looked into. No big deal either way...if it was a big deal we'd have system set up already...

Quote:
Originally Posted by taledarkside View Post
The intern wage is what I have found when searching for a job during school. 0-15. Wages higher are given to people that are actual programmers. You made it out as if your interns didn't know what they were doing and you needed help with the excel code because your interns couldn't figure it out.
My bad...that's not how it is, but I can see why you might have thought that.

My interns are business and law school students, they are not expected to be computer programmers; their expertise is elsewehre. They basically operate like a first year staff sans the client list. My industry is understaffed as a whole...so it pays but you have to work a lot.

A superior rider uses superior judgment to avoid situations that require superior skill.

Last edited by DefyInertia; 05-20-2008 at 07:03 PM.
DefyInertia is offline  
post #20 of 22 (permalink) Old 05-20-2008, 07:04 PM
Registered User
 
Join Date: May 2008
Location: Illinois
Posts: 8,696
Location: Illinois
Sportbike: ninja
Years Riding: since I was a kid
How you found us: K.I.T.T.
           
Quote:
Originally Posted by DefyInertia View Post
yeah...ill_ag got it...I was eating a late lunch and thought I'd start a thread.

I don't need an answer...this is something I've often thought about but never really looked into. No big deal either way...if it was a big deal we'd have system set up already...



My bad...that's not how it is, but I can see why you might have thought that.

My interns are business and law school students, they are not expected to be computer programmers; their expertise is elsewehre. They basically operate like a first year staff sans the client list. My industry is understaffed as a whole...so it pays but you have to work a lot.
gotcha. Thought you had computer science interns that couldn't tackle a simple problem.
taledarkside is offline  
post #21 of 22 (permalink) Old 05-20-2008, 07:06 PM
pfft.
 
ill_ag's Avatar
 
Join Date: Nov 2003
Location: Houston
Posts: 11,644
Location: Houston
Sportbike: 01 F4i (Well, I HAD one, anyway)
Years Riding: 7
How you found us: I google myself regularly
           
Quote:
Originally Posted by taledarkside View Post
gotcha. Thought you had computer science interns that couldn't tackle a simple problem.
Worse-- he's got business and law school interns that aren't used to working for their giant allowances
ill_ag is offline  
post #22 of 22 (permalink) Old 05-21-2008, 12:19 AM Thread Starter
Former Chicagoian
 
DefyInertia's Avatar
 
Join Date: Apr 2007
Location: San Francisco, CA
Posts: 864
Location: San Francisco, CA
Sportbike: 950 Super Enduro / FZ6 / 640 ADV
Years Riding: Never enough!
How you found us: SBN
           
Well, I gave it a shot after getting home from work tonight. I have a basic working solution using the Solver add-in. I'm going to tweak it and make it more user friendly when I have some free time. It needs a few more constraints to work properly with larger sets of numbers. Thanks for the help.



Oh yeah, is there a way to upload excel files to this thread? I tried once but it didn't work.
Attached Images
File Type: jpg Solver - 05.20.2008.jpg (70.2 KB, 9 views)

A superior rider uses superior judgment to avoid situations that require superior skill.

Last edited by DefyInertia; 05-21-2008 at 12:32 AM.
DefyInertia is offline  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the Chicagoland Sportbikes forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may 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 On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome