Array formula returns zero, but evaluate formula shows a value

Did you put those{ } in manually? Because when you edit a formula (like your pic seems to indicate, you dont see them inthe formula bar. If you entered them manually, that would probably be why you are not getting your expected answer. You need to 1st create the formula (without the curly braces {} ), then you need enter ARRAY formulas using CTRL SHT ENTER, not just enter

OK, its kinda hard to see from a screenshot, what exactly are you trying to do?

Have you considered other formulas for this, like sumifS() or sumproduct(), for instance?

Originally Posted by GriffinCarpenter

The puzzle is that the formula is returning zero even though stepping through the evaluate formula to the final stage shows that the answer should be around 0.34 (see screenshots attached)
Attachment 345214
Attachment 345215

Whoever said "a picture is worth 1000 words" does not know how to debug Excel problems. It would be better to upload an example Excel file to a file-sharing website and post the shared/public URL in a response here.

[ERRATA] Oh, I forgot: this forum permits you to attach files to a posting. So it would be better to attach an example Excel file.

That said, I can duplicate what you see by the following paradigm:

1. Set Manual calculation mode.
2. Enter 0 into A1.
3. Enter 0.241889783497524 into A2.
4. Enter =A1*A2 into A3. The result is zero.
5. Enter 1.27397651447924 into A1. Note that A3 is still zero.
6. Use Evaluate Formula to step through the calculation of A3.

In step #6, we see =1.27397651447924*0.241889783497524 just before the last step, but we see =0 after the last step.

Does Manual calculation mode explain your problem?

Im glad you found the problem circ ref's can be tricky to resolve, they dont always appear where excel says they are.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Well thanks for the feedback then