Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 26th 06, 10:28 PM posted to microsoft.public.excel.worksheet.functions
techiemom60
 
Posts: n/a
Default nested if statment - needs if(isna


I have the following formula, which works fine. It returns either the
date, or the word current if the date is <=5 from today.

IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE)))

My issue is that if there is no date for the selection in my vlookup,
it returns #n/a. I would like to get rid of that.

I have added the if(isna(vlookup...), however, then it returns either
the word True to False.

I have also tried
=if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE))),
with no results.

Any assistance would be greatly appreciated.


--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile: http://www.excelforum.com/member.php...o&userid=20124
View this thread: http://www.excelforum.com/showthread...hreadid=526351


  #2   Report Post  
Old March 26th 06, 10:52 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default nested if statment - needs if(isna

First you can remove 8 parenthesis, then use something like

=IF(ISNUMBER(MATCH(A3,Cons!$C$3:$C$126,0)),IF(TODA Y()-VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)<=5, "Current",VLOOKUP(A3,Cons!$C$3:$F$8770,4,FALSE))," Not
N/A")

don't know why you are using the whole range in the lookup, do you enter
this formula in a 124 row array?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"techiemom60"
wrote in message
...

I have the following formula, which works fine. It returns either the
date, or the word current if the date is <=5 from today.

IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE)))

My issue is that if there is no date for the selection in my vlookup,
it returns #n/a. I would like to get rid of that.

I have added the if(isna(vlookup...), however, then it returns either
the word True to False.

I have also tried
=if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE))),
with no results.

Any assistance would be greatly appreciated.


--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile:
http://www.excelforum.com/member.php...o&userid=20124
View this thread: http://www.excelforum.com/showthread...hreadid=526351


  #3   Report Post  
Old March 26th 06, 10:57 PM posted to microsoft.public.excel.worksheet.functions
Trevor Shuttleworth
 
Posts: n/a
Default nested if statment - needs if(isna

Try

=IF(ISNA(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,F ALSE)),"",IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE))))

Regards

Trevor


"techiemom60"
wrote in message
...

I have the following formula, which works fine. It returns either the
date, or the word current if the date is <=5 from today.

IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE)))

My issue is that if there is no date for the selection in my vlookup,
it returns #n/a. I would like to get rid of that.

I have added the if(isna(vlookup...), however, then it returns either
the word True to False.

I have also tried
=if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))< =5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$877 0,4,FALSE))),
with no results.

Any assistance would be greatly appreciated.


--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile:
http://www.excelforum.com/member.php...o&userid=20124
View this thread: http://www.excelforum.com/showthread...hreadid=526351



  #4   Report Post  
Old March 27th 06, 08:00 PM posted to microsoft.public.excel.worksheet.functions
techiemom60
 
Posts: n/a
Default nested if statment - needs if(isna


Thank you both. This was very helpful.


--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile: http://www.excelforum.com/member.php...o&userid=20124
View this thread: http://www.excelforum.com/showthread...hreadid=526351



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
Nested Subtotals in Excel 2002 KG Excel Discussion (Misc queries) 2 September 10th 05 11:51 AM
Why are my nested sub-totals are displaying incorrectly? chiefdean13 Excel Discussion (Misc queries) 1 July 20th 05 05:45 AM
Nested IF statements John Simons Excel Worksheet Functions 14 February 16th 05 07:17 AM
Q: Nested if JIM.H. Excel Discussion (Misc queries) 2 December 31st 04 10:17 PM
nested ifs sthompson Setting up and Configuration of Excel 1 December 15th 04 07:38 PM


All times are GMT +1. The time now is 06:37 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017