[Solved] Determine points at end of week

[taylor.r113y]

Hi Open Office,

I am once again requesting your help, because I am stupid.
In order to most accurately model stock market fluctuations (sometimes called "opportunities", depending on the context), I have decided to avoid inputting "number of points" on a daily basis.

Instead, I wish to take the total overlay of fluctuation/opportunity over the week, by the end of the week, as of the end of the last working day of the week.

Let us designate the total number of points in a week with 5 full working days, the fluctuations/opportunities, as "x"

Every year, beginning at the start of January 1st, x increases by p.

Number of working days in a week with no holidays = d

Number of working days in a week minus holidays, if holidays occur on a workday = wd

I want a formula that inputs x ONLY on a Friday, 6, if the working week has 5 full working days, Mon - Fri,
And, that inputs (x divided by d, multiplied by wd) on an earlier day, when the full working week is truncated by any holiday,
Which I have listed in the "Holidays" sheet,
Using only one and the same formula.

Upto now, I have
=if(and(year(a1)=2025;weekday(a1)=6...); 1013.5; ...)

Where x = 1013.5.

I already know how to do the p increase part.

The part I don't know how to input, and input it in the shortest and most beautiful way possible, with least extraneous "fuss" is:

On weeks where Friday is a public holiday, it would be (1013.5/5 multiplied by 4), in the Thursday row, when Thursday is the last full day of the working week.
On weeks where Monday is a public holiday, it would be (1013.5/5 multiplied by 4) in the Friday row, when Friday is the last full day of the working week.
On weeks where Thursday and Friday are public holidays, it would be (1013.5/5 multiplied by 3), in the Wednesday row, when Wednesday is the last full day of the working week.
On weeks where there are only two working days, such as the Christmas and New Year period, it would be (1013.5/5 multiplied by 2)...

I have attached a dummy version of the folder.

Thank-you,

Edit: Changed subject, was Formula that inputs a number on specific days at the end of a working week  Make your post understandable by others  -- MrProgrammer, forum moderator

[taylor.r113y]

Now I've got:
IF(AND(WORKDAY(A26-1;1;$Holidays.$C$2:$C$500)=A26;YEAR(A26)=2025;WEEKDAY(A26)=6);1013.1;"")
Which is closer.

Still working on it.
May end up using some equivalent of "networkdays".
Any contribution in the meantime is much appreciated.

[taylor.r113y]

Now I have:
=IF(AND(WORKDAY(A95-1;1)=A95;NETWORKDAYS(A91;A95)=5;YEAR(A95)=2025;WEEKDAY(A95)=6);1013.1;IF(AND(WORKDAY(A95-1;1)=A95;NETWORKDAYS(A91;A95)=4;YEAR(A95)=2025;WEEKDAY(A95)=6);(1013.5/5)*4;IF(AND(WORKDAY(A95-1;1)=A95;NETWORKDAYS(A91;A95)=3;YEAR(A95)=2025;WEEKDAY(A95)=6);(1013.5/5)*3;IF(AND(WORKDAY(A95-1;1)=A95;NETWORKDAYS(A91;A95)=2;YEAR(A95)=2025;WEEKDAY(A95)=6);(1013.5/5)*2;""))))

Which doesn't work.

I am happy with the number occuring only on a Friday, I will settle for that if it means the overall formula is simpler.

But I cannot settle for the (x/d)*wd not working correctly.

[taylor.r113y]

=IF(AND(WORKDAY(A130-1;1)=A130;NETWORKDAYS(A126;A130;$Holidays.$C$2:$C$200)=5;YEAR(A130)=2025;WEEKDAY(A130)=6);1013.1;IF(AND(WORKDAY(A130-1;1)=A130;NETWORKDAYS(A126;A130;$Holidays.$C$2:$C$200)=4;YEAR(A130)=2025;WEEKDAY(A130)=6);(1013.5/5)*4;IF(AND(WORKDAY(A130-1;1)=A130;NETWORKDAYS(A126;A130;$Holidays.$C$2:$C$200)=3;YEAR(A130)=2025;WEEKDAY(A130)=6);(1013.5/5)*3;IF(AND(WORKDAY(A130-1;1)=A130;NETWORKDAYS(A126;A130;$Holidays.$C$2:$C$200)=2;YEAR(A130)=2025;WEEKDAY(A130)=6);(1013.5/5)*2;""))))

It works.
All I need to do now is p (the easy bit, I hope!)

[taylor.r113y]

=IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=5;YEAR(A140)=2025;WEEKDAY(A140)=6);1013.1;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=4;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*4;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=3;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*3;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=2;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*2;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=5;YEAR(A140)=2026;WEEKDAY(A140)=6);1072.5;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=4;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*4;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=3;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*3;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=2;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*2;""))))))))

This is the formula that I wanted, that works for 2025 and 2026 (and 202y, when y is the last number of the year upto 2029) is replicable.

If you have a cleaner formula, I'd love that.

[taylor.r113y]

This version is a truncated and dummy version of the actual.
As I said, if anyone has a cleaner, tidier and neater formula for what I've done, I'd appreciate it.

[MrProgrammer]

Hi, and welcome to the forum. Thank you for the attachment.

=IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=5;YEAR(A140)=2025;WEEKDAY(A140)=6);1013.1
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=4;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*4
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=3;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*3
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=2;YEAR(A140)=2025;WEEKDAY(A140)=6);(1013.5/5)*2
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=5;YEAR(A140)=2026;WEEKDAY(A140)=6);1072.5
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=4;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*4
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=3;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*3
;IF(AND(WORKDAY(A140-1;1)=A140;NETWORKDAYS(A136;A140;$Holidays.$C$2:$C$200)=2;YEAR(A140)=2026;WEEKDAY(A140)=6);(1072.5/5)*2
;""))))))))

If anyone has a cleaner, tidier and neater formula for what I've done, I'd appreciate it.
I am happy with the number occuring only on a Friday, I will settle for that if it means the overall formula is simpler.

Cell C2: =IF(A2=F2;H2/5*G2;"")
Be sure to review Insert → Names → Define.

202509102208.ods

Determine points every Friday

(33.2 KiB) Downloaded 12 times

I wish to take the total [on] the last working day of the week.

Cell E2: =IF(D2;IF(D1=1;E1;A2);"")
Cell F2: =IF(D2;IF(D3=1;F3;A2);"")

202509102337.ods

Determine points on last workday of week

(33.29 KiB) Downloaded 15 times

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Calc date formulas
Calc Guide: Accessing and applying styles

[taylor.r113y]

I have attempted to integrate your solution into my worksheet, but I am getting errors all over the place.

What does the phrase "HoliDates" mean? Why is it not the same name as the designated "Holidays" sheet I have specifically created?

My Vlookup in column J does not work as intended, which is resulting in a ECF in column E.

I have attached the integrated worksheet.
I am aware it is all over the place, but I know I can fix the subsequent errors and "tidying" after the formulae are correct and working.

Could you look at it, and tell me what I am doing wrong, and what is happening?

Thanks,

[taylor.r113y]

Hi,

So,

I have taken and understood (somewhat, but not completely) your feedback.

I do not understand the term "HoliDates".

I do not know how you have used Vlookup.

Your teaching on the above would be immensely appreciated.

However,

I have replaced your formulae of vlookup with my own, making a few substitutions, to match my own level of understanding.

I have also, changed the term "HoliDates" to match my own worksheet names.

It works!

Your idea of
matching the workday in column A with the last working day of the week in a new column is excellent,
and I have replaced the clumsy
=if(workday(a1-1;1;$Holidays.$...)=a1...
with your formatting, it is much cleaner.

I am working on formatting the whole document, and have attached a redacted and dummy version.

Thank-you in advance,

[taylor.r113y]

Hi,

I have applied and understood completely.

Initially, I overlooked the "named ranges", and this is actually a key component of my understanding.

My named range of Points is linked to a separate worksheet called "PWY" (Points Week Year).

I have attached a completed dummy and truncated version of my work.

Thank-you,