why same Sum formula highlight f9 shows different results

[Bobgiant]

Can someone tell me the reason behind it? I can't figure out

Untitled18.png



So I put same formula into these 4 cells (H33, H34, H35, H36) and then highlight the "offset formula" inside and press f9 and they show different results. Can someone tell me the reason behind it? for example the math behind it


The highlight result that I manually typed out to show you guys is on the L columns

[TMS]

Post the workbook, not a picture of it.

[Bobgiant]

you have to highlight the offset part inside the sum function and press f9 to understand what I am asking on H33 to H36

[Glenn Kennedy]

I can not see the results that you get (column I). I see =SUM({3}) in all cases.

Were you array-entering the formulae, or just using Enter?

OFFSET cannot dispaly arrays correctly and this can somethimes cause problems. I think that's the root cause of the issue you're seeing. I believe that there's a problem with the way formula arrays are handled in the height & width sections of OFFSET. When I use:

SUM(OFFSET($L$33,TRANSPOSE(ROW($L$33:$L$36))-ROW($L$33),,,))

or

=SUM(OFFSET($L$33,{0,1,2,3},,,))

they calculate perfectly and although Evaluate Formula shows =SUM(#VALUE!,#VALUE!,#VALUE!,#VALUE!)... the end result (18) displays correctly.

[AliGW]

They all evaluate the same for me.

Are you eentering them as array formulae with CSE?

[curiouscat408]

[... withdrawn ... potentially misleading conclusions ...]

[menem]

I doubt about TRANSPOSE(), why need to add this function?

Sorry my english is not so well, might be difficult to understan.

imo, when you enter formaula in this way, it might try to converted to
array formula and change relative cell according to the cell location.

Regards.


Note: I'm understand you, you just doubt why the result of OFFSET in each formula after
debug (select and press F9 within the cell) give different result.

[Bobgiant]

@Glenn Kennedy

when you do the two above formulas, do you get the final result 3 and not 18 (6+5+4+3) right?

[Bobgiant]

@menem

I have no idea why Transpose is used, I just randomly find formula on internet and check what they can do

[TMS]

In Excel 365, both formulae produce the result 18, as presented. However, in Excel 365, Array Formulae are recognised and evaluated. In earlier versions of Excel, you need to press Ctrl-Shift-Enter to commit them rather than just Enter.

[Bobgiant]

I am using excel 2010 and I pressed ctrl-shift-enter but the result is 3?

[Bobgiant]

Untitled27.png

see the curly bracket? which means I pressed ctrl-shift-enter and the result is 3 not 18? why?

[TMS]

The attachment is invalid.

[XOR LX]

Hi,

This highlights one of the dangers of using F9 to perform piecemeal evaluation of formulas.

No matter what your version of Excel, using F9 to evaluate a section of a formula will always coerce an array evaluation of that section, even if that section will not actually be array-evaluated when the entire formula itself is committed (either with ENTER or CTRL+SHIFT+ENTER).

Using the Evaluate Formula dialog is a more reliable method.

That said, this does not explain why the OP is seeing different results.

Regards

[Bobgiant]

@ XOR LX

How do you use Evaluate Formula dialog? What is it?

wow ok I found it. I never used that button before.

[AliGW]

With the cell containing the formula selected, go to the Formulas ribbon | Evaluate Formula.