Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline Dr Moose

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2013
  • Posts: 4195
  • Total likes: 180
  • DansDeals.com Hat Tips 3
    • 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: 565
  • Total likes: 20
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 565
  • Total likes: 20
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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.

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • 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: 565
  • Total likes: 20
  • 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: 565
  • Total likes: 20
  • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2013
  • Posts: 1071
  • Total likes: 222
  • DansDeals.com Hat Tips 13
    • 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: 565
  • Total likes: 20
  • 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: 120
  • 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,

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • 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: 120
  • 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.

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • 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)

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #255 on: January 31, 2015, 07:43:08 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #256 on: January 31, 2015, 07:50:16 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

Do you have a master list anywhere of all customers?
If not, create one by copy pasting from all 12 months the customer account number onto one column of a new tab. Remove duplicates (under data toolbar) to have a master list of all customers billed during the year.

Then use a vlookup to pull the data from the various tabs. If each customer had several fees per month, first sort the various months and subtotal them to get the total for that customer for the month.

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #257 on: January 31, 2015, 07:58:47 PM »
Sounds good, il try that..

 Been working with array sumifs or's and's stuff but it hasnt  been working out..

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #258 on: February 01, 2015, 11:43:24 AM »
The vlookups werent working well because there were many duplicate entires and formating issues.

I ended up copying all clients from all months into column A, and all fees from all months into B. Then sorted by a/z and subtotaled.

#another twenty min job took 5.5 hours.  :-\

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #259 on: February 01, 2015, 11:57:32 AM »
The vlookups werent working well because there were many duplicate entires and formating issues.

I ended up copying all clients from all months into column A, and all fees from all months into B. Then sorted by a/z and subtotaled.

#another twenty min job took 5.5 hours.  :-\
OT for this thread, but it sounds like your situation might work better in Microsoft Access.
Workflowy. You won't know what you're missing until you try it.