VBA is a very useful thing. You can consolidate data from many files and process large amounts of information using interesting algorithms. For example, the
macro nodupes . It uses the error that occurs during the execution of the code as a check for the presence of an element in the collection being formed. There are macros that use recursion to form all possible combinations and permutations.
But I would like to show some examples of using standard functions of an Excel sheet.
Quite often it is necessary to make a garden from formulas to get the necessary text or numbers out of the cells. This is usually due to incorrect presentation of data. Numbers are numbers, text is text, do not mix them. Dates (and times) are numbers with which calculations can and should be made. Don't be confused on March 14, 2018 from March 14, 2018. The second is just a custom date format [$ -F800], what we see in the cell, and the formula bar will be 03/14/2018. And enough about that.
1. Suppose you have a table with the start and end times of some events, for example, telephone conversations.
The figure is part of the table _t2. It is necessary to calculate the maximum simultaneous number of events. For this there is such a formula:
=(((_t2[start]>=(_t2[start]))*(_t2[start]<=(_t2[end]));(_t2[start])^0)) =MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))
The formula is massive, input is confirmed by simultaneously pressing the Control, Shift, Enter (CSE) buttons. In the formula bar it will be seen that the formula is in curly brackets. MUMMARY makes a virtual matrix the size of the number of rows table by the number of rows of the table, in the rows of which are the results of the calculation, as if the following formula was entered into the rows of the table (for the second row) = SUMPROIZV (H ((C $ 2: C2> A2))) / = SUMPRODUCT (N ((C $ 2: C2> A2))), and extend to the entire table.
2. The same, but more difficult. In addition to the beginning and end, there is the number of units or output / received power. It is necessary to determine the maximum power at some point in time.
Part of the table _t1:
And the formula itself:
=((((_t1[start]*1440)<=(1:1436)-1)*((_t1[stop]*1440)>=(1:1436)-1);_t1[power])) =MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))
Since time is a fraction of a unit, we multiply the values by the number of minutes in a day to get whole numbers. And the second argument of MUMNAGE is a column of power values. In the first case, a column of units was formed.
3. On the left in the table are the amounts corresponding to certain intervals. It is necessary to calculate the amount by month. It is assumed that the sums are evenly distributed within their intervals.
In column H, the first days of the months, in column I the following formulas:
=(_tis[sum per day]*((((H3;0)>_tis[end];_tis[end];(H3;0))-(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2) =SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)
Formulas are massive. You could use SUMPRODUCT / SUMPRODUCT, but IF ERROR does not work without massive input. Also, unfortunately, MAX and MIN cannot form a virtual array of values. Therefore, IF is used to calculate parts of intervals falling on a month. Since the resulting parts may be negative, an error is artificially caused (the square root of a negative number leads to it). In the original table, it was possible to do without the columns of the interval duration and the daily amount. The formula would get a little longer.
4. Small entertainment. How many happy Fridays can be in a year?
Number of Fridays:
=(((((A:A;B2):(A:A;C2)))=13)*((((A:A;B2):(A:A;C2));2)=5)) =SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))
Dates Fridays:
=((15;6;(($A:$A;$B2):($A:$A;$C2))/(((($A:$A;$B2):(A:A;$C2)))=13)/(((($A:$A;$B2):($A:$A;$C2));2)=5);(A$2));"") =IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")
It is very good (here) that the first row of the sheet corresponds to 01/01/1901. Therefore, it is easy enough to form an array of dates of the year using INDEX and LINE and count only Fridays thirteenth.
5. The size of the interest rate, depending on the amount, is quite common. If you use them incorrectly, the graph of the amounts received will be as red as the graph below:
To avoid this, it is necessary to apply the rate only to the part of the amount falling within the interval. And for lower intervals, take fixed amounts.
For such source data:
Formula:
=(A3;tbl;2)*(A3-(A3;tbl;1))+(((tbl[limit]<=A3)*(tbl[limit]-(tbl[limit];-1;))*(tbl[rate];-1;);)) =VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))
The formula is massive. In A3, the amount for which interest is to be calculated. Baseline –tbl.
And, finally, a credit line with a variable interest rate. The formula for calculating the amount of interest depending on the dates / amounts of tranches / returns.
Credit history:
Rate change:
=(B1=0;(((_d;_s[[date]:[rate]]);)*(B5:B16)*((_d>(A5:A16))*(_d<=A2);;)*1/(365+(((_d);4)=0)))-(((_d;_s[[date]:[rate]]);)*(D5:D16)*((_d>(C5:C16))*(_d<=A2);;)*1/(365+(((_d);4)=0)));) =IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)
The formula is massive. Counts interest by day, takes into account leap years.
Thanks for attention!