Jump to content

Excel Help


Recommended Posts

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

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

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))

 

 

jrxPcUejrNEcnKxhFuuw.png

Link to post
Share on other sites

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

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.

  • Like 3
Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...