+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    09-22-2023
    Location
    Cambridge
    MS-Off Ver
    Excel, 2016
    Posts
    4

    Post 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.
    Last edited by 6StringJazzer; 09-22-2023 at 08:16 AM. Reason: Adding the rest of your formula on the second line

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

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

    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???
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2405
    Posts
    25,133

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

    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    09-22-2023
    Location
    Cambridge
    MS-Off Ver
    Excel, 2016
    Posts
    4

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

    Thank you for your response.

    Basically I want a formula that will work out what the current growth rate is. So it should take the data from January (D8) and work out the percentage increase compared to the most recent data.

    Alternatively, it could look at the cells where I have worked out growth rate over each year and work out total growth rate from those figures. The trouble is some of those cells are blank (or not technically blank as thy hold a formula that's not produced any results yet).

    Does that help explain my problem a bit?

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,698

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

    In the given data D8 is to be compared with which Cell D24 or D34.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    09-22-2023
    Location
    Cambridge
    MS-Off Ver
    Excel, 2016
    Posts
    4

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

    Quote Originally Posted by 6StringJazzer View Post
    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.

    Thank you this has helped me realised what was happening. So the figures in row 34 are worked out using the January figures in row 22. So when there is nothing in January data, it can't work out the 2023 Total and therefore skips that row and finds the actual most recent data to compare to.

    So I need this formula to ignore specific rows. Rows 20, 34, 48, 62, 76, 90, 104, 118 and 132. Is this possible?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

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

    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.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

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

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Registered User
    Join Date
    09-22-2023
    Location
    Cambridge
    MS-Off Ver
    Excel, 2016
    Posts
    4

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

    Quote Originally Posted by Glenn Kennedy View Post
    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.

    Thank you for your effort. Unfortunately you have come across the same issue as me I think. Your formula finds D34 (Total 23) and believes that that is the most recent data where as actually it should be ignoring that row

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,967

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

    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:
    Quote 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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

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

    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.
    Last edited by Glenn Kennedy; 09-23-2023 at 02:52 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00
    By Ali54121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-14-2022, 01:24 AM
  2. Replies: 8
    Last Post: 02-02-2020, 01:39 AM
  3. Compound growth rate (Getting monthly rate from annual growth rate)
    By rsbrais in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-24-2017, 04:35 PM
  4. Calculating Growth rate
    By sadrap in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2015, 12:50 PM
  5. Need Help Regarding Calculating Growth Rate
    By pappu6600 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-20-2013, 11:15 AM
  6. Replies: 1
    Last Post: 06-18-2012, 04:08 AM
  7. Automation with a growth rate
    By joseclar in forum Excel General
    Replies: 1
    Last Post: 05-06-2009, 10:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1