sumproduct mod row
 

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Hi Denise

If you want to start on row 17 and use values for every 13th row, then

=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4))
--
Regards

Roger Govier


"denise" wrote in message
...
Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Surely your formula should be:

=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4))

It then works for me. Do you have #VALUE! in your data anywhere?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify
a
formula.

This formula is working now (in J3) to sum starting with the value in row
11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

There aren't any visible #VALUE! errors in the range however when I evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that should
be numbers. I set the format to number and still get False. These number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Denise,

Re-formatting will not change what is actually being held in the cell.
Having said that I am about to tell you to do just that!

Try entering a new formula:

=--('1'!T22)

That will change the cell contents into a number so try

=ISNUMBER(A56) should be TRUE

and =ISTEXT(A65) should return FALSE

When I tested it in XL97 it automatically re-formatted the cell to Text so I
re-formatted it back to General just to be consistent'

Then evaluate the formula againa and see it you are still getting the
#VALUE!


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that
should
be numbers. I set the format to number and still get False. These number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a
number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the
spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day
1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value
in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row
17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE.
I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Ok, things are getting curiouser and curiouser...

That fixed the problem except when the source cell ('1'!T22) is blank then
it reverts to #VALUE! If there is a number in the cell, all now works fine. I
think I am going to re-work the layout so I have consecutive rows of number
without all those text rows.

Thanks much for all your help!
Denise

"Sandy Mann" wrote:

Denise,

Re-formatting will not change what is actually being held in the cell.
Having said that I am about to tell you to do just that!

Try entering a new formula:

=--('1'!T22)

That will change the cell contents into a number so try

=ISNUMBER(A56) should be TRUE

and =ISTEXT(A65) should return FALSE

When I tested it in XL97 it automatically re-formatted the cell to Text so I
re-formatted it back to General just to be consistent'

Then evaluate the formula againa and see it you are still getting the
#VALUE!


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that
should
be numbers. I set the format to number and still get False. These number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a
number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the
spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day
1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value
in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row
17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE.
I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

sumproduct mod row
 

Denise,

I assume that when you say that the cell is blank you mean it is actually
returning an empty string, (ie ""), which is text. I assume therefore that
'1'!T22 is a cell formatted as general with a formula which is returning a
text representation of a number or an empty string. I would therefore look
at that cell.

--
Good luck, post back if you need further help

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Ok, things are getting curiouser and curiouser...

That fixed the problem except when the source cell ('1'!T22) is blank then
it reverts to #VALUE! If there is a number in the cell, all now works
fine. I
think I am going to re-work the layout so I have consecutive rows of
number
without all those text rows.

Thanks much for all your help!
Denise

"Sandy Mann" wrote:

Denise,

Re-formatting will not change what is actually being held in the cell.
Having said that I am about to tell you to do just that!

Try entering a new formula:

=--('1'!T22)

That will change the cell contents into a number so try

=ISNUMBER(A56) should be TRUE

and =ISTEXT(A65) should return FALSE

When I tested it in XL97 it automatically re-formatted the cell to Text
so I
re-formatted it back to General just to be consistent'

Then evaluate the formula againa and see it you are still getting the
#VALUE!


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that
should
be numbers. I set the format to number and still get False. These
number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being
returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a
number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the
spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for
Day
1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying
to
modify a
formula.

This formula is working now (in J3) to sum starting with the
value
in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in
row
17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE.
I'm
guessing the problem is with the =3 at the end of the formula but
I
can't
figure out what it should be. Any ideas?

Thanks,
Denise