Formula bar and cell results differ

**beaunydal** · 02-14-2010, 02:14 AM

On the attached doc is an INDEX formula designed to return the most frequent value in the range A2:A20 that is closest to the bottom of the list.
The list is...
TEESIDE

IPSWICH

QUEENSFERRY
QUEENSFERRY
QUEENSFERRY
QUEENSFERRY
QUEENSFERRY
QUEENSFERRY
QUEENSFERRY

CARDIFF
CARDIFF
CARDIFF
CARDIFF
CARDIFF
CARDIFF

The last one beneath the 6th CARDIFF is a blank.
The formula should return QUEENSFERRY since it is the only most frequent value, and the formula is designed to return A12 corresponding to INDEX(A2:A20,11)
The formula is...

Please Login or Register  to view this content.

The strange thing is the formula returns 0, which it turns out corresponds to INDEX(A2:A20,19), the blank cell at the bottom.
In the Formula bar, when I select the 2nd argument of the INDEX function then press F9, it calculates out at 11. Also, when I select all of the INDEX formula in the Formula bar, leaving out the leading equals sign, F9 results in "QUEENSFERRY". (C1 in the attached doc has =INDEX(A2:A20,11), and of course it returns "QUEENSFERRY")
Now these results are completely different to results produced in cells. When I copy/paste

Please Login or Register  to view this content.

into a cell then add the leading equals sign, the cell returns 19 (B2 in the attached doc)

The attached doc shows various stages of the SUMPRODUCT formula and these are consistent with the F9 results shown in the Formula bar. The only difference I can detect is that COUNTIF(Range,Range) in the Formula bar returns a count for the blank cells (4 in this example), whereas the same in-cell array formula does not count blank cells. I don't think this explains the disagreement between the F9/Formula bar results and the cell results.

Is there an explanation for this disagreement between F9/Formula bar and cell results?

Beau Nydal

**DonkeyOte** · 02-14-2010, 05:03 AM

You need to be careful when using MAX embedded within Arrays and SUMPRODUCT.
The MAX is applied to the entire range rather than on an iterative basis...
ie the MAX of ROW(A1:A19) will be 19 at all times...
It follows that only where the Max frequency happens to include the last value will you get the expected result - in essence this correct output is based more on coincidence than anything else.

Also, on an aside, when using COUNTIF in this manner within SUMPRODUCT you should look to avoid including Blanks unless blank is a valid result of course.

Given you want the last MODE rather than the first you could I guess use something like:

Please Login or Register  to view this content.

For the first MODE you can use:

Please Login or Register  to view this content.

**beaunydal** · 02-14-2010, 10:39 AM

Hi DonkeyOte,
thanks for the warnings about the use of MAX and COUNTIF in Excel's array formulas.
It was my first response to an OOo Community Forum post requesting a formula for returning the most common word in a range.
The OP responded stating that the formula did not work in all situations and posted a link to an Excel file in which the formula returned 0 instead of the most common word.
When I opened the Excel file using OOo Calc the formula worked, so I had a close look at the Excel file in Excel and noticed the discrepancy between the Formula bar calculations and cell calculations for this formula.
Thanks for the suggested formulas.
In the Calc forum I've already suggested a new formula. It is an array formula and is quite long, but it is not affected by the presence of blank cells and is able to deal with situations where two or more words are the most common...

Please Login or Register  to view this content.

When filled down the column it returns each of the most common words. When there are no more it returns blanks.
l will see if I can use your suggestions to come up with a simpler formula that is able to do the same. If you can see a way of simplifying above monster that would be great.
LOL Call me slow! It's taken me some days to realize DonkeyOte=Don Quixote.
Beau Nydal

**DonkeyOte** · 02-14-2010, 11:27 AM

Are you looking for a OO formula ? If so I'm afraid I have no idea - I work in XL.

What I would say, FWIW, is that these types of formulae are always best broken down into multiple lightweight calcs... it is rare that a single cell approach is a good long term strategy.

In even the most basic terms avoiding repetitive calculations is a golden rule.
In this instance for ex. the Max frequency element is constant for all so it would be far better to calculate this once (stored in a given cell) and refer to that cell thereafter rather than repeatedly calculating the Max frequency calc which is in itself quite expensive.

I would also be inclined in this instance to either

a) store the count of the MAX frequency in a given cell (as per the MAX Frequency itself)

or

b) use LOOKUP to avoid the double evaluation of the array, eg:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",Array)))

Option a) would be best as you need only conduct the array once per cell and only when required
Option b) would still calculate the Array in each cell but only once per cell
(b is also limited in terms of expected output - ie assumes consistent data type - in this instance Text string )

EDIT:
using your sample file - assuming:

a) MODEs are to be returned from Last Mode to First (eg Cardiff to Queensferry [when both are 7])
b) you're using single-cell (ie not storing Max Frequency or count thereof separately)
c) results are always text strings
d) results are to be listed say in M2 onwards...

Please Login or Register  to view this content.

In formula length terms the above drops from 446 to 267 but the above is not quite as flexible ... even so it's still hideous ... and would not be the route I'd be going down myself