Forum Discussion
Product function returning 0
ABlea010: Note the corrections below.
NikolinoDE wrote: "AI was partially deployed to support the text."
The usual GIGO from "AI" -- at least, free chatGPT.
I am always skeptical of "AI" results, and I always check the results carefully. Of course, that presumes that I have the skills myself to check the results. If I don't, I don't rely on the "AI" results.
-----
Presumably AI wrote:
=PRODUCT((J5-MIN(J5:J6000))/(MAX(J5:J6000)-MIN(J5:J6000)))
[....] The relative value is then scaled to between 0 and 1.
Right! And since 0 is indeed one of the multicands, the product of the "normalized" data must be zero, even when the product of the original is not (!).
So, this is not a valid alternative. It is a misdirection. I've seen such "garbage out" every one of the few times that I've experimented with (free) chatGPT, just out of curiosity. It scares me that anyone relies on it. I believe that "AI" will be the next Theranos.
-----
Presumably AI wrote:
=PRODUCT(J5:J1000) * PRODUCT(J1001:J2000) * ... * PRODUCT(J5901:J6000)
[....] This can help to reduce the sum of errors and the result is more likely to be correct.
Only under specific conditions.
With ABlea010's data in its original order, since the product of the first two subsets results in zero due to floating-point underflow, the product of all the sub-products must be zero.
And that is true, even if we reorder ABlea010's data, for the reasons that HansVogelaar explained so well.
[EDIT] In contrast, the product of sub-products, possibly with reordered data, might improve the result if some of the data were greater than one.
“The power of artificial intelligence is so incredible that it will transform society in profound ways.”
– Bill Gates
Spring in AI is the most important development in computer science in my lifetime. Every month there are amazing new applications and transformative new techniques, as well as huge advancements in existing technologies. A tool that can develop tools. A tool that can also bring fear, fear in people who see it as their helplessness in their own further development.
It is so easy to teach others and so difficult to teach ourselfs.
For some people, the world is a stage play in which only they play the main role.
Nevertheless, I thank you for the corrections.
Knowing that you don't know anything is the highest thing. Lao Tzu
NikolinoDE wrote: "A tool that can also bring fear, fear in people who see it as their helplessness in their own further development."
I think it is silly when people fear technology for its advancement because it might replace them. Humans can always find better ways to apply themselves. On the contrary, I welcome any new technology or idea that is truly an improvement.
But a tool is only as good as the person using it. For answers.microsoft moderators, they have introduced an AI Moderator Assist feature ("OpenAI") that can assist moderators in developing answers.
But the OpenAI developers recognize that it is not to be used blindly. They emphasize that it "is designed to create an AI generated first draft response to an OP question posted in Answers. It is intended to be used only by technical experts who are able to validate the accuracy of the AI generated first draft and make any necessary changes to the content before posting it".
(Unfortunately, I do not agree with whom they consider to be "experts".)
I should emphasize that my criticism of "AI" is mainly of free chatGPT. In my limited use (tests, really), it repeatedly churns out responses that might correctly offer valuable conceptual information, but they are always wrong in some details -- sometimes horribly wrong to the point of being misdirections.
In contrast, in another forum, I saw one "AI" response that was truly insightful and mostly correct. It was generated by a more-advanced "for pay" version of GPT.
It is also important to understand that not all AI are equally capable. I was intrigued by early developments in "machine learning" (robotics, too); and that has come a long way in the past many decades. Unfortunately, my computer science took me in another direction, and now, I no longer have the energy nor the wherewithal to delve into it again.
In contrast, GPTs are "large language" processors. They have an amazing ability to interpret natural language and to synthesize natural language responses. But it is important to understand that they are just language processors. For technical responses, they do not have the ability to truly understand the technology (e.g. math). This is a point that chatGPT has now been modified to emphasize, both when we log in, and in "chats" when we point out technical errors in its responses.
Enough said! This is an irrelevant and useless digression. The point of my first response in this thread was: free chatGPT responses to Excel questions should never be taken at face value. And the responses to this OP's question were totally wrong and a misdirection.
As I already mentioned in an older post. Please do not comment on my posts to show that your answer is correct. If you would rather help others with your contributions, you do not need to downplay my contributions.
This is very disturbing, although some of the advice is correct.
Anyway, for the second and last time, don't comment on my posts, if they are wrong, there are forum managers and MVPs here who will correct me, correct me with respect, which I really appreciate.
I want to continue to be a goofy and avoid or remove anything toxic.
Go your way and please let others go their way too.
I would like to end all communication here and wish you much success.