Variables

[Francis Hookam]

(I've just pasted this into a Word MG email!)

There are many occasions when I want to use a variable in a spreadsheet
formula ­ for instance:

R1C1 might have =COUNT(R9C:R100C) giving the result 62 since, on this
occasion only, R9C1:R70C1 are occupied

The formula in say R1C5 is =R1C1-COUNTA(R9C:R100C)
I should like to replace the 100 of R100C with the variable, that is the
contents of R1C1, something like this:

=R1C1-COUNTA(R9C:R & R1C1 & C)
or
=R1C1-COUNTA(R9C:R + R1C1 + C)
or even
=R1C1-COUNTA(R9C:R&TEXT(R1C1, "0")&C)

Of course I could extend R9C:R100C to R9C:R1000C or whatever to the bottom
of the sheet, to cover any eventuality, but it would be much neater to have
the flexibility of using a variable held in another cell

How can I do this please

Francis Hookham

 

[CyberTaz]

Hi Francis-

I'm not real clear on your request, primarily because I the A1:A45 refs
rather than R1C1:R45:C1. If I'm interpretting this at least a little
accurately, you may want to look into a function named INDIRECT().

Hope this is useful. |:>)

 

[JE McGimpsey]

(I've just pasted this into a Word MG email!)

There are many occasions when I want to use a variable in a spreadsheet
formula ­ for instance:

R1C1 might have =COUNT(R9C:R100C) giving the result 62 since, on this
occasion only, R9C1:R70C1 are occupied

The formula in say R1C5 is =R1C1-COUNTA(R9C:R100C)
I should like to replace the 100 of R100C with the variable, that is the
contents of R1C1, something like this:

=R1C1-COUNTA(R9C:R & R1C1 & C)
or
=R1C1-COUNTA(R9C:R + R1C1 + C)
or even
=R1C1-COUNTA(R9C:R&TEXT(R1C1, "0")&C)

Of course I could extend R9C:R100C to R9C:R1000C or whatever to the bottom
of the sheet, to cover any eventuality, but it would be much neater to have
the flexibility of using a variable held in another cell

How can I do this please

One way:

=R1C1-COUNTA(OFFSET(R9C,0,0,R1C1,1))

Or, if you don't want to have a stored variable:

=COUNT(R9C1:R100C1)-COUNTA(OFFSET(R9C,0,0,COUNT(R9C1:R100C1),1)