Formatting cells again

L

Lise

Okay so i got the formula from my previous post answered
however when i input the formula its not working. What am
i doing wrong?

=sumproduct(mid(b5:b7,find("$",b5:b7),255)*1)

Please help...
 
L

Lise

Sorry, i'm new here.

Here's what I've entered

=SUMPRODUCT(MID("IE $10",1,11)*(B5:B7),FIND
("$","b5:b7"),11)*1

Error message is #Value!
 
R

RagDyeR

What happened that caused you to change from the formula in the OP, to the
one in this post?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Sorry, i'm new here.

Here's what I've entered

=SUMPRODUCT(MID("IE $10",1,11)*(B5:B7),FIND
("$","b5:b7"),11)*1

Error message is #Value!
 
L

Lise

I thought I had to fill in...I re-entered the original
formula and it worked!! Thanks - it only took me 4hrs.

Lise
 
L

Lise

New problem...I tried to enter the formula in the actual
spreadsheet and now i can't seem to get the formula to
work properly. Here's what i did:

=sumproduct(mid(c11:c20,FIND("$",c11:c20),255)*1)

just to test out Mondays totals but now i'm getting the
#Value! error again in my totals column. What exactly is
the 255 number?
-----Original Message-----
I thought I had to fill in...I re-entered the original
formula and it worked!! Thanks - it only took me 4hrs.

Lise
-----Original Message-----
What happened that caused you to change from the formula in the OP, to the
one in this post?
--

Regards,

RD
---------------------------------------------------------
Click to expand...
- -
----------

Sorry, i'm new here.

Here's what I've entered

=SUMPRODUCT(MID("IE $10",1,11)*(B5:B7),FIND
("$","b5:b7"),11)*1

Error message is #Value!
What
am


.
Click to expand...
.
Click to expand...
 
R

RagDyer

This means you have a cell in which the text formulas cannot return a
number.

All your cells must have a dollar sign in them before the number.
That means, you must be able to *see* the dollar sign in the formula bar.
If you typed $25, you wouldn't see the dollar sign in the formula bar
because it would be an XL format, not a text character.

Easiest might be, if you have an empty cell, put a (text)$0 in it.

BTW, the 255 is a safety factor, tellng the formula to return 255 characters
after the dollar sign.
You can change it to whatever you think your largest number of digits might
be, 5, 10 ... whatever.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

New problem...I tried to enter the formula in the actual
spreadsheet and now i can't seem to get the formula to
work properly. Here's what i did:

=sumproduct(mid(c11:c20,FIND("$",c11:c20),255)*1)

just to test out Mondays totals but now i'm getting the
#Value! error again in my totals column. What exactly is
the 255 number?
-----Original Message-----
I thought I had to fill in...I re-entered the original
formula and it worked!! Thanks - it only took me 4hrs.

Lise
-----Original Message-----
What happened that caused you to change from the formula in the OP, to the
one in this post?
--

Regards,

RD
---------------------------------------------------------
Click to expand...
- -
----------

Sorry, i'm new here.

Here's what I've entered

=SUMPRODUCT(MID("IE $10",1,11)*(B5:B7),FIND
("$","b5:b7"),11)*1

Error message is #Value!
What
am


.
Click to expand...
.
Click to expand...
 
L

Lise

YEAH!! I figured out where my error was and have corrected
the problem and now the formula works but now have a new
problem.

so i wanted to add C11:C20 with the SUMPRODUCT formula but
not always are all cells (c13, c14, c15,c18, c19,c20)
filled with the data. Somedays yes, somedays no so how do
i set the formula so that i don't have to modify it each
time. I'd like to attach my spreadsheet to show but not
sure how to either...TIA

Lise
-----Original Message-----
This means you have a cell in which the text formulas cannot return a
number.

All your cells must have a dollar sign in them before the number.
That means, you must be able to *see* the dollar sign in the formula bar.
If you typed $25, you wouldn't see the dollar sign in the formula bar
because it would be an XL format, not a text character.

Easiest might be, if you have an empty cell, put a (text) $0 in it.

BTW, the 255 is a safety factor, tellng the formula to return 255 characters
after the dollar sign.
You can change it to whatever you think your largest number of digits might
be, 5, 10 ... whatever.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

New problem...I tried to enter the formula in the actual
spreadsheet and now i can't seem to get the formula to
work properly. Here's what i did:

=sumproduct(mid(c11:c20,FIND("$",c11:c20),255)*1)

just to test out Mondays totals but now i'm getting the
#Value! error again in my totals column. What exactly is
the 255 number?
-----Original Message-----
I thought I had to fill in...I re-entered the original
formula and it worked!! Thanks - it only took me 4hrs.

Lise
Click to expand...
-
-
Click to expand...
-
-
.
Click to expand...


.
Click to expand...
 
P

Peo Sjoblom

Please don't attach any files, it is not looked kindly upon!
What is your formula that works and what do these cells contain,
if you get errors when these cells are empty that can be fixed but
we need to see the formula that works

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
L

Lise

I've used the "Sumproduct" formula and in my cells i have
Monday - Friday, under each day if someone cancels they
have to chose the reason and the $amount that
corresponds. So i've set up a drop down list and included
None $0 because I want the formula to add Monday C11:C15,
Tues E11:E15, etc for the whole week and then in cell L11
total the $$ amount. My problem is when there is no
cancellations in order for the formula to work i had to
include the None $0. Is there a way to assume that if the
cell is blank it is $0?

Thank you
lise
 
R

RagDyer

Wouldn't it be easier if you incorporated the "None $0" into all the
appropriate cells *beforehand*, as part of your form, and then, if there are
cancellations, the drop down would be used to *change* the $0 to the correct
amount?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I've used the "Sumproduct" formula and in my cells i have
Monday - Friday, under each day if someone cancels they
have to chose the reason and the $amount that
corresponds. So i've set up a drop down list and included
None $0 because I want the formula to add Monday C11:C15,
Tues E11:E15, etc for the whole week and then in cell L11
total the $$ amount. My problem is when there is no
cancellations in order for the formula to work i had to
include the None $0. Is there a way to assume that if the
cell is blank it is $0?

Thank you
lise
 
Top