Find cells with data.
 

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

Find cells with data.
 

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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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

"vezerid" wrote in message
ups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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}))))))),d 3)
"Bob Dowell" wrote in message
...
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

"vezerid" wrote in message
ups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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

On Oct 10, 10:12 pm, "Bob Dowell" wrote:
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}))))))),d 3)"Bob Dowell" wrote in message

...

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

"vezerid" wrote in message
oups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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
"vezerid" wrote in message
oups.com...
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

On Oct 10, 10:12 pm, "Bob Dowell" wrote:
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}))))))),d 3)"Bob
Dowell" wrote in message

...

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

"vezerid" wrote in message
oups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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

On Oct 11, 5:06 pm, "Bob Dowell" wrote:
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"vezerid" wrote in message

oups.com...

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

On Oct 10, 10:12 pm, "Bob Dowell" wrote:
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}))))))),d 3)"Bob
Dowell" wrote in message

...

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

"vezerid" wrote in message
oups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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

Find cells with data.
 

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


"vezerid" wrote in message
ups.com...
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

On Oct 11, 5:06 pm, "Bob Dowell" wrote:
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"vezerid" wrote in message

oups.com...

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

On Oct 10, 10:12 pm, "Bob Dowell" wrote:
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}))))))),d 3)"Bob
Dowell" wrote in message

...

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

"vezerid" wrote in message
oups.com...
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),C OLUMN(1:1)),
{1,2})))))

HTH
Kostis Vezerides

On Oct 10, 6:44 pm, "Bob Dowell" wrote:
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