Giveaway of the Day Forums

1. 

   gergn
   Member

   To keep track of expenses, I use formulas like the one below in several spreadsheets:

   =IF(software.$AU16="";"";SUMIF(INDIRECT(AT$1);software.$AU16;INDIRECT(F$1)))

   It first checks if there is an entry on the current row (16 in this example) in the column that contains years (AU in this case) and sums all software expenses in that year. I prefer the indirect adressing to make editing simpler AND to restrict the counting to those rows that have actually been filled with data.

   I enter the data near the end of each month. So up to today only the January 2021 data are available. They are in row 69, as the result of the function =SUMPRODUCT(MAX(((A10:A500<>"")*ROW(A10:A500)))), the column A containing dates.

   This morning I noted that the last entry did not contain the value of January, but of December 2020. When I added a check column, I saw that the total amount was exactly short of the January 2021 amount. But how I looked, I did not see an error in the formula.

   Now when you do NOT use indirect addressing, you will get an error when the two ranges of the SUMIF function have different number of elements. And when I looked at the cells containing the search and summation ranges, I noted there was a difference. AT$1 contained the text AT9:AT69 (61 entries) and F$1 the text F8:F69 (62 entries). Cell F69 contains the January 2021 value, but the actual summation only summed the 2021 values in the range F8:F68. AT69 is the only cell containing 2021, and thus the contents of F68, containing the December 2020 values, was reported as the year-to-date sum of 2021.

   Both Excel 365 and Libre Office produced the incorrect result. But it is all my fault, of course, not checking the ranges. I expected Excel and Libre Office to check if two indirect ranges contained the same number of elements and they do not. Is this a bug or a shortcoming of Excel and Libre Office?

   You know what the late British PM Disraeli would say when he would still be around today: "There are lies, damned lies, consultants and spreadsheets".

   Posted 3 years ago #
2. 

   ChrisS
   Administrator

   In your formula... SUMIF(INDIRECT(AT$1);software.$AU16;INDIRECT(F$1) you have, as you mention, an array of 61 evaluating an array of 62. I could be wrong but I think the dynamic range specified by the smaller array over-rides that which is specified by the larger array. You might try changing the order and having the larger array first - I suspect that would result in an error.

   In the current example, the smaller array is contained within the larger array so you don't get an error, you just get truncated results. By reversing the order, you have the range specified by the larger array extending beyond the smaller array and you'd get the error (I think).

   Why don't you just use a Pivot Table and let Excel handle all of the work?

   Edit: One other thing, I don't know how you're defining the ranges but were I doing it, I'd set the first one and have Excel update all of the other ranges based on it (using the various text functions) so they'd all match.

   Posted 3 years ago #
3. 

   gergn
   Member

   Re "Why don't you just use a Pivot Table and let Excel handle all of the work?" Because I think this is simpler and less Excel-bound. And my customer certainly would not know how to handle a pivot table. I do let Excel/Libre Office/Google Sheets do all the work, apart from typing in the last date just after the previous one in column A and the amount involved in the same row, in column F.

   The typing error I made was in the formula that computes one of the two ranges. They should be equal in size, of course. They should run from the first to the last date.

   I have automated Excel as follows. The number of rows containing data is found using the formula

   =SUMPRODUCT(MAX(((A10:A500<>"")*ROW(A10:A500)))) and is named e.g. datarange. You see I assume my spreadsheet will never use more than 500 rows, or about 49 years. At my age, that is realistic. If I were 30 years younger, I would perhaps need more than 500 rows. And of of course, if I needed to enter data on a daily basis instead of just once a month.

   The formulas to compute the ranges are something like

   =CONCATENATE(CHAR(COLUMN(F1)+64);"8:";CHAR(COLUMN(F1)+64);TEXT(datarange;"###0"))

   and

   =CONCATENATE(CHAR(65);CHAR(COLUMN(AT1)+38);"8:A";CHAR(COLUMN(AT1)+38);TEXT(datarange;"###0"))

   Column F contains amounts to be summed and column AT contains the formula =IF($A10="";"";YEAR($A10)), so the year the amount was received. Because the customer needs yearly amounts for tax purposes. But I have comparable spreadsheets with columns containing ledger codes, for bookkeeping purposes.

   As I prefer to use Libre Office, because of its simple user interface, and my customer only has Excel, I do not use macros. Of course, I use Excel 365 to test the spreadsheet before sending it off to the customer. With cells containing formulas locked and irrelevant columns hidden.

   Another reason I do not use macros is that I also use Google Sheets and run the spreadsheet on my iPad Pro and chromebook. I hope to "educate" my customer (a family member) to use Google Sheets instead of Excel. Sharing sheets is so much easier with Google Sheets and all updates are taken care of by Google. I can set my Libre Office to use English formulas instead of Dutch formulas, to be compatible to Google Sheets. And because most internet tips on using Excel are in English and thus use English formulas. Some Dutch Excel formula names are very counter-intuitive.

   The typing error I made, was in the starting row 8 in one of the two range formulas. That resulted in unequal sized columns. Which is wrong in this case.

   You are completely right if you were to remark that in this case there is no need at all for dynamic ranges. I could have used the ranges F8:F500 and AT8:AT500. But I just wanted to experiment with dynamic ranges as a substitute for parameters in a subroutine call. And to discover the differences in the formula for rows up to and including Z and those starting at AA. And I think I can observe the difference in computing speed on a cheap chromebook when only cells 8 to 69 have to be summed instead of 8 to 10,000.

   Posted 3 years ago #

RSS feed for this topic

Reply

You must log in to post.