Making a logical calculation

Hello everyone,

I have the following code that outputs an xlsx multiple sheets file,

%% Create Tables to export :

t1 = datetime(2021,6,21,1,0,0);
t2 = datetime(2022,6,21,24,0,0);
dt = (t1:hours(1):t2)'

[months,~,jj] = unique([year(dt) month(dt)],'rows');
N = size(months,1);
sheet_names = datetime(months(:,1),months(:,2),ones(N,1),'Format','MMMMyyyy');
ss=0;
for ii = 1:N
    idx = jj == ii;
    DateTIME = dt(idx,1);
    MV_Tariff_IEco = DateNtime(idx,2);
    LV_Tariff_BIG = DateNtime(idx,3);
    Import_TR2kWh = ImportedData(idx,1);
    Import_TR1kWh = ImportedData(idx,2);
    Export_TR2kWh = ImportedData(idx,3);
    DayOfWeek = DateNtime(idx,4);
    Total_Import_Excluding_ShuffersalkWh = CalcBigQShmona(idx,4);
    newTable = table(DateTIME,MV_Tariff_IEco,LV_Tariff_BIG,Import_TR2kWh,Import_TR1kWh,Export_TR2kWh,Total_Import_Excluding_ShuffersalkWh,DayOfWeek);
    writetable(newTable,'OutPutTable.xlsx','Sheet',char(sheet_names(ii)));

end


sheetnames('OutPutTable.xlsx')

I want to make a simple calculation and store it reuslt in a certain cell in every sheet, the calculation well be related for every sheet.

The calculations for every single sheet is :

Formula1 = (The number of weekdays of the current sheet) * 1000 * 0.84913 ---> to be stored somewhere in the sheet as shown in the picture, in my example it's stored in column J.

Formula2 = (Sum of ' ImportedData(:,3) ' ) * 0.84913to be stored somewhere in the sheet as shown in the picture.

note = importedData(:,3) is just the column called "Export_TR2kWh", it's summed and then multiplied by 0.84913.

Thanks !

Answer 1

[Jon]

You can do the calculation for each sheet within your main loop. Then output it to the location you want in the sheet using writematrix with the Range parameter, e.g.

x = ... % your calculated value
writematrix(x,'OutPutTable.xlsx','Sheet',char(sheet_names(ii)),'Range','J5')

[Adnan Jayyousi]

Thanks,

The main issue for me is to calculate the number of work days in a specifig month, in the loop...

[Jon]

I'm not totally clear on what you mean by "number of work days in specific month", but here are some ideas:

I assume here that table, T, (maybe you call this table newTable) has a column DateTime that has the days and times e.g. 6/22/2021 3:00

If you want to count all of the rows on a sheet that correspond to weekdays you could do the following:

numWeekDays = sum(~isweekend(T.DateTime)); % count the weekdays (not weekend)

If you want to count just the number of weekdays that are included on the whole sheet you could use:

[~,idx] = unique(day(T.DateTime)); % find the days that are included on the sheet
numWeekDays = sum(~isweekend(T.DateTime(idx))) % count the ones that are weekdays (not weekend)