SuperJon 175 Posted November 19, 2014 Share Posted November 19, 2014 Attached a sample workbook. There would be much more data in the actual workbook. What I'm looking for needs to be done in Sheet 2. User would select contractor from a drop down (A2). Then they would enter how many units are going to be installed (A5). Then a formula (A8) would give the approximate # of days to install based on how many units are entered. I figure this can be done by setting up some sort of Range or AverageIf and Vlookup. Maybe something like look up how many days on average it takes the contractor to install between 1 and 10 units, 11 and 20 units, and anything over 20 units. This is beyond my level of Excel knowledge, so any guidance is appreciated. Sample.xls Link to post Share on other sites
SuitedAces21 2,722 Posted November 20, 2014 Share Posted November 20, 2014 you came to the right place. 1 Link to post Share on other sites
Napa Lite 3,278 Posted November 20, 2014 Share Posted November 20, 2014 You're lucky I had my monthly venture out of just refreshing the sick thread. I'll have you know though that I don't have a lot of money in my bank account and my identity is not worth stealing if this is some ruse to put a virus on my computer when I'm just trying to help you. Link to post Share on other sites
Napa_Don 688 Posted November 20, 2014 Share Posted November 20, 2014 Here you go. I'm working under the assumption you know how to make the drop down menu for contractor and can also update the necessary cell/range references. When you "enter" the formula you have to hold CTRL+SHIFT+ENTER to make it an array formula otherwise it won't work. =IFERROR(INDEX(E4:H9,MATCH(1,(E4:E9=A2)*(F4:F9<=A5)*(G4:G9>=A5),0),4),INDEX(E4:H9,MATCH(1,(E4:E9=A2)*(G4:G9<=A5),1),4)) Link to post Share on other sites
SuperJon 175 Posted November 20, 2014 Author Share Posted November 20, 2014 Sweet. I'll give it a try. One question (for now at least) Under the Days/Install column. If an install could take longer than 6 days, I assume the following changes would need to be made Add more days under column H. Change E4:H9 to E4:Hx Is that right? Link to post Share on other sites
Napa_Don 688 Posted November 20, 2014 Share Posted November 20, 2014 2 Link to post Share on other sites
SuperJon 175 Posted November 20, 2014 Author Share Posted November 20, 2014 Ok I tried it out, but shouldn't it be referencing to the Data sheet? For example, 9999 units should take longer than 6 days. Link to post Share on other sites
hank213 1,823 Posted November 20, 2014 Share Posted November 20, 2014 not with right number of mexicans. 5 Link to post Share on other sites
Napa_Don 688 Posted November 20, 2014 Share Posted November 20, 2014 Listen you snot-nose little shit, I spend 9 hours a day making sheets like this while you're crapping in your hands and wiping it on your face. The chart I made (and location) is pure nonsense and is just there to illustrate the formula for simplicity. You'll have to determine what the appropriate levels of installs are and then the subsequent days required based on your own analysis and then you can put the chart into whatever tab you want as long as you update the reference. 3 Link to post Share on other sites
SuitedAces21 2,722 Posted November 20, 2014 Share Posted November 20, 2014 easy nap. don't want your bp to get over 250 again. 3 Link to post Share on other sites
SuperJon 175 Posted November 20, 2014 Author Share Posted November 20, 2014 Whoa easy there cracker. All you had to do was explain that to me the first go around. Rather than expect me to be some sort of fudging Uri Geller. But thanks xoxo <3 Link to post Share on other sites
Napa_Don 688 Posted November 20, 2014 Share Posted November 20, 2014 You're a real blue flame special, aren't you, son? Young, dumb and full of cum, I know. What I don't know is what you need this for. Guess we must just have ourselves an as.shole shortage, huh? 6 Link to post Share on other sites
mrfritz 885 Posted November 20, 2014 Share Posted November 20, 2014 tombstone is on 2 Link to post Share on other sites
hank213 1,823 Posted November 20, 2014 Share Posted November 20, 2014 perhaps it is mrfritz, but i do believe our young napa don is currently engaged watching point break. 6 Link to post Share on other sites
SuitedAces21 2,722 Posted November 20, 2014 Share Posted November 20, 2014 personally i'm watching the office on netflix Link to post Share on other sites
brvheart 1,752 Posted November 20, 2014 Share Posted November 20, 2014 Man, I miss Chris Farley. Link to post Share on other sites
SuitedAces21 2,722 Posted November 20, 2014 Share Posted November 20, 2014 we all do, brvy. we all do. Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now