Can you help me understand this formula?

[BenRichards]

=IF(COUNT.IF($GR$3:$GR$18;"<="&GR3)=0;" ";(COUNT.IF($GR$3:$GR$18;"<="&GR3)))

Hi everybody. I'm having trouble understanding the above formula. If there are different letters in the range $GR$3:$GR$18, this formula will give a number of how the letter in question (in cell GR3) relates to the others, what the letter's number would be if we were to sort the letters from smallest to largest.

However, if I enter numbers in that range, the formula returns nothing. I'd like for it to work with both letters and numbers. Any ideas?

 

[Oeldere]

not an anser to your question but change in the formula.

COUNT.IF to countif (without a dot).

 

[jasonb75]

not an anser to your question but change in the formula.

COUNT.IF to countif (without a dot).

That would cause an error.

Ben, could you give some examples of cell content and expected results?

As far as I can see, the only time it should return nothing would be if GR3 was empty.

 

[Oeldere]

Dutch (AANTAL.ALS) English (COUNTIF)

@Jasonb75

Please explain why that cause an error.

I don't understand, i realy appreciate it if you could explain it to me.

 

[jasonb75]

There are differences between various English versions of excel, one uses COUNT.IF instead of COUNTIF, using the wrong one would return a #NAME? error, the same as if you tried using English functions in your Dutch settings, etc.

 

[Oeldere]

@jasonb75

There are differences between various English versions of excel

I never seen / heared about this.

Thanks for the reply, and point me on this one.

Realy appreciated.

 

[jasonb75]

I've been trying to find exactly which version with no success, maybe the OP can answer that for us.

 

[mikerickson]

I note that COUNT.IF($GR$3:$GR$18;"<="&GR3)=0 will always be FALSE , unless this formula is changed by dragging.

The OP could get the same result by using the formula =COUNT.IF($GR$3:$GR$18;"<="&GR3)
and formatting the cell with the custom format General;General;""

 

[BenRichards]

Oeldere, I'm using Excel 2013, the language is not English, hence the COUNT.IF, which I translated so people would know what sort of a function I'm talking about. I didn't alter anything else in the formula.

But my point was that I wanted it to work with numbers as well as letters.

jasonb75, the formula is in cells GQ3-GQ18 (dragged down from the top one).

If I put:

A in GR3
B in GR4
C in GR5

Then the results of the formula will be:

14 in GQ3
15 in GQ4
16 in GQ5

However, if I enter numbers in GR-cells, there are no results in the GQ-cells. I'd like to sort numbers in the same fashion as well.

mikerickson, thanks for your input.

 

[RoryA]

I suspect GR3:GR18 are formatted as Text. Are the numbers aligned to the left of the cells?