Cell Reference

A

ArthurN

Once I posted a question if I could reference the cell dynamically when the
reference equals some other cell value, for example, and some nice people
here advised me to use the "indirect" function, which worked perfectly for me
and made me happy until I discovered this function missing on my PDA running
Windows 2003 SE. Is there some workaround? I'm getting so desperate...
 
R

RagDyeR

You could have posted an example of the formula in question.

However, as an example:

=SUM(A1:A10)

Cell references for start and end of Sum() range are in B1 and C1.

=SUM(INDIRECT("A"&B1&":A"&C1))

Alternate formula:

=SUM(INDEX(A:A,B1):INDEX(A:A,C1))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Once I posted a question if I could reference the cell dynamically when the
reference equals some other cell value, for example, and some nice people
here advised me to use the "indirect" function, which worked perfectly for
me
and made me happy until I discovered this function missing on my PDA running
Windows 2003 SE. Is there some workaround? I'm getting so desperate...
 
A

ArthurN

What I meant is:
=Average(A1:_this cell equals the value of another cell, which in it's turn
might be a function like =Address(R1C4)) In this case, the Indirect function
would capture the result of the Address function and the Average function
will work as expected. But, as I told, the indirect function is missing on
PPC.
It has the Index function, though, but I don't know how to use is it in this
case.
Regards,
ArthurN
 
R

RagDyeR

Is there a reason why you can't post an exact formula that you're looking
for help to revise?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

What I meant is:
=Average(A1:_this cell equals the value of another cell, which in it's turn
might be a function like =Address(R1C4)) In this case, the Indirect function
would capture the result of the Address function and the Average function
will work as expected. But, as I told, the indirect function is missing on
PPC.
It has the Index function, though, but I don't know how to use is it in this
case.
Regards,
ArthurN
 
A

ArthurN

Hi,
I'm sorry for the ambiguity. What I want is this- I use Pocket Excel to keep
my students' grades. And the table looks this way:
--------------------------
assign !Ann!Kate!date
--------------------------
reading! 93 ! !03/12
writing ! 90 ! 63 !03/13
-------------------------
I'm using different functions to count the average, etc.
The blank cell in the first line is an academic debt, which, until changed,
is counted as "0". When counting the average, I count the number of blank
cells in the rows that have date cell not empty as "0". And I don't know how
to do that.

Thanks,
ArthurN
 
R

Ragdyer

Since you *still* haven't posted an actual formula that you're using, I'll
make a guess that:
Column A = subject
Column B to E = grades
Column F = date
Column G = AVG.

With data in A2 to F10.

I'll assume your problem is not this simple, but just to start somewhere,
try this in G2 and copy down:

=(F2>0)*(SUM(B2:E2)/4)

NOW ... post back with your comments !
 
A

ArthurN

I wish I knew the formula myself:):)
The table structure is this:
Column A = assignments (like reading, composition, etc)
Column B to E = grades, with students' names as column headings.
Column F = date of the assignments
-------
Row B = average (just beneath each students' name; and per students, not per
assignment)
-------
With date in A3 to F(not known) the table is still in progress with the
assignments being addeded.
-------
The table has regular grades (like 0, 60, 63, 67) and blank cells that stand
for an academic debt- they're not yet "0", but in the average should be
counted as "0".
-------
I don't know Excel well, may be there's another solution to this. But this
is all I could come up with
-------
First, as I see it, I have to find the last row busy:
1) Counta($F:$F)
2) Address(counta($f:$f),B) - to find the last cell busy in the column I'm
counting. Beneath the student's name
3) countblank(b$3:indirect(address(counta($f:$f),b)) - now I know the number
of blank cells - I could've used this on some other sheet to specify how many
debts each students has, but I have no Indirect function on my PPC, that's
the trouble
4) to count the average is pretty simple then
=(sum(b$3:indirect(address(counta($f:$f),b))))/(counta($f:$f)-2)

Thanks,
ArthurN
 
Top