Sum of weekdays on Weekends irrespective of Position

[Venkatesh1128]

i have created sheet for Sum of the Weekdays data on Weekend.

in Cell C1 given months from Jan to Feb with drop down oprtion and cell D2 given the year with drop down option.

From Cell C2:BL2 given the dates as 1 to 31, and in from C3:BL3 given the weekday names basing on the year, month and dates of the above cells with the formula as:
=DATE($D$1;MONTH(DATEVALUE($C$1&" 1"));C2)

from C4:BL4 given the data names and C5:BL5 give the data values
on whenever there is saturday and sunday then Sum of the Weekday data values have to arrive on Saturday and sunday for that i used the formula as
=SUMIFS($C5:I5;$C$4:I$4;K$4;$C$2:I$2;">"&K$2-IF(WEEKDAY(K$3)=7;6;IF(WEEKDAY(K$3)=1;7;-999))) and it was successful,

whenever we use to change the month under Cell C1, the below Week name Positions will change automatically in that case the above formula for Sum of the weekday Values needs to be changed manually,

So here i need a help for Automatic formula for sum of the Weekday values on Weekends irrespective Weekends Positions.

attached the sheet for reference.


Please anyone help me out on this.

[RusselB]

Before I give this too much thought, I'd like to get a few questions clarified.
1) The numbers on Saturday are the sum of the previous week. If correct, say so...if not, please explain how the Saturday numbers are calculated (using English, not formulas)... while your formula works, I'm wondering if there might not be something more efficient.
2) Did you realize that your Sunday numbers are simply double your Saturday numbers? Was this intentional or did you have something else in mind for the Sunday numbers? If something else, please explain what you had in mind.
3) Do you have any objections to using helper rows? If your only concern is in regards to appearance, I believe that, once done, the row could be hidden, as there should be no need to modify the formulas I'm thinking of.

[Venkatesh1128]

1. Yes, the numbers on Saturday are sum of the previous week.

2. I have Considered is last day of week instead of 1st day the Sum of Monday to Tuesday will appear on Saturday and the sum of Monday to Saturday has to appear on Sunday.

3.No objections of Using helper rows

Thank you!..

[RusselB]

#2 doesn't make sense to me... if you wrote it correctly and if I'm understanding correctly, then you'd have something like:

Code: Select all   Expand viewCollapse view

Monday 1
Tuesday 2
Wednesday 4
Thursday 8
Friday 16
Saturday 3
Sunday 34

If this looks correct to you, then I'll work with it... If not, then please correct/clarify

[Venkatesh1128]

#2 doesn't make sense to me... if you wrote it correctly and if I'm understanding correctly, then you'd have something like:

Code: Select all   Expand viewCollapse view

Monday 1
Tuesday 2
Wednesday 4
Thursday 8
Friday 16
Saturday 3
Sunday 34

If this looks correct to you, then I'll work with it... If not, then please correct/clarify

Sorry, it would be good if i was more clear on #2.

here it is my explanation on #2:

in my table there is a target of 1,000 on Sunday fro both AHR and THR

Saturday= Sum of numbers from monday to Friday

Sunday= difference between the Target and Saturday values(Target-Saturday Values).

Sorry for the inconvenience and thanks for your Patience.

[Lupp]

Sorry! Your sheet is unmanageable from the beginning. Its design obviously started with a bit of formatting (borders, cell merging ...) without any previous considerations about the intended functionality which in fact always must be the starting point. Next topics always should be usability, scalability, maintainability, openness for enhancements. This also was not considered in advance. What you then tried to implement in row 5 is not feasible without extreme and useless complications.

Yes spreadsheets were invented as a kind of sketchpad++. But the supposed user was an engineer or a businessman needing a swift one-time result. The supposed usage was NOT a surrogate for long-term data-keeping and evaluation.

In addition I doubt that the special role you assign to the weekend days is a promising idea. What if for special reasons one saturday or sunday may also create values for AHR and/or THR? What if you later want to implement an enhancement allowing you to calculate the distances from the weekly targets per day?

Anyway the task in the background most likely is better done based on a database. You may not be ready to shift it that way, however...

I took the time to sketch a sheet of manageable design allowing for the input and evaluation you seem to need. Its formulae access a few constants from a second sheet. You may consider my suggestions. See attachment.

[Venkatesh1128]

Sorry! Your sheet is unmanageable from the beginning. Its design obviously started with a bit of formatting (borders, cell merging ...) without any previous considerations about the intended functionality which in fact always must be the starting point. Next topics always should be usability, scalability, maintainability, openness for enhancements. This also was not considered in advance. What you then tried to implement in row 5 is not feasible without extreme and useless complications.

Yes spreadsheets were invented as a kind of sketchpad++. But the supposed user was an engineer or a businessman needing a swift one-time result. The supposed usage was NOT a surrogate for long-term data-keeping and evaluation.

In addition I doubt that the special role you assign to the weekend days is a promising idea. What if for special reasons one saturday or sunday may also create values for AHR and/or THR? What if you later want to implement an enhancement allowing you to calculate the distances from the weekly targets per day?

Anyway the task in the background most likely is better done based on a database. You may not be ready to shift it that way, however...

I took the time to sketch a sheet of manageable design allowing for the input and evaluation you seem to need. Its formulae access a few constants from a second sheet. You may consider my suggestions. See attachment.

Thanks for your hardwork, i had a question on this sheet, might be i may not clear to you.

in your sheet how can we add number of persons data, i think it is suitable only for one person data but in a month i have nearly 100 persons data and more over the formula has to work automatically on weekends irrespective of range( eg: if there is a formula for whole table, irrespective the weekends position(i.e., cell) it has make sum of the weekday values.

[Venkatesh1128]

#2 doesn't make sense to me... if you wrote it correctly and if I'm understanding correctly, then you'd have something like:

Code: Select all   Expand viewCollapse view

Monday 1
Tuesday 2
Wednesday 4
Thursday 8
Friday 16
Saturday 3
Sunday 34

If this looks correct to you, then I'll work with it... If not, then please correct/clarify

Sorry, it would be good if i was more clear on #2.

here it is my explanation on #2:

in my table there is a target of 1,000 on Sunday fro both AHR and THR

Saturday= Sum of numbers from monday to Friday

Sunday= difference between the Target and Saturday values(Target-Saturday Values).

Sorry for the inconvenience and thanks for your Patience.

Could you please help me on this, if possible.

[mikele]

Hello,
maybe there are better solutions (I agree with Lupp's general position), but nevertheless I tried to figure out if and how it would be possible to manage this problem.
First of all: a cell can contain either a value or a formula. So, if you want to have the the data of one person in one row, so you need fixed columns for weekend. That's why the columns (C, D, ...) and the last columns (..., BW, BX) represent days of different month.
In my solution these columns are protected by conditional format. All values/texts are hidden. If you want to change that, change the cell style "different month". The merged cells in row 2 were problematic for this. That's why I had to unmerge them, D2 got the value of C2 (and so on) and merge them again (containing both contents).
The cell in C5:BX104 are formatted by cell styles too (background color and protection). So you can easily adapt them to your concept.
The protection of the cells is only activated if the sheet ist protected (as I did, without password).