More Help Please

  • Thread starter Thread starter Calvin
  • Start date Start date
C

Calvin

This was my orginal question: What I am tring to do is create a cel
that will look at my NOW() string and find ex: "*Monday*". My forma
for the NOW() cell is dddd d mmm yyy. What I have found out is that i
I use a IF statement like such: IF (M2="*Monday*",L2,wrong) well nee
less to say that doesnt work. Peo one one the users here suggested
use a COUNTIF and after some more research that only works if you kno
the exact value. Overall I would like to say IF a cell has Monday i
the results take the vaule of another cell and add it to this cell.
And I gt an awsome answer, that work with try

=IF(WEEKDAY(M2,1)=2,L2,"no monday")

My next question is if I only want it to add the corresponding cell i
a range of cells how can do this? example:

if two of the values in range M2:M50 is equal to Monday ie(M2&M3) an
if so I only want L2&L3 added to the cell, how do I prevent th
equation from adding up L2:L50? I tried this, but it returned a #NUM
error

=IF(WEEKDAY(M2:M50,1)=2,L2:L50,"no monday"
 
OK I was able to understand your last post but now I dont completly
What does the (--( do
 
Calvin,

It transforms a sequence of True/False items in 1/0 values

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
OK I was able to understand your last post but now I dont completly.
What does the (--( do?
Click to expand...

Hi
o.k. some explanations:
the part
WEEKDAY(M2:M50,1)=2
returns TRUE or FALSE for each row in column M (depending on the date).
So it returns an array like this:
TRUE
FALSE
TRUE
TRUE
....

The function SUMPRODUCT would now multiply this array with the values
in L2:L50. So without the -- Excel would do something like the
following:
TRUE * L2 +
FALSE * L3 +
TRUE * L4 +
TRUE * L5 +
....

The -- (which is the same as (-1)*(-1)) coerces the boolean values to
numbers (TRUE=1, FALSE=0). So now SUMPRODUCT will do something like:
1 * L2 +
0 * L3 +
1 * L4 +
1 * L5 +
....

Instead of using
=SUMPRODUCT(--(WEEKDAY(M2:M50,1)=2),L2:L50)
you could use
=SUMPRODUCT(1*(WEEKDAY(M2:M50,1)=2),L2:L50)
or
=SUMPRODUCT(0+(WEEKDAY(M2:M50,1)=2),L2:L50)
The important thing is to use a mathematical operation which does not
alter the result. Which one you use depends on your taste. Though I
think sombody posted that the usage of -- is the most efficient (in
respect to performance)

Frank
 
OK I tried that and it returned a #VALUE! error. So did some reading an
the documentation say basically I multplies the array; am
understanding this correctly? If so I dont want to multiply I want t
add and final will one of the funky functions like SUMX2MY2 work? I ra
across it while reading on SUMPRODUCT. Thank again guy
 
OK I tried that and it returned a #VALUE! error. So did some reading
and the documentation say basically I multplies the array; am I
understanding this correctly? If so I dont want to multiply I want to
add and final will one of the funky functions like SUMX2MY2 work? I
ran across it while reading on SUMPRODUCT. Thank again guys
Click to expand...

Hi Calvin
it only multiplys the adjacent columns and adds the results per row. So
this formula should work. You shouldn't get the #VALUE error. Are there
some text entries in the M2:M50 range, so that weekday would evaluaté
to an error?
If you still have problems, you can email me your spreadsheet and I'll
set-up an example function for you (frank[dot]kabel[at]freenet[dot]de)

Frank
 
Hi Calvin
the problem is that you create a circular reference in column M (looks
like you're creating datestamp). This causes the SUMPRODUCT function to
evaluate to an error. So you should use another approach for your
timestam in column M (e.g. have a look at
http://www.mcgimpsey.com/excel/timestamp.html) and do not use the
circular refference method but the worksheet change procedure

After this the SUMPRODUCT function should work

Frank
 
Back
Top