Thread
:
sumproduct mod row
View Single Post
#
10
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
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
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann