Data Nerd

I'm obsessed with data, which is the reason I take daily readings of my solar generation and electricity usage. I also keep a spreadsheet forecasting my income and expenditure a year ahead, enabling me to budget with pinpoint accuracy.

As an aside, in my 20s and 30s I developed Lotus123 systems for analysing the stability characteristics of ships, reducing lengthy tables to formulae based on regression analysis, enabling me to develop computer programmes to easily calculate a ship's stability.

All of this electrical power and financial data is contained within various pages of a rather large spreadsheet, and some of the formulae used to aid appraisal are rather complex. The constant shifting of blocks of data at the start of each month adds to the memory usage, such that the transition from one month to the next, and all the copying, can result in Excel taking up to 3 minutes to copy an array. It gets worse with time.

I thought I'd ask ChatGPT to simplify one specific formula that compares values in one column to the next column, summing only those values which are greater than the next (month's) column, thereby showing me the outgoings for various expenditures. Cells that are of a lower value to the one to the right are due to be paid in that month, whereas those less than the cell to the right are savings that are accruing until the desired month of payment is reached.

It simplified the following longhand formula, which I used;

=+IF(K24>L24,K24,"0")+IF(K25>L25,K25,"0")+IF(K26>L26,K26,"0")+IF(K27>L27,K27,"0")+IF(K28>L28,K28,"0")+IF(K29>L29,K29,"0")+IF(K30>L30,K30,"0")+IF(+K31>L31,+K31,"0")+IF(K32>L32,K32,"0")+IF(K33>L33,K33,"0")+IF(K34>L34,K34,"0")+IF(K35>L35,K35,"0")+IF(K36>L36,K36,"0")+IF(K37>L37,K37,"0")+IF(K38>L38,K38,"0")+IF(K39>L39,K39,"0")+IF(K40>L40,K40,"0")+IF(K41>L41,K41,"0")+IF(K42>L42,K42,"0")+IF(K43>L43,K43,"0")+IF(K44>L44,K44,"0"+IF(K45>L45,K45,"0"))+IF(K47>L47,K47,"0")+IF(K48>L48,K48,"0")+IF(K49>L49,K49,"0")

to  =SUM(IF(K24:K49>L24:L49,K24:K49,0))

Now multiply that by 12 for each month of the year ahead and you can see the saving in processing.

This is an array calculation, which I've never encountered before in Excel, and must be entered in a special manner, rather than simply pressing Enter. The effect on the time taken to update everything at the end of the month is phenomenal.

To provide a visual aid, I apply conditional formatting to each month column so that any amount that has to paid in that month (is greater than the amount in the corresponding cell for the next month) is a different colour from the background.

I really love ChatGPT. It will even write computer code for you.