Please help fix my ChatGPT formulas. Growth rate since January 2022.

CIVF Social Media and Website Analytics-2.xlsx

See attached document.

Formula in D6 is:
=IF(COUNTIFS(D8:D131, "<>""") <= 1, "",
(LOOKUP(2, 1/(D8:D131<>""), D8:D131) - INDEX(D8:D131, MATCH(TRUE, INDEX(D8:D131<>"", 0), 0))) / INDEX(D8:D131, MATCH(TRUE, INDEX(D8:D131<>"", 0), 0))
)

This formula compares the data in D8 to the most recent data provided. If cell D131 (December 2030) is blank, then it should look for data in D130, then D129 etc etc. It does this correctly.

However, when there is data in any of the January cells (except for D8), the formula doesn't work and gives me -100%. Delete what's in the January cell and the formula works again.

For example, if you open the document, you can see D6 says -100%. This is obviously incorrect. However if you delete the contents of D22, the formula works correctly.

I'm at a loss as to why. ChatGPT is unable to help me any further.

Is anyone able to help with this one? Thanks very much in advance.

instead of showing us a non-working formula made up by some dopey machine, it would have been more helpful to tell us what you want the formula to do.

A guess:

=(INDEX(D:D,AGGREGATE(14,6,ROW(D$8:D$19)/(D$8:D$19<>""),1))-D8)/D8

copied across. But this gives the same result as row 20... so who knows what you expect to see???

The first problem I found with your formula is that it looks for the value of the last non-blank cell in the column. Well, you have percentage totals for each year so it is finding 23.5% in row 34 instead of the last value you really want in row 24.

I don't know why deleting the January value makes it work.

But ChatGPT is notoriously unreliable for solving tech problems. This is mostly because people do not formulate their questions in a rigorous way. If you get an answer from ChatGPT that doesn't work, don't bother to try to to fix it. Throw it away and get some real help.

In the given data D8 is to be compared with which Cell D24 or D34.

Yep. Totally clear, now.

=(LOOKUP(2,1/(IF(ISNUMBER(SEARCH("total",$C8:$C132)),0,1)*D8:D132>0),D8:D132)-D8)/D8

copied across. Neurons 1, ChatGPT 0... I think.

Windows is not your Excel version - which version do you have? Please update your forum profile. Thanks.

The problem is the LOOKUP(...) part of the function. If you use the Evaluate Formula tool, you will see that 1/(D8:D131<>"") part of the formula returns an array of 1s interspersed with #Div/0 errors. From the help file for the LOOKUP() function:

Originally Posted by MS Excel help

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.

An array of 1s interspersed with #Div/0 errors is not sorted, so the lookup results are unstable and erratic. This strategy works well when your "filled range" is a contiguous block of cells followed by a contiguous block of empty cells, but your table is not set up that way. Because your table is setup with blanks interspersed with two different types of values (raw data and percentage change calculations), this "last value lookup" needs to be more complex.

Solutions will probably vary. Before exploring solutions, are you absolutely required to program the spreadsheet in this arrangement? I have found over the years that, if I pay attention to how I arrange my data/calculations in the spreadsheet, programming the sheet can be a lot easier. In this case, thinking long term, you will find it easier to program if you store the raw data in a nice database arrangement, then you can fill in this spreadsheet using lookups, queries, and other functions and tools that are designed to work on a good database. Are you willing to consider a more complete re-write of the spreadsheet?

If you decide that you must use the spreadsheet exactly as is, then the solutions will involve re-writing the LOOKUP() part of the function in a way that more reliably identifies where the "last value (but not really THE last value)" is in the column.

No. I do not think that it does. If there are no 2023 values, then it picks up the last value from 2022 (Dec) which is obviously the SAME as Jan-Dec 2022.

Also, overtype the total value with a silly number: 10,000,000 or whatever. It has NO effect on the % increase

SHOW ME in a file EXACTLY where/how it is giving an incorrect result... and what it should be.