PDA

View Full Version : Excel



Tammy
19-12-2003, 04:39 PM
If I have several rows of formulae that pick up numbers or remain blank (as in "") how can I pick up the rightmost cell with a value in each row? I can find no function that will do that. Any suggestions?

parry
19-12-2003, 06:25 PM
Hi Tammy, if you have values with no blank columns in between one way would be to use the count function inside the address function to locate the address of the cell that isnt blank. If you have blank columns in between this wont give you the correct result, so you would need to deduct # blanks from the column value depending upon how many areas there are.

Find the last non-blank cell in row # 1 (no blanks)
=ADDRESS(1,COUNT(1:1))

Find the last non-blank cell in row # 7 (no blanks)
=ADDRESS(7,COUNT(7:7))

Find the last non-blank cell in row # 7 (with blanks - find last used cell in first area)
=ADDRESS(7,COUNT(7:7)-1)

hth

parry
19-12-2003, 06:32 PM
oops the function should be COUNTA not COUNT.

Tammy
23-12-2003, 11:00 AM
Thanks for your reply Parry.

The formula puts "" into some cells and this seems to throw the COUNTA function. The values across the sheet could be 15, 22, "", 65, "" in which case I would want to pick up the 65 being the last number but I do not want the last "".

I've also tried COUNTIF function using >0 as the criteria and that doesn't give the correct result either.

Cheers
TAmmy

parry
23-12-2003, 12:30 PM
Im not sure of the answer Tammy. I could create a custom function that could do it but there should be a combination of formulas that can do this. Try posting your question on www.mrexcel.com and ask for a formula solution.

regards
parry

parry
23-12-2003, 01:58 PM
Hi Tammy, I have posted a question on MrExcel on your behalf as I would like to know the answer myself. If nobody can give an answer I will give you a custom function to do this but I prefer to use a formula where possible.

parry
23-12-2003, 02:29 PM
Well that didnt take long. Theres some clever people out there. See the Mr Excel post (http://www.mrexcel.com/board2/viewtopic.php?t=72647) for the detail but the answer is either of these formulas where row 1 is the row your searching....

=ADDRESS(1,MATCH(9.99999999999999E+307,1:1))

or

=CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1)))

Tammy
23-12-2003, 04:11 PM
Hi Parry

Thanks for that.. and I just solved it myself another way with an array formula as follows:

={OFFSET(A3,0,MAX(IF(ISNUMBER(A3:K3),COLUMN(A3:K3) ))-1)}

where the cell values in a3 to k3 were numbers, blanks or "" in the cells. In each case I tried the array selected the last number of the array.

Cheers
T

Tammy
23-12-2003, 04:27 PM
PS I never knew that Mr Excel.Com existed. Very interesting site.

I also did not mention that I wanted to pick up the value of the rightmost cell.

T

Mike
23-12-2003, 05:51 PM
> I also did not mention that I wanted to pick up the
> value of the rightmost cell.

You could do that using Parry's examples by incorporating the "INDIRECT" function:

=INDIRECT(ADDRESS(1,MATCH(9.99999999999999E+307,1: 1)))

or

=INDIRECT(CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1))))


Mike.

Mike
23-12-2003, 05:52 PM
minus the
of course (how did that get in there? ?:|)

Mike.