Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
I have the following Index formula which is not producing the correct result
and I am unsure of why {=INDEX(StartTime_HoursWorked,MATCH(1,(Date_HoursW orked=H$9)*(StaffNo_HoursWorked=$B$13)))} My named ranges are using the Offset formula, all worked off "StaffNo_HoursWorked" who's formula is =OFFSET(Database1!$A$2,0,0,COUNTA(Database1!$A:$A) ,1) My guess is the above Offset formula is the problem. As I have dynamic values in y Database, this is why I am using this Any help would be appreciated Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
John wrote...
I have the following Index formula which is not producing the correct result and I am unsure of why {=INDEX(StartTime_HoursWorked,MATCH(1, (Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13 )))} .... The problem is that you're looking for the first exact match, so you need to include the 3rd argument to MATCH, and that argument needs to be zero or FALSE. =INDEX(StartTime_HoursWorked,MATCH(1, (Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13) ,0)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
Thanks Harlan for your guidance, problem now is it returns a #N/A value.
In my test data I only have 4 rows, with each named range being the same size, so not sure why. The value returned in B13 does appear in the range "StaffNo_HoursWorked" as does the value in H9 in Date_HoursWorked "Harlan Grove" wrote in message oups.com... John wrote... I have the following Index formula which is not producing the correct result and I am unsure of why {=INDEX(StartTime_HoursWorked,MATCH(1, (Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$1 3)))} ... The problem is that you're looking for the first exact match, so you need to include the 3rd argument to MATCH, and that argument needs to be zero or FALSE. =INDEX(StartTime_HoursWorked,MATCH(1, (Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13) ,0)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
John wrote...
Thanks Harlan for your guidance, problem now is it returns a #N/A value. .... What does the formula =SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13)) return? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
0 (Zero), or I had it formatted in Time format 12:00am
"Harlan Grove" wrote in message oups.com... John wrote... Thanks Harlan for your guidance, problem now is it returns a #N/A value. ... What does the formula =SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13)) return? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
Harlan
This #N/A maybe caused by the way I produce the value in H(, which has the following ="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")") Maybe Im just trying to be too clever. When I just type a date in H9 it returns a value of 1 in the formula "John" wrote in message ... 0 (Zero), or I had it formatted in Time format 12:00am "Harlan Grove" wrote in message oups.com... John wrote... Thanks Harlan for your guidance, problem now is it returns a #N/A value. ... What does the formula =SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13)) return? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
John wrote...
This #N/A maybe caused by the way I produce the value in H(, which has the following ="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")") Maybe Im just trying to be too clever. When I just type a date in H9 it returns a value of 1 in the formula "John" wrote in message 0 (Zero), or I had it formatted in Time format 12:00am "Harlan Grove" wrote in message .... What does the formula =SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_Hou rsWorked=$B$13)) return? You're getting #N/A errors because of your H9 value and maybe also your B13 value. It seems Date_HoursWorked is a range containing date values. If so, it'd never equal the text value given by the formula you show above. In Excel, numbers never equal text. Replace the H9 formula with =Home!$K$12-5 and format it as you see fit. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index not Reurn the correct Result
Thanks Harlan, I created a "white cell" in H8 with Home!$K$12-5 etc and left
H9 visible and worked the formula off of H8 "Harlan Grove" wrote in message oups.com... John wrote... This #N/A maybe caused by the way I produce the value in H(, which has the following ="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")") Maybe Im just trying to be too clever. When I just type a date in H9 it returns a value of 1 in the formula "John" wrote in message 0 (Zero), or I had it formatted in Time format 12:00am "Harlan Grove" wrote in message ... What does the formula =SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_Ho ursWorked=$B$13)) return? You're getting #N/A errors because of your H9 value and maybe also your B13 value. It seems Date_HoursWorked is a range containing date values. If so, it'd never equal the text value given by the formula you show above. In Excel, numbers never equal text. Replace the H9 formula with =Home!$K$12-5 and format it as you see fit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conversion of Numercials to Figure | Excel Worksheet Functions | |||
VBA | Excel Worksheet Functions | |||
Convert number into words | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions |