Problem with Formula
 

I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

Try to sum absencedetail

=SUMPRODUCT((DailyDate=F67)*(StaffNumber=$C$11)*Ab senceDetail)

--
Don Guillett
SalesAid Software

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

In case AbsenceDetail houses text-formatted numbers instead of true numbers:

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--AbsenceDetail)

John wrote:
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

Are you saying that the AbsenceDetail cell contains the text Holiday? If so,
that will sum as 0.

--
HTH

Bob Phillips

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

Aladin / Don

Getting #values returned, I expect the word 'Holidays'


"Aladin Akyurek" wrote in message
...
In case AbsenceDetail houses text-formatted numbers instead of true
numbers:

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--AbsenceDetail)

John wrote:
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I
have the fllowing formula which doesn't work and I'm not sure why. A
similiar formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05; StaffNumber = 100 and the value that is within the
AbsenceDetail cell for the 09/05/05 for # 100 is Holiay, except my
formula returns 0 (I have it formatted as General)

Any ideas appreciated


Thanks

Thats correct Bob, it contains Holiday and returns 0, but I want it to
return 'Holiday'


"Bob Phillips" wrote in message
...
Are you saying that the AbsenceDetail cell contains the text Holiday? If
so,
that will sum as 0.

--
HTH

Bob Phillips

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I
have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

Can you post a few rows from AbsenceDetail?

John wrote:
Aladin / Don

Getting #values returned, I expect the word 'Holidays'


"Aladin Akyurek" wrote in message
...

In case AbsenceDetail houses text-formatted numbers instead of true
numbers:

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--AbsenceDetail)

John wrote:

I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I
have the fllowing formula which doesn't work and I'm not sure why. A
similiar formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05; StaffNumber = 100 and the value that is within the
AbsenceDetail cell for the 09/05/05 for # 100 is Holiay, except my
formula returns 0 (I have it formatted as General)

Any ideas appreciated


Thanks




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays







"Aladin Akyurek" wrote in message
...
Can you post a few rows from AbsenceDetail?

John wrote:
Aladin / Don

Getting #values returned, I expect the word 'Holidays'


"Aladin Akyurek" wrote in message
...

In case AbsenceDetail houses text-formatted numbers instead of true
numbers:

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--AbsenceDetail)

John wrote:

I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I
have the fllowing formula which doesn't work and I'm not sure why. A
similiar formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05; StaffNumber = 100 and the value that is within the
AbsenceDetail cell for the 09/05/05 for # 100 is Holiay, except my
formula returns 0 (I have it formatted as General)

Any ideas appreciated


Thanks




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

You cannot sum text. And what if many cells c ontain Holiday, what do you
want then? Or some don't.

Maybe you want

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--(AbsenceDetail="Holida
y"))


--
HTH

Bob Phillips

"John" wrote in message
...
Thats correct Bob, it contains Holiday and returns 0, but I want it to
return 'Holiday'


"Bob Phillips" wrote in message
...
Are you saying that the AbsenceDetail cell contains the text Holiday? If
so,
that will sum as 0.

--
HTH

Bob Phillips

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a
particular
day, to showing any absence detail shown for a particular employee. I
have
the fllowing formula which doesn't work and I'm not sure why. A
similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell
for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have
it
formatted as General)

Any ideas appreciated


Thanks

Thanks Bob, what I want is whatever value is within the field associated
with the Range Name AbsenceDetail that equates to the DailyDate &
StaffNumber

Can't use hardcoded value of "Holidays' becuase there are quite a number of
different values that can be returned within the AbsenceDetail field



"Bob Phillips" wrote in message
...
You cannot sum text. And what if many cells c ontain Holiday, what do you
want then? Or some don't.

Maybe you want

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--(AbsenceDetail="Holida
y"))


--
HTH

Bob Phillips

"John" wrote in message
...
Thats correct Bob, it contains Holiday and returns 0, but I want it to
return 'Holiday'


"Bob Phillips" wrote in message
...
Are you saying that the AbsenceDetail cell contains the text Holiday?
If
so,
that will sum as 0.

--
HTH

Bob Phillips

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a
particular
day, to showing any absence detail shown for a particular employee. I
have
the fllowing formula which doesn't work and I'm not sure why. A
similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell
for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have
it
formatted as General)

Any ideas appreciated


Thanks

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(Staf fNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:
Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

I think you will have to hardcode them, within an array. What are they?

--
HTH

Bob Phillips

"John" wrote in message
...
Thanks Bob, what I want is whatever value is within the field associated
with the Range Name AbsenceDetail that equates to the DailyDate &
StaffNumber

Can't use hardcoded value of "Holidays' becuase there are quite a number
of
different values that can be returned within the AbsenceDetail field



"Bob Phillips" wrote in message
...
You cannot sum text. And what if many cells c ontain Holiday, what do
you
want then? Or some don't.

Maybe you want


=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--(AbsenceDetail="Holida
y"))


--
HTH

Bob Phillips

"John" wrote in message
...
Thats correct Bob, it contains Holiday and returns 0, but I want it to
return 'Holiday'


"Bob Phillips" wrote in message
...
Are you saying that the AbsenceDetail cell contains the text Holiday?
If
so,
that will sum as 0.

--
HTH

Bob Phillips

"John" wrote in message
...
I am trying to re-jig a formula that shows hours worked for a
particular
day, to showing any absence detail shown for a particular employee.
I
have
the fllowing formula which doesn't work and I'm not sure why. A
similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail ))

My range names are correct and all have the same 'length'. F67 is
09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail
cell
for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I
have
it
formatted as General)

Any ideas appreciated


Thanks

Thanks Aladin, thats exactly what I'm looking for and with the correct
answer also


"Aladin Akyurek" wrote in message
...
It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(Staf fNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:
Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDate= H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDeta il,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0)) }

Any hints?


"Aladin Akyurek" wrote in message
...
It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(Staf fNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:
Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

Think I have it if it's incorrect someone might let me know

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$ 11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate =F67)*(StaffNumber=$C$11),0))))}

"John" wrote in message
...
Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDate= H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDeta il,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0)) }

Any hints?


"Aladin Akyurek" wrote in message
...
It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(Staf fNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:
Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

John,

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$ 11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate =F67)*(StaffNumber=$C$11),0))))}

is correct but expensive. How about using a 2-cell approach...

Y2 or any other convenient cell:

{=MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0)}

X2:

=IF(ISNUMBER(Y2),INDEX(AbsenceDetail,Y2),"")

which is confirmed with just enter?


John wrote:
Think I have it if it's incorrect someone might let me know

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$ 11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate =F67)*(StaffNumber=$C$11),0))))}

"John" wrote in message
...

Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDat e=H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDe tail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0 ))}

Any hints?


"Aladin Akyurek" wrote in message
. ..

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(S taffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays


[...]