Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 80850 times)

Offline Dr Moose

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2013
  • Posts: 3958
  • Total likes: 85
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #240 on: November 19, 2014, 10:11:36 AM »
yeah, I rtm'd to merge these two threads ages ago...
umm, you only joined yesterday ....
Hey there! I am using DansDeals Forums.

Offline WQ

  • DansDeals Copper Elite
  • *
  • Join Date: Nov 2014
  • Posts: 11
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #241 on: November 19, 2014, 10:45:57 AM »
umm, you only joined yesterday ....
my point is still valid

Offline Ephcc90

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 505
  • Total likes: 3
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #242 on: November 19, 2014, 12:31:26 PM »
How in the world can I do this? I assume an IF formula.

"Employee shall be entitled to vacation only upon completion of 1 year of service.  1-7 years of service: 2 weeks paid vacation; 8-11 years of service: 3 weeks paid vacation; 12-15 years of service: 4 weeks paid vacation; 15+ years of service, employees may receive an additional 5 days of vacation. 

I have the date of hire in column D. I need to calculate it in terms of hours so assume every day is 7.5 hours.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9267
  • Total likes: 285
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #243 on: November 19, 2014, 12:39:03 PM »
How in the world can I do this? I assume an IF formula.

"Employee shall be entitled to vacation only upon completion of 1 year of service.  1-7 years of service: 2 weeks paid vacation; 8-11 years of service: 3 weeks paid vacation; 12-15 years of service: 4 weeks paid vacation; 15+ years of service, employees may receive an additional 5 days of vacation. 

I have the date of hire in column D. I need to calculate it in terms of hours so assume every day is 7.5 hours.
You need to specify what column(s) of data you have, and what you want the output column to contain.
Workflowy. You won't know what you're missing until you try it.

Offline Ephcc90

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 505
  • Total likes: 3
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #244 on: November 19, 2014, 12:43:11 PM »
You need to specify what column(s) of data you have, and what you want the output column to contain.
I have the date of hire in column D. I want to have the accrued time in AQ.  I basically want to say if they worked 1- 7 years then they get 75 hours, if they worked 8-11 they get 112.5, etc.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9267
  • Total likes: 285
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #245 on: November 19, 2014, 12:44:29 PM »
I have the date of hire in column D. I want to have the accrued time in AQ.  I basically want to say if they worked 1- 7 years then they get 75 hours, if they worked 8-11 they get 112.5, etc.
Okay, so the first step is to create a formula to figure out how many years they worked, based on their date of hire. You'll want to play around with the Date functions to find one that works for you.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2751
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #246 on: November 19, 2014, 01:04:00 PM »
I have the date of hire in column D. I want to have the accrued time in AQ.  I basically want to say if they worked 1- 7 years then they get 75 hours, if they worked 8-11 they get 112.5, etc.

Find a spot to put a table of the vacation allotments and name that range (let's say vacation_table).. The first column should be the lowest number of years you need to be eligible for that amount of vacation, and the second column should be the amount of vacation:
175
8112.5

Then the formula in AQ1 would be = IFERROR(VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0)

Offline Ephcc90

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 505
  • Total likes: 3
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #247 on: November 19, 2014, 01:12:41 PM »

Find a spot to put a table of the vacation allotments and name that range (let's say vacation_table).. The first column should be the lowest number of years you need to be eligible for that amount of vacation, and the second column should be the amount of vacation:
175
8112.5

Then the formula in AQ1 would be = IFERROR(VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0)
Thanks so much!!!

Offline Ephcc90

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 505
  • Total likes: 3
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #248 on: November 19, 2014, 01:43:00 PM »

Find a spot to put a table of the vacation allotments and name that range (let's say vacation_table).. The first column should be the lowest number of years you need to be eligible for that amount of vacation, and the second column should be the amount of vacation:
175
8112.5

Then the formula in AQ1 would be = IFERROR(VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0)
One more thing... I only want to count the employees who worked more than 30 hours. I have a column that says how many hours they worked. 
How do I add that?

Offline Toasted

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2013
  • Posts: 922
  • Total likes: 63
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Problem
« Reply #249 on: November 19, 2014, 01:52:37 PM »
One more thing... I only want to count the employees who worked more than 30 hours. I have a column that says how many hours they worked. 
How do I add that?
Say hours worked is in column H.
 = IFERROR(IF(H1>30,VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0),0)

Offline Ephcc90

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 505
  • Total likes: 3
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #250 on: November 19, 2014, 02:11:48 PM »
Find a spot to put a table of the vacation allotments and name that range (let's say vacation_table).. The first column should be the lowest number of years you need to be eligible for that amount of vacation, and the second column should be the amount of vacation:
175
8112.5

Then the formula in AQ1 would be = IFERROR(VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0)
Say hours worked is in column H.
 = IFERROR(IF(H1>30,VLOOKUP(DATEDIF(D1,TODAY(),"Y"),vacation_table,2,TRUE),0),0)
Thanks so much guys!

Offline far rockaway

  • Dansdeals Gold Elite
  • ***
  • Join Date: Nov 2011
  • Posts: 122
  • Total likes: 0
  • DansDeals.com Hat Tips 3
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #251 on: November 20, 2014, 08:53:10 AM »
Anyone have an answer to create one generic formula for this? It would be greatly appreciated.

I have different from/to dates throughout the year with each period having a balance( ex. Weight in pounds) I am trying to create a list of each month and the weighted average pounds per month.

So column A is from date (ex. January 1, 2014) column B is to date ( January 6, 2014) and column C is balance in pounds. I then have each row with a different periods throughout the year with a different weight. I want to create a summary per month what the weighted average is.

Thanks,

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2751
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #252 on: November 20, 2014, 09:46:52 AM »
Anyone have an answer to create one generic formula for this? It would be greatly appreciated.

I have different from/to dates throughout the year with each period having a balance( ex. Weight in pounds) I am trying to create a list of each month and the weighted average pounds per month.

So column A is from date (ex. January 1, 2014) column B is to date ( January 6, 2014) and column C is balance in pounds. I then have each row with a different periods throughout the year with a different weight. I want to create a summary per month what the weighted average is.

Thanks,
Assuming the 'from' and 'to' months are the same for each entry (ie no entry spans 2 months) and that you have your data in rows 1-100 (just replace the references to 1 or 100 with your actual starting row and ending row of the data):

Put the formula =MONTH(A1) in column D. Put 1 through 12 in cells E1:E12 and =SUMIF($D$1:$D$100,E1,$C$1:$C$100)/COUNTIF($D$1:$D$100,E1) in cells F1:F12.

Offline far rockaway

  • Dansdeals Gold Elite
  • ***
  • Join Date: Nov 2011
  • Posts: 122
  • Total likes: 0
  • DansDeals.com Hat Tips 3
    • View Profile
  • Location: Far Rockaway
Re: Excel Problem
« Reply #253 on: November 22, 2014, 08:43:57 PM »
Assuming the 'from' and 'to' months are the same for each entry (ie no entry spans 2 months) and that you have your data in rows 1-100 (just replace the references to 1 or 100 with your actual starting row and ending row of the data):

Put the formula =MONTH(A1) in column D. Put 1 through 12 in cells E1:E12 and =SUMIF($D$1:$D$100,E1,$C$1:$C$100)/COUNTIF($D$1:$D$100,E1) in cells F1:F12.

Thanks.

This gets me the average. I'm looking to get the weighted average. Depending how many days in the month.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2751
  • Total likes: 127
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #254 on: November 22, 2014, 08:57:14 PM »
Thanks.

This gets me the average. I'm looking to get the weighted average. Depending how many days in the month.
Put = B1-A1 in column G. Then replace the COUNTIF function from my formula below with SUMIF($D$1:$D$100,E1,$G$1:$G$100)