Making a logical calculation
1 view (last 30 days)
Show older comments
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 !
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1051720/image.png)
0 Comments
Answers (1)
Jon
on 30 Jun 2022
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')
2 Comments
Jon
on 1 Jul 2022
Edited: Jon
on 1 Jul 2022
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)
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!