Problem with if function in excel

  • Thread starter panchal jignesh
  • Start date
P

panchal jignesh

I have 20 (F19-F39) raws in Comlumn F,

in cell F4 - i am trying to us mathfunction to show altest value I put in
F19-F39. I am adding new values in column A (F19-F39) as I go. I want to see
the latest value added in F19-F39 in cell A4.

Hoe can I do? I am using this formula, but it doesnt work from F31 onward.

=IF(F39>0,F39,IF(F38>0,F38,IF(F37>0,F37,IF(F36>0,F36,IF(F35>0,F35,IF(F34>0,F34,IF(F33>0,F33,IF(F32>0,F32,))))))))

please reply on this address too, if possible

(e-mail address removed)
 
R

Ron Rosenfeld

On Sun, 28 Feb 2010 22:46:01 -0800, panchal jignesh <panchal
I have 20 (F19-F39) raws in Comlumn F,

in cell F4 - i am trying to us mathfunction to show altest value I put in
F19-F39. I am adding new values in column A (F19-F39) as I go. I want to see
the latest value added in F19-F39 in cell A4.

Hoe can I do? I am using this formula, but it doesnt work from F31 onward.

=IF(F39>0,F39,IF(F38>0,F38,IF(F37>0,F37,IF(F36>0,F36,IF(F35>0,F35,IF(F34>0,F34,IF(F33>0,F33,IF(F32>0,F32,))))))))

please reply on this address too, if possible

(e-mail address removed)

=LOOKUP(2,1/(F19:F39>0),F19:F39)

will return the last non-zero value in f19:f39

If it is possible for a value to be a zero, then:

=LOOKUP(2,1/(F19:F39<>""),F19:F39)

might be better.

Also, if there will never be any blanks, you could also try:

=OFFSET(F18,COUNT(F19:F39),0)

--ron
 
J

Joe User

panchal jignesh said:
I am adding new values in column A (F19-F39)
as I go. I want to see the latest value added in
F19-F39 in cell A4.

Try the following in A4:

=lookup(1E307,F19:F39)

If you would like the formula to work even when there are no numbers in that
range, then:

=if(count(F19:F39), lookup(1E307,F19:F39), "")


----- original message -----
 
P

panchal jignesh

Thanks, It works, Great Job.,

Ron Rosenfeld said:
On Sun, 28 Feb 2010 22:46:01 -0800, panchal jignesh <panchal


=LOOKUP(2,1/(F19:F39>0),F19:F39)

will return the last non-zero value in f19:f39

If it is possible for a value to be a zero, then:

=LOOKUP(2,1/(F19:F39<>""),F19:F39)

might be better.

Also, if there will never be any blanks, you could also try:

=OFFSET(F18,COUNT(F19:F39),0)

--ron
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top