Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 100
Default Adding the resulting intersection to an If statement.

=IF(Sheet1!AQ81,INT(Sheet1!W8/100)*60+MOD(Sheet1!W8,100)+(Sheet1!AR8/10)+INDEX('Adjust
Pts'!$C$8:$Q$651,MATCH(Pace!J4,'Adjust
Pts'!$C$6:$Q$6,0),MATCH(Pace!L4,'Adjust
Pts'!$B$8:$B$651,0)),INT(Sheet1!W8/100)*60+MOD(Sheet1!W8,100))+INDEX('Adjust
Pts'!$C$8:$Q$651,MATCH(Pace!J4,'Adjust
Pts'!$C$6:$Q$6,0),MATCH(Pace!L4,'Adjust Pts'!$B$8:$B$651,0))

Is it possible to add the result of an Index, Match, Match to an If
statement? The above formula gives me a #Value! error. I could do this in
seperate columns, but thought this might work.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default Adding the resulting intersection to an If statement.

The formula is constructed of an IF() function summed to an INDEX() function:
=IF() + INDEX()

The above IF() function also contains a nested INDEX() function:
=IF(Sheet1!AQ81,INT()*60+MOD()+(Sheet1!AR8/10)+INDEX(),INT(Sheet1!W8/100)*60+MOD())

From Help, the syntax of the INDEX function is:
INDEX(array, row_num, column_num)

Your function appears to have the row_num and column_num arguments reversed.
For example:
INDEX('Adjust Pts'!$C$8:$Q$651,MATCH(Pace!J4,'Adjust
Pts'!$C$6:$Q$6,0),MATCH(Pace!L4,'Adjust Pts'!$B$8:$B$651,0))

Should be:
INDEX('Adjust Pts'!$C$8:$Q$651,MATCH(Pace!L4,'Adjust
Pts'!$B$8:$B$651,0),MATCH(Pace!J4,'Adjust Pts'!$C$6:$Q$6,0))

If I'm correct then the above INDEX() function by itself should return the
#REF! error code.

Regards,
Greg



"M.A.Tyler" wrote:

=IF(Sheet1!AQ81,INT(Sheet1!W8/100)*60+MOD(Sheet1!W8,100)+(Sheet1!AR8/10)+INDEX('Adjust
Pts'!$C$8:$Q$651,MATCH(Pace!J4,'Adjust
Pts'!$C$6:$Q$6,0),MATCH(Pace!L4,'Adjust
Pts'!$B$8:$B$651,0)),INT(Sheet1!W8/100)*60+MOD(Sheet1!W8,100))+INDEX('Adjust
Pts'!$C$8:$Q$651,MATCH(Pace!J4,'Adjust
Pts'!$C$6:$Q$6,0),MATCH(Pace!L4,'Adjust Pts'!$B$8:$B$651,0))

Is it possible to add the result of an Index, Match, Match to an If
statement? The above formula gives me a #Value! error. I could do this in
seperate columns, but thought this might work.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding A Not(IsBlank) To IF Statement(help) getmhawks Excel Worksheet Functions 2 June 12th 06 10:43 PM
Finding a match and adding to the statement ben simpson Excel Discussion (Misc queries) 4 March 21st 06 04:51 PM
Adding an AND Statement to COUNTIF Kenton_SJ Excel Worksheet Functions 2 June 17th 05 11:59 PM
when adding cells resulting from tax calulations they do not equa. Marty Excel Discussion (Misc queries) 1 March 14th 05 03:38 PM
adding if statement results Robb27 Excel Worksheet Functions 3 November 20th 04 06:27 AM


All times are GMT +1. The time now is 04:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"