Free Microsoft Excel 2013 Quick Reference

if Statements with negative values

Hello I need an if statement that says if cell a1 is Blank or negative, if true a2, if false a3


Post your answer or comment

comments powered by Disqus
I'm trying to put together an if statement with Multiple values and far too complicated criteria
Thanks to everybody for all the help so far!

Hi, It's me again

Now I have one more doubt for the list

I'm trying to do a macro that confirms that a cell value is an hexadecimal number. For that, I'm usingo this code:


	VB:
	
Var3 = Application.Evaluate("Hex2Dec(C5)") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The I need to do an If statemente between Var3 and the error value of the Hex2Dec.

Buuuuuuut.........how can I do an if statement with an error value?

I REALLY hope that anyone can help me

Thanks : D

Need some help with multiple if statements returning multiple values in one cell. Column B lists names (B7 - B163) and Row 2 lists Segment Names (U2 - AW2) with the corresponding rows indicating a primary skill assignment as a value of 1. User names may have multiple primary skill values of 1. Need to list all Segment names of primary skills assigned next to names in Column B all in one cell.

How can I write an IF statement where it produces a true (or false) value as "outlier" if the a cell's value is =<3% or =>-3%? The field in column AG that I'm pulling from has a either a % or "n/a" in the cells. I tried =IF(AND((AG2="n/a"),(AG2<=3%),(AG2>=-3%)),"outlier"," ") but it didn't work. I'm basically trying to identify anything in a column below or equal to -3% and above or equal to 3% and ignore everything else.

I'd also like to have a cell on the top of the sheet where I can change the 3 in the formula to any value.

Thanks in advance.

Hello,

I am trying to use an if statement with multiple values (up to five) in a single cell but I had no luck so far. The values are separated a dash (although I have the same list with the values separated by a comma).

For instance, I have in a cell the numbers 10,28,35,47,64 and I'd like to get a checkmark if any one number falls between 10-20. If this can be done without having to separate the numbers into different columns it would be great.

Thank you

Sans

Hi

Here is my problem. I am trying to work out the number of working days
between 2 dates to assess delivery performance. In some instances it is
a positive number of days because goods have been delivered early and
in others it is a negative number of days because goods have been
delivered late. To get an accurate number of working days, I am using
the NETWORKDAYS function and then subtracting 1 more day. This is fine
where the number of days is a positve value but where the number of days
is a negative value, I actually need to add 1 more day on. Hopefully
the screenshot will illustrates this. The formula I have used in P2
is:

=NETWORKDAYS(O2,N2)-1

The answer appears as -5 but in actual fact it should be -3

The formula has been copied in the remaining cells in column P. It
gives the correct answer in P3 because this is a positive number of
days.

I wondered if I could combined an If statement with NETWORKDAYS but
couldn't get this to work.

Any help would be much appreciated

Shirley Munro

--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=554002

Hi

Here is my problem. I am trying to work out the number of working days between 2 dates to assess delivery performance. In some instances it is a positive number of days because goods have been delivered early and in others it is a negative number of days because goods have been delivered late. To get an accurate number of working days, I am using the NETWORKDAYS function and then subtracting 1 more day. This is fine where the number of days is a positve value but where the number of days is a negative value, I actually need to add 1 more day on. Hopefully the screenshot will illustrates this. The formula I have used in P2 is:

=NETWORKDAYS(O2,N2)-1

The answer appears as -5 but in actual fact it should be -3

The formula has been copied in the remaining cells in column P. It gives the correct answer in P3 because this is a positive number of days.

I wondered if I could combined an If statement with NETWORKDAYS but couldn't get this to work.

Any help would be much appreciated

Shirley Munro

Hello All,

I am having trouble with nesting multiple if statements with if((and)) in them.

Here is what I am trying to do:

Say A1= 10

IF(A15, A17, A110, 1.30)

In words if cell A1 is less than 5 I want the value 1 to be entered, if A1 is between 5 and 7 I want the value 1.1 to be entered, if A1 is between 7 and 10 I want the value 1.2 to be entered, if A1 is greater than 10 I want the value 1.30 to be entered.

I am not sure if it is an issue with the parentheses or how I am writing the formula but I get #value error.

Any help would be much appreciated.

ryanpraski@gmail.com

Thanks!

Hi

Or
=IF(P11=10,"N/A",P11=100)

--
Arvi Laanemets
( My real mail address: arvi.laanemetstarkon.ee )

"Bob Phillips" > wrote in message
...
> =IF(P11=100,"Yes",IF(P11=10,"N/A","No"))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> > wrote in message
> oups.com...
>> Hello,
>>
>> I have a conditional if statement with 2 conditions.
>> If condtion 1 is satisfied I would like to display "Yes"
>> If Conditions 2 is satisfied I would like to display "N/A"
>> else display "No"
>>
>> is this possible? as there only seems to be room for a value if true
>> and a value if false.
>>
>> I tried doing it this way
>>
>> =IF(P11=100,"Yes","No")or(P11=0,"N/A","No")
>>
>> but excel kept telling me to get lost.
>>
>> Any suggestions would be greatly appreciated.
>>
>> Clive
>>
>
>

hi,

I need some help creatng an 'IF' statement that contains a
'VLOOKUP' . In Sheet 1 i have an empty column called ROC (Column F)
that should contain the 'IF/VLOOKUP' function. This should look up
Column B in Sheet 2 and try to match them with entries in Sheet
1:Column D(but only the first four digits of the entries in Sheet
2:Column B. In Sheet 2:Column B row 58 the entry is 5730P; in Sheet 1
Column D, Row 18 the entry is 5730. The 'VLOOKUP' function needs to
identify that the first 4 digits are similar, then output the value of
Sheet 2 Column 'A' row 58 in the Column F Row 18.

if anyone has knowledge of if statement with vlookups please get back
to me.

Thank you,
Nakli

To get more of an idea on my question; My question is similar to the
thread below:

Someone else's Question/Answer relating to vlookups::

Afternoon,

I know some one out there might be able to assist me with my dilemna.

In Sheet1 I have a list of 8 digts id numbers in column A and in
sheet2
is where I am doing the lookup from.

The formula below is working great. I picked up from this website.

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE) )

What I need to know if possible is, can another (vlookup or if ) be
added to the ending formula to do search on just the first 4 number
of
the id, if I got the response Invalid Number.

I currently have another column doing the lookup on just the 4
digits.

Thanks

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 4 2006, 1:33 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Fri, 4 Aug 2006 10:33:02 -0700
Local: Fri, Aug 4 2006 1:33 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
You might try something like this.........

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number"&", Four-digit lookup =
"&YourFourDigitLookpuFormula,VLOOKUP(A6,Schedule!$ A$13:$E
$1463,3,FALSE))

Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Afternoon,

> I know some one out there might be able to assist me with my dilemna.

> In Sheet1 I have a list of 8 digts id numbers in column A and in sheet2
> is where I am doing the lookup from.

> The formula below is working great. I picked up from this website.

> =IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
> Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE) )

> What I need to know if possible is, can another (vlookup or if ) be
> added to the ending formula to do search on just the first 4 number of
> the id, if I got the response Invalid Number.

> I currently have another column doing the lookup on just the 4 digits.

> Thanks

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 1:56 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 13:56:46 -0400
Local: Mon, Aug 7 2006 1:56 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Gracias Chuck,

Thank you for the suggestion, however it did not work for me. I still
got a return of "*Invalid Number*". I do have the 4 digit in the
Sheet1
where I am doing the lookup from.

I am hoping I followed example

Here is what I entered.

=IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1 463,3,FALSE)))

The last part of the arguement does work correctly. I currently using
it when doing a vlookup on just the 4 digit on a seperate column.

I am doing a vlookup on my 8 digit user ID and when I do not get a
match I then what do a vlookup on the first 4 digits only.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 7 2006, 3:26 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Mon, 7 Aug 2006 12:26:02 -0700
Local: Mon, Aug 7 2006 3:26 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
If you are wanting to look up the LEFT 4 digits of a cell, then you
will have
to build that in to your VLOOKUP table, or another one.....unless you
have
both cells containing just those 4 digits AND other cells containg the
entire
number.....VLOOKUP cannot extract the left 4 digits out af a number in
the
table.....just add a column on the left side of the table....assume
you
insert a new column A and the old column A is now B and the table
extends to
F now, then try

=IF(ISNA(VLOOKUP(A6,Schedule!$B$13:$F$1463,3,FALSE )),"Invalid
Number"&",
"&vlookup(left(A6,4),$A$13:$F$1463,3,false),VLOOKU P(A6,Schedule!$B
$13:$F$14*63,3,FALSE))

hth
Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Gracias Chuck,

> Thank you for the suggestion, however it did not work for me. I still
> got a return of "*Invalid Number*". I do have the 4 digit in the Sheet1
> where I am doing the lookup from.

> I am hoping I followed example

> Here is what I entered.

> =IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
> Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1 463,3,FALSE)))

> The last part of the arguement does work correctly. I currently using
> it when doing a vlookup on just the 4 digit on a seperate column.

> I am doing a vlookup on my 8 digit user ID and when I do not get a
> match I then what do a vlookup on the first 4 digits only.

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 6:34 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 18:34:31 -0400
Local: Mon, Aug 7 2006 6:34 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Evening Chuck

I am going to try your suggestion. Just want to mention to you that
the VLoop can strip the LEFT 4 digits. I am currently using this
arguement

=IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the
same
work sheet. I am just trying to eliminate from having a lot of
columns
with formulas which slow up the workbook when saving updates or
making
change to it.

I do have the 8 digits and 4 digits in the same column but when doing
the vlookup I have 2 columns one for the 8 digits and the other for
the
4 digit.

The end results is being populated to another worksheet.

Gracias

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 6:41 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 18:41:38 -0400
Local: Mon, Aug 7 2006 6:41 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Sat3902 Wrote:

- Hide quoted text -
- Show quoted text -

> Evening Chuck

> I am going to try your suggestion. Just want to mention to you that
> the VLoop can strip the LEFT 4 digits. I am currently using this
> arguement

> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the same
> work sheet. I am just trying to eliminate from having a lot of columns
> with formulas which slow up the workbook when saving updates or making
> change to it.

> The database from where I am doing the lookup from does have the 8
> digits and 4 digits in the same column. But the work sheet that I
> import the data needing to be matched up, I have 2 columns one for the
> 8 digits and the other for the 4 digit to do the Vlookup.

> The end results is being populated to another worksheet. I hope I am
> not confusing you with what I am needing.
> Gracias

Have a good day

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 8 2006, 8:46 am

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Tue, 8 Aug 2006 05:46:13 -0700
Local: Tues, Aug 8 2006 8:46 am
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Well then, if you have both the 4 digit and 8 digit numbers in column
A then
this will probably work.....

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number,
"&vlookup(left(A6,4),$A$12:$E$1439,3,false),VLOOKU P(A6,Schedule!$A
$13:$E$14*63,3,FALSE))

Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Evening Chuck

> I am going to try your suggestion. Just want to mention to you that
> the VLoop can strip the LEFT 4 digits. I am currently using this
> arguement

> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the same
> work sheet. I am just trying to eliminate from having a lot of columns
> with formulas which slow up the workbook when saving updates or making
> change to it.

> I do have the 8 digits and 4 digits in the same column but when doing
> the vlookup I have 2 columns one for the 8 digits and the other for the
> 4 digit.

> The end results is being populated to another worksheet.

> Gracias

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.

It worked when I got a match on the 8 digit but did not do the
vlookup
on the 4 digit that is when I got the *#N/A*

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.

It worked when I got a match on the 8 digit but did not do the
vlookup
on the 4 digit that is when I got the *#N/A*

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 8 2006, 8:03 pm

Newsgroups: microsoft.public.excel.newusers
From: "CLR" >
Date: Tue, 8 Aug 2006 20:03:34 -0400
Local: Tues, Aug 8 2006 8:03 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
I'm ready for bed now and cannot do any more tonight........all I can
see
off the bat is that you do not have the "Sheet2! reference on the
middle
part of the formula.........you might try this.........
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid number,
"&VLOOKUP(LEFT(A16,4),Sheet2!$A$2:$C$20882,3,FALSE ),VLOOKUP(A16,Sheet2!
$A$2*:
$B$20882,2,FALSE))

Otherwise, maybe you might send me a copy of your file
to .......croberts
at tampabay dot rr dot com.......and I'll take a look
tomorrow........sorry,
I'm out of gas tonight, but hang in there....we'll whip this thing.

Vaya con Dios,
Chuck, CABGx3

"Sat3902" > wrote
in
message ...

- Hide quoted text -
- Show quoted text -

> Chuck

> Thank you for your time and patients. I tried your suggestion from this
> morning. It only work the first part of the formula.

> Here is what I entered
> =IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
> number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
> P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

> Got a return value of #N/A when I did not get a match on the 8 digit.

> It worked when I got a match on the 8 digit but did not do the vlookup
> on the 4 digit that is when I got the *#N/A*

> Was I suppose to space the last letter of Vlookup at the last Vlookup
> argument. I followed your example. It did the same without the same.

> Again Thank your assistance Please don't stop.
> Unless it can not be done.

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile:

http://www.excelforum.com/member.php...o&userid=36777

- Hide quoted text -
- Show quoted text -

> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 11 2006, 12:07 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Fri, 11 Aug 2006 12:07:30 -0400
Local: Fri, Aug 11 2006 12:07 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Happy days are here again.

Got it to work, with help from a friend and your assistance.

I just needed to add in the ( Left formula ). So if I do not get a
match on my 8 digit number then it will match on the first 4 digit
next.

Here is the formula.

=IF(ISNA(VLOOKUP(A2,'Sheet2'!$A$2:$B
$3950,2,FALSE)),VLOOKUP(LEFT(A2,4),'She*et2'!$A$2: $B$3950,2,FALSE),
(VLOOKUP(A2,'Sheet2'!$A$2:$B$3950,2,FALSE)))

The only draw back about this is, I getting the #N/A value
when I don't get a hit. I can not seem to find the right mix to just
get a blank value if there is not match. I welcome your
input.

Gracias por todo
Senor Chuck

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 11 2006, 12:54 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Fri, 11 Aug 2006 09:54:02 -0700
Local: Fri, Aug 11 2006 12:54 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
This is pretty messy, but you might give it a try.......it should
return the
value in the first table if it's there, otherwise return from the
second
table, if there....and if it's in neither, then return blank.........

=IF(AND(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B
$3950,2,FALSE)),ISNA(VLOOKUP(LEFT(A2,*4),Sheet2!$A $2:$B
$3950,2,FALSE))),"",IF(ISNA(VLOOKUP(A2,Sheet2!$A$2 :$B$3950*,
2,FALSE)),VLOOKUP(LEFT(A2,4),Sheet2!$A$2:$B$3950,2 ,FALSE),
(VLOOKUP(A2,Shee*t2!$A$2:$B$3950,2,FALSE))))

I'm trying to display a value in an excel cell based on what the user selects from 3 drop-down boxes.

The determination of what should be displayed is based on "BIA Decision Matrix" scale located at the bottom of the worksheet.

So for example if the user selects "1,000,000 per day" from cell I5, "<=1 hour" or "2-24 hours" from cell I6 AND "0% of care", "25% of care, "50% of care" or "75% of care" from cell I7, the value to display in cell I27 should be "Critical functions - mission critical, direct patient care. BIA rating: 0" - (blue) and so on as per the matrix attached.

What would be even better is based on the 3 inputs from the dropdown if the Relative Critically Rating table could be highlighted to show the cell where the input falls within.

I'm pretty sure this is a Nested IF statement with AND conditions but I'm struggling to determine how to write this.

Any ideas would be much appreciated.

Thanks, Rai

Here is my formula (which I have to credit martindwilson with helping me get this far).

1) =IF(INDEX($B:$B,COLUMN(D1))>$C3,(INDEX($A:$A,COLUMN(D1))*'SHEET1'!F3,$A3*'SHEET1'!F3))

I am doing something wrong here (big surpise) as the IF statement returns only #VALUE or FALSE, but no real number.

The reason for the format of that formula is so I can drag and auto fill the cells on my sheet. I need to be able to drag and auto fill, otherwise I will cry myself to sleep everynight.

That formula above is much the same as this….

2) =IF(B4>C3,A4*'SHEET1'!F3,A3*'SHEET1'!F3))

When I replace this formula with the 2nd option, it works. So where is my hangup?

Note: I looked through the forums (but not all 1000 pages of threads) in hopes that someone already covered this. I am sure they did, but I did not see it. So for that, I am sorry if I have repeated an issue.

Ok i've got a massive workbook with sales data and the purpose is to create a "report" card for all of the salepeople in various categories to show strengths and areas to improve upon. Since I've assumed this project I've decided to automate as much as is practical since this is run frequently.

Everything is good except for the Gross Profit where there is a sticky weighting system as follows

Quota less Actual = X
0.0% = 10
-0.2% = 9
-0.4% = 8
-0.6% = 7
-0.8% = 6
-1.0% = 5
-1.2% = 4
-1.4% = 3
-1.6% = 2
-1.8% = 1
-2.0% = 0

So I've tried the following if statement but and I can't seem to get it right

=IF(E4>=0,"10",IF(E4>=-0.002,"9",IF(E4<=-0.004,"8",IF(E4<=-0.006,"7",IF(E4<=-0.008,"6",IF(E4<=-0.01,"5",IF(E4<=-0.012,"4",IF(E4<=-0.014,"3",IF(E4<=-0.016,"2",IF(E4<=-0.018,"1","0"))))))))))

Any help would be greatly appreciated I've beat my head against a wall for three days now.

I am trying to figure out how to execute an if statement with the following scenario:

I have a spreadsheet which calculates a percentage based on data input. I would like for Excel to decypher if the percentage falls between a range of =80 it is 15%, =70 it is 35%, =60 is 55%, and

I am having difficulty getting EXCEL (ver. 2000) to handle graphs with negative values of x (typical trig function graphs). Does anyone have some pointers on how to customize so I can create charts illustrating sin, cos, exponential, functions, etc., with x values both negative and positive? Thanks!

I would like to have an IF statement that if it returns false, leaves the cell contents alone. I have searched the web and it seems that you cannot do this, but one person recommended having the false return the font color or cell color. That logically would seem to work; however, what is the format for specifying a font color? (cell reference.font.color = black for instance).

Hello Everyone,

I have a huge file of data that contains many codes, where some are entered inccorrectly. What I've done is highlighted the incorrect code using the conditional formatting. Now that those codes are highlighted, I want to count how many of those incorrect codes are not part of the list I have defined.

For instance, I defined rows A2:A10 as department_code, and used this in the conditional formatting: =countif(department_code,B2)=0

How do I incorporate the department_code in an if statement with 2 criterias? Here's what I had in mind: sumif B2:B100 department_code and branch=26 and date="200701??"

Any help is greatly appreciated!

Thanks!!!

Hi,

In layman's terms I want a formula that does:

(A) a series of nested IF statements with VLOOKUPS that returns a figure
(B) a series of nested IF statements with VLOOKUPS that returns another figure
(C) adds both (A) and (B) together.

My current formula is:
=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%+IF( $O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,FALSE)))

and it returns a figure, multiplies it by 50%, then adds it to the other
figure from the second part of the formula. The above formula works.

What I want to do is add a series of nested IFs to both parts of the
formula; i.e.

(A)=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%, =IF($O$64=2,VLOOKUP(G33,SL!$G$9:$AH$33,19)*40%, etc, etc

(B)IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,if(IF( $O$71=2,VLOOKUP(G33,SL!$G$9:$AH$33,28, etc, etc.

(C) = (A)+(B)

Please help. I'll be forever in your debt!

James

I am using a Pivot table to Count the number of changes occurring against a
project. The Count is against whether the change is Approved or Rejected.
This works fine until I add a 3rd element. Some changes have a negative
financial impact, others positive. Those rows with negative value do not show
up in the Count. Where am I going wrong?

Columns on the source data table a Change Request No; Aligned Project No
(1-6); Status (Approved/Rejected); Financial value

Pivot table data: Rows = Aligned Project; Columns = Status; Data = Count of
Change Requests & Sum of Financial value

Hi,
I am working on a nested If statement with 3 IF's in it.
The first 2 are numbers and the 3rd is text.
The first 2 work fine, the 3rd will not work and I am wondering if I have made a silly mistake somewhere.
The statement is as follows:

=IF(V8>=20000,"Replace Bearings",IF(V8

Hi I need a IF formula with MIN value, as well as MAX, then TRUE, or FALSE (Or similar).

Hence in the example below, for each nested parted the formula checks another cell for the MIN value.

=IF(E2

Hi,

In layman's terms I want a formula that does:

(A) a series of nested IF statements with VLOOKUPS that returns a figure
(B) a series of nested IF statements with VLOOKUPS that returns another figure
(C) adds both (A) and (B) together.

My current formula is:
=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%+IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,FALSE)))

and it returns a figure, multiplies it by 50%, then adds it to the other
figure from the second part of the formula. The above formula works.

What I want to do is add a series of nested IFs to both parts of the
formula; i.e.

(A)=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%,=IF($O$64=2,VLOOKUP(G33,SL!$G$9:$AH$33,19)*40%, etc, etc

(B)IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,if(IF($O$71=2,VLOOKUP(G33,SL!$G$9:$AH$33,28, etc, etc.

(C) = (A)+(B)

Please help. I'll be forever in your debt!

James

I need to create an 'if' statement that does the following...
If E2>40, and F2 is MA or CA or GA or RI then"Check State" if not leave blank ("").
Can you do an if statement with and and or in the same formula?

Hello, I am trying to create an if statment that would include the multiple conditions of:

= IF(ISERROR(D12/B12),0,D12/B12)
AND
= IF(OR((N22/L22)>500%,(N22/L22)<-500%),"NM",(N22/L22)).

I want to be able to combine these statements into one IF statement with these conditions. Any suggestions?

Thanks in advance for your help!!


No luck finding an answer? You could always try Google.