CODEWITHSUNDEEP
excelexcel-formula
GuiFGDeo
GuiFGDeo

Reputation: 736

Excel formula over full column result is different than with fixed range (same values)

so this is what I'm trying to do:

Excel image

You can see B1 formula in B2 and C1 formula in C2.

I'm applying the formulas with ctrl+shift+enter, thats why there is "{ }" around the formulas.

The correct value is from C1, as it can be seen if you place this on Google:
10^0.5 + 10^0.6 + 10^0.7

I have no idea why this is happening!

Thanks for the help.

Upvotes: 0

Views: 33

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

When you do the whole column it is including all the blanks as 0

10^(0/10) = 1

enter image description here

So it is adding 1 for each row that is blank, that is 1048573 rows or 1048573+12.1552217

One should not use full column references when using array formulas.

You can limit the range with this array formula and still be dynamic

=SUM(10^($A$1:INDEX(A:A,MATCH(1E+99,A:A))/10))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Now you can add to the list without changing the formula and only iterate through the data without extra iterations.

enter image description here

You can remove the need for Ctrl-Shift-Enter with SUMPRODUCT():

=SUMPRODUCT(10^($A$1:INDEX(A:A,MATCH(1E+99,A:A))/10))

It still has the need to limit the data range to only the data to limit the iterations, but can be entered normally.

If you really want to do the extra iterations you will need to put an IF in you SUM:

=SUM(IF(A:A<>"",10^(A:A/10)))

or with SUMPRODUCT:

=SUMPRODUCT((A:A<>"")*(10^(A:A/10)))

These will be slower as they are doing nearly 2 million calculations, 99.9% of which are unneeded.

Upvotes: 2

Related Questions