Jump to content


Excel Help


  • Please log in to reply
16 replies to this topic

#1 SuperJon

SuperJon

    <3

  • Members
  • 6,664 posts
  • Gender:Male
  • Location:Florida

Posted 19 November 2014 - 03:56 PM

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.

Attached Files



#2 SuitedAces21

SuitedAces21

    once and future king

  • Members
  • 24,871 posts
  • Gender:Not Telling

Posted 19 November 2014 - 04:04 PM

you came to the right place.
Spoiler

#3 Napa Lite

Napa Lite

    Poker Forum Veteran

  • Members
  • 7,730 posts
  • Gender:Male

Posted 19 November 2014 - 04:09 PM

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.

#4 Napa_Don

Napa_Don

    I'm Your Huckleberry

  • Members
  • 8,370 posts
  • Gender:Male

Posted 19 November 2014 - 04:46 PM

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


Posted Image

#5 SuperJon

SuperJon

    <3

  • Members
  • 6,664 posts
  • Gender:Male
  • Location:Florida

Posted 19 November 2014 - 04:53 PM

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?

#6 Napa_Don

Napa_Don

    I'm Your Huckleberry

  • Members
  • 8,370 posts
  • Gender:Male

Posted 19 November 2014 - 05:02 PM

Posted Image

#7 SuperJon

SuperJon

    <3

  • Members
  • 6,664 posts
  • Gender:Male
  • Location:Florida

Posted 19 November 2014 - 05:26 PM

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.

#8 hank213

hank213

    inventor of the tactical turtleneck..the tactilneck

  • Members
  • 9,527 posts
  • Gender:Male
  • Location:Danger Zone

Posted 19 November 2014 - 05:37 PM

not with right number of mexicans.
Bloody Mary, full of vodka, blessed are you among cocktails. Pray for me now at the hour of my death, which I hope is soon. Amen.

#9 Napa_Don

Napa_Don

    I'm Your Huckleberry

  • Members
  • 8,370 posts
  • Gender:Male

Posted 19 November 2014 - 05:49 PM

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.

#10 SuitedAces21

SuitedAces21

    once and future king

  • Members
  • 24,871 posts
  • Gender:Not Telling

Posted 19 November 2014 - 06:46 PM

easy nap. don't want your bp to get over 250 again.
Spoiler

#11 SuperJon

SuperJon

    <3

  • Members
  • 6,664 posts
  • Gender:Male
  • Location:Florida

Posted 19 November 2014 - 06:47 PM

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

#12 Napa_Don

Napa_Don

    I'm Your Huckleberry

  • Members
  • 8,370 posts
  • Gender:Male

Posted 19 November 2014 - 07:04 PM

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?

#13 mrfritz

mrfritz

    Poker Forum Veteran

  • Members
  • 2,561 posts
  • Gender:Male
  • Location:Vitamin Barn

Posted 19 November 2014 - 07:14 PM

tombstone is on
This is my first rodeo

#14 hank213

hank213

    inventor of the tactical turtleneck..the tactilneck

  • Members
  • 9,527 posts
  • Gender:Male
  • Location:Danger Zone

Posted 19 November 2014 - 07:16 PM

perhaps it is mrfritz, but i do believe our young napa don is currently engaged watching point break.
Bloody Mary, full of vodka, blessed are you among cocktails. Pray for me now at the hour of my death, which I hope is soon. Amen.

#15 SuitedAces21

SuitedAces21

    once and future king

  • Members
  • 24,871 posts
  • Gender:Not Telling

Posted 19 November 2014 - 07:34 PM

personally i'm watching the office on netflix
Spoiler

#16 brvheart

brvheart

    I'm the best.

  • Members
  • 25,352 posts
  • Gender:Male
  • Location:Toyko, Japan
  • Interests:Playing in nuclear fallout.
  • Favorite Poker Game:I play 100/200 live with my best friend Jason.

Posted 19 November 2014 - 07:45 PM

Man, I miss Chris Farley.

View PostiZuma, on 20 August 2012 - 11:32 AM, said:

napa I was jesus christing suited, you guys just slipped in before me.

View PostEssay21, on 25 February 2013 - 08:32 PM, said:

.

#17 SuitedAces21

SuitedAces21

    once and future king

  • Members
  • 24,871 posts
  • Gender:Not Telling

Posted 19 November 2014 - 07:49 PM

we all do, brvy. we all do.
Spoiler




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users