I'm using this function to rank data :

=SUMPRODUCT(($B$2:$B$11>B2)/COUNTIF($B$2:$B$11,$B$2:$B$11&""))

The problem is that it keeps picking up empty cells and assigns them the highest value (lowest if I reverse the formula). How do I stop this formula from ranking the empty cells?

I'm using this formula so that the ranks don't skip numbers when there are duplicates.

Say cell A1 has the formula A2 + A3

and B1 has the formula B2 + B2

and C1 has the formula A1 + B1

I need an easy and quick way to create the formula for C1 in the flattened state..

i.e.
(A2 + A3) + (B1 + B2)

Although the above example is trivial, I am trying to simplify a formula that refers to around 40 cells (and around 70 calls through a third party plugin).

Any help appreciated!

This is the formula I'd like to print to each cell going down the column:

=VLOOKUP(CONCATENATE(E2,C2), WORKABILITY_INDEX!$A$1:$B$82, 2, FALSE)

I am using a statement like the one below:

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "A").Value) <> "" Then
Cells(i, "L").Formula = "=VLookup(Concatenate(RC[-8],RC[-10]), ""WORK

I have searched high and low and can't find a simple solution for this. Hopefully it exists.

I have 3 random cells that contain alphanumeric strings. Eg: A3="1L" and B12="2R" and H5="3T" etc. etc. Note: These cells are not in a range, they are random.

Is there a formula in excel that can sum the numeric values of these 3 cells? ie.

I am struggling to get a calc formula to do the following calculations:

A B D G H A
A B C 2 1
A D E 2 1
A F G 1 2
B H I 1 1

As in the example, I need for the formula to compare two cells, and tell me how many common elements they share -without me stating which elements are there in any of the two cells, although they are always letters from the alphabet, a

We have Excel 2007 (German) and vista ultimate.
I am trying Excel by building a roster but have run into a problem with the following formula.

=IF($AT$8>$AU$8;COLUMN(AT1)-COLUMN(P1);IF($AS$8>$AT$8;COLUMN(AS1)-COLUMN(P1);IF($AR$8>$AS$8;COLUMN(AR1)-COLUMN(P1);COLUMN(AQ1)-COLUMN(P1))))+1

As best as I can tell the formula doesn't work because the A8 row is formatted for dates and displays m

See the following situation:

For some

Here cells A1:E3 have plain text data (no formulas), and C4:E4 have the same formula (i.e. typed in one cell and then autofilled to another ones).

I wanted to make cells C4:E4 contain __ if there's nothing in the two cells to the right of them in line 2, and average of 5 cells around corresponding cell in line 2 otherwise.

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like:

Applepie
Applesauce
Crabapple
Banana
Mango

What I need is the count of all visible cells containing the word "Apple".

Good afternoon.

I cannot find a better forum for questions on Gnome-Office, sorry.

In a Gnumeric 1.10.17 workbook of two tables, I enter the formula
=lookup($A$1;Journal!$C$3:$D$65536;Journal!$B$3:$B $65536)
in the cells of a column.

The value from A1 is found many times in the range Journal!$C$3:$D$65536 and the values in Journal!$B$3:$B$65536 are of big diversity.

However, the result in AL