Project assistance

[cdi_747]

Hello,

I am working on an assignment that requires to show a few outputs based on 5, 10, 20, 60, 90 day collected data. The problem I have been running into is how to set up future added days along with names of employees without making it complicated to add them. I also want to be able to show accumulated points based on previous days worked. Any ideas? (attachment shows idea)

 

[SirJB7]

Hi, cid_747!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file. I shifted a few rows down your actual formula totals and added the table totals feature (Table Tools floating tab, Table Style Options group, check Totals Row). For each column you can choose from the embedded drop down menu which type of total you want.

You should check if this automatic feature can replace your manually set by formula totals, since the formulas you placed there were motley (zero values, different ranges, etc.).

For the aging issue, I replaced the column headers (5 DAYS, 10 DAYS, ... ANNUAL) for the days value (5, 10, ... 360), so as to use them in the table body formulas:
O2: =SI.ERROR(SUMA(DESREF($N9;;;O$1/5*7;1))/SUMA(DESREF($C9;;;O$1/5*7;1));"") -----> in english: =IFERROR(SUM(OFFSET($N9,,,O$1/5*7,1))/SUM(OFFSET($C9,,,O$1/5*7,1)),"")
Copy across as required.

Just advise if any issue.

Regards!

 

[cdi_747]

Thank you so much for your reply. I have also done the new member posting as well. You taught me quite a few new things with this... I was wondering, in the formula you provided can you tell me what the 5 in the formula is doing please? (in red below)

=IFERROR(SUM(OFFSET($N9,,,O$1/5*7,1))/SUM(OFFSET($C9,,,O$1/5*7,1)),"")

Thank you again!

Hi, cid_747!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file. I shifted a few rows down your actual formula totals and added the table totals feature (Table Tools floating tab, Table Style Options group, check Totals Row). For each column you can choose from the embedded drop down menu which type of total you want.

You should check if this automatic feature can replace your manually set by formula totals, since the formulas you placed there were motley (zero values, different ranges, etc.).

For the aging issue, I replaced the column headers (5 DAYS, 10 DAYS, ... ANNUAL) for the days value (5, 10, ... 360), so as to use them in the table body formulas:
O2: =SI.ERROR(SUMA(DESREF($N9;;;O$1/5*7;1))/SUMA(DESREF($C9;;;O$1/5*7;1));"") -----> in english: =IFERROR(SUM(OFFSET($N9,,,O$1/5*7,1))/SUM(OFFSET($C9,,,O$1/5*7,1)),"")
Copy across as required.

Just advise if any issue.

Regards!

 

[SirJB7]

Hi, cdi_747!
What if I say that's the line rate for my 1st post? 2nd and followings are cheaper, just 4.95.
Now a bit more seriously, what if I say that I don't remember? And even worse, I don't realize after opening the workbook I uploaded and checking the formulas!
If I don't remember bad it was something about your formulas on a week (7 days) basis or something alike.
So let me go for a Carslberg and I'll come back in a while.
Regards!
PS: While stands for couple of that Danish nectar.

 

[SirJB7]

Hi, cdi_747!
Maybe it's because of this. Your original formula at O2 cell said:
=SUMA(N9;N16)/SUMA(C9;C16) -----> in english: =SUM(N9,N16)/SUM(C9,C16)
from wich I assumed that for 5 days (O1) you were taking 7 data days (N & C 9:16), so I changed the 1st row strings (5 DAYS, 10 DAYS...) to numbers (5, 10, ...) so as to handle the proper relation within the formulas (7 days for column O, 14 for column P and so).
If that wasn't correct, please elaborate a bit more regarding you O column formulas (why just 7? do fill the whole column for a couple of numbers, e.g., 5, 10 & 60).
Hope it helps and I guessed fine.
Regards!

 

[cdi_747]

LOL!!! U have me cracking up! I think your guesses were great. I hope to be a Guru of excel and simply forget the genius that I display to people everyday! LOL!

I have changed the format to give more control to the data as it accumulates over time.

So ultimately when members work they earn points for each assignment distributed to them in a given day. I will need to set up a formula that can tally how many points a member has been given per hours worked over the last 5, 10, 20, 60, 90, 365 days. I will also need to be able to add members and remove them as they are fired and hired.

Ex. If Joan works Nov 20, we need to show how many points per hour worked she has accumulated on a 5 day total. So adding up points earned per hour worked for Nov 16-20 for the 5 day illustration would be ideal.

I am looking for a formula that will take TODAY into consideration and calculate back the last 5 days in order to show what she has accumulated. It would also be great to have the dropdown boxes in (A5:F5) and allow the user to select five days from a particular point other than today.

I have attached the same file with another tab showing the re-work and more days added to get a working model on the 5 day illustration. Thank you so much for your assistance as well.

 

[SirJB7]

Hi, cdi_747!

That can't be called a crack, just a formula analysis... a very different and much easier thing

About your new uploaded file, a few doubts:
a) For Joan, you say "If Joan works Nov 20", where apart from the file name can I derive that information from?
b) For the rolling periods of 5, 10, 20, missed 30?, 60, 90 and 365 days, you have dates in range C5:H5 that are fixed and neither related to the Nov 20 date nor to the periods in range C3:H3.

So please clarify that and upload the fixed file which reflects the correct information wanted to be displayed, entering manually the output required so as to have a clear and thorough idea of what you're looking for.

Regards!

 

[cdi_747]

Ok. I understand what you are saying. The file being named 11-20 has nothing to do with desired results. Only that the file was created that day. I'm sorry for the confusion.

I will go with other examples to help with what I am truly looking to do. I cant figure it out, as it is a bit of a complex formula, I think....

I would like to select a date in C5 (Dec 2) and have the totals show below this date. The example I have in the file works perfect aside from the fact that if I change anything (C5:H5) the formula does not shift the selection. ie. changing the drop down box to Dec 1, it should return Nov 27 - Dec 1.

I hope this makes sense. Thank you for your assistance!

 

[SirJB7]

Hi, cdi_747!

Try this:
a) Define a dynamic named range for the dates in row 5 from column J in advance:
DayList: =DESREF(Sheet1!$J$5;;;1;CONTARA(Sheet1!$J$5:$XFD$5)) -----> in english: =OFFSET(Sheet1!$J$5,,,1,COUNTA(Sheet1!$J$5:$XFD$5))
b) Place this formula in C7 and copy across and down as required:
C7: =SUMA(INDIRECTO(DIRECCION(FILA();MAX(COINCIDIR(C$5;DayList;0)+COLUMNA(DayList)-SI.ERROR(C$3*1;365);COLUMNA(DayList));4;1)&":"&DIRECCION(FILA();COINCIDIR(C$5;DayList;0)+COLUMNA(DayList)-1;4;1))) -----> in english: =SUM(INDIRECT(ADDRESS(ROW(),MAX(MATCH(C$5,DayList,0)+COLUMN(DayList)-IFERROR(C$3*1,365),COLUMN(DayList)),4,1)&":"&ADDRESS(ROW(),MATCH(C$5,DayList,0)+COLUMN(DayList)-1,4,1)))

Regards!

 

[cdi_747]

AMAZING!!!!!!!!!

What you put here works like a dream. You also gave me some things to learn because I'm not 100% sure of how you thought through this. You are really good with this...

I made the table with the dates from 1/1 to 12/31 like you mentioned. Things work just fine but I dont know where to put the OFFSET formula you show here. I placed the formula in C7 and things still work good. Am I missing something?

What is a good course in your opinion to learn how to think through and produce such a result? Thank you so very much with this problem!

 

[SirJB7]

Hi, cdi747!

The OFFSET formulas are used to define dynamic named ranges, so you should enter them via the Name Manager (Formulas tab, Defined Names group). If you didn't so and you say that the results are correct, I think you're wrong about the correctness of the results, so upload the file with the formulas yet used.

About where to learn Excel you might find a lot of references searching within this site. What you won't learn anywhere but inside of your mind is how to think, that's off the scope of any course, neither of Excel nor VBA; it's just a combination of logic, intuition and hours of getting wrong.

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

 

[cdi_747]

I'm sure you know how to do this but what I did was highlight the dates row from 1/1 - 12/31 and named it in the upper left corner near "fx", "DayList"... I am still unsure as to where to place the =OFFSET formula you gave. Sorry.

Also, I was wondering if there was a way to exclude weekends/holidays in the formula you provided? In my "DATA" tab there is a data validation list with the holidays needed. I was trying to insert =NETWORKDAY inside of the formula you gave and do not know where to place this...

Where to place this formula is why I would like a class on excel. I have been working in excel for many years and typically know more than most people who I am around but there is still so much more to learn.

Attached you will find the updated file. Lastly, I was reading that you were helping a lady and she mentioned a newsletter. Where can I find that? I would like to read more about you as well as Chandoo, and I would love to chime in and help people where I can like you have helped me.

Thank you again.

 

[SirJB7]

Hi, cdi_747!

DateList.
It's the same but you defined a fixed range that doesn't allow to insert cells before the beginning and after the end. To define it dynamically do this:
- Go to Formulas tab, Defined Names group, Name Manager icon
- Click on DayList, press Edit
- Paste the definition from the earlier post into the Refers To text box and click on Accept

Weekends/Holidays.

I am looking for a formula that will take TODAY into consideration and calculate back the last 5 days in order to show what she has accumulated.

I made the table with the dates from 1/1 to 12/31 like you mentioned.

You never mentioned skipping days, so the formulas are now considering N (5, 10, ...) days (rows) for building the ranges. That should be updated, probably using function NETWORKDAY/S as you guessed.

Newsletter.
In this page, click on Home (top left), and in the right side you'll find Join The Newsletter.

Regards!