Find cells with data.

  • Thread starter Thread starter Bob Dowell
  • Start date Start date
B

Bob Dowell

I need find the last two cells in a row that contain a number,and use the
sum of those numbers in an average. This will be part of a IF function.

Thanks in advance,
Bob
 
Bob,

I don;t know the wider context in which you want to use it, but the
following formula will sum the rightmost 2 numbers in a row. If there
is any text it is ignored.

The following *array* formula (commit with Ctrl+Shift+Enter) will
calculate the sum for data found in row 1.

=SUM(N(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(1:1),COLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides
 
Kostis,

Thanks for your reply, this is the statement I entered:
=IF(COUNT(E3:N3)>2,AVERAGE(SUM(N(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(1:1),COLUMN(1:1)))))))),d3)
I got an error messages that indicated too many levels of nesting. Would
appreciate any additional help. I don't have to worry about text if that
would make a difference.

Thanks again,
Bob
 
This is an edit of the previous post. The statement was as follows:
=IF(COUNT(E3:N3)>2,AVERAGE(SUM(N(INDIRECT(ADDRESS(1,LARGE(IF(ISNUMBER(1:1),COLUMN(1:1){1,2}))))))),d3)
 
In both cases we have too many levels of nesting.
Why do you need the AVERAGE? From what I understand from your formula
you are just getting the average of a single number, the one returned
by my formula. Your structure is:

=IF(COUNT(E3:N3)>2,AVERAGE(x),D3)

But x is only one number, the one returned by my SUM formula. Your
formula has 9 levels of nesting. If you remove the AVERAGE in the
following sketch, you will still have 8 levels, which is too many:

=IF(COUNT(E3:N3)>2,x,D3)

You can break the formula into two cells. One with my formula and the
other like the sketch above, where x will refer to the cell with my
formula. Write back if this is not an option.

Regards,
Kostis
 
I inserted a column and broke the formula down but I get a value error with
your formula. I don't have anything but numbers to the right. Is there
anyway to point your formula to a range, say from h3 to o3? If it would help
I can send you the excel file.

I certainly appreciate your help.
Bob
 
Bob,

as a first attempt, replace the occurrences of 1:1 in my formula with
H3:O3.

If it still does not work, you can email me.

Regards,
Kostis
 
Kostis,
I was not able to make your formula work, inserting the range didn't solve
the value error. I'm not sure it was going to do what I wanted anyway. I was
able to do what I wanted with nested IF's, COUNT's & AND functions. Thanks
for your help. You got my brain to working.

Bob
 
Back
Top